At 23/08/2002 10:32 AM, you wrote:

>Daniel,
>You don't give details of the SP but one guess is it is querying your
>CLIENT table and perhaps CLIENTORDER has a dependency on the CLIENT table
>also?

Here's the Stored Procedure and as you suggested, I aliases the CLIENTS table:
CREATE PROCEDURE CLIENTFULLNAME_1 (
NAMETYPE SMALLINT,
CLIENTNO INTEGER)
RETURNS (
FULLNAME VARCHAR (1113))
AS
DECLARE VARIABLE Lastname VarChar(122);
DECLARE VARIABLE FirstName VarChar(122);
DECLARE VARIABLE MiddleName VarChar(122);
begin
FOR SELECT
c.LastName,
c.FirstName,
c.MiddleName
FROM Clients c
WHERE c.CLIENTNO=:CLIENTNO
ORDER BY c.ClientNo
INTO :LastName, :FirstName, :MiddleName
DO
BEGIN
IF ((NameType=1) AND (:FirstName IS NOT NULL) AND (:MiddleName IS
NOT NULL)) THEN
FullName = f_lrtrim(Lastname)||', '||f_lrtrim(Firstname)||'
'||f_lrtrim(MiddleName);
ELSE
IF ((NameType=2) AND (:FirstName IS NOT NULL) AND (:MiddleName IS
NOT NULL)) THEN
FullName = f_lrtrim(Firstname)||' '||f_lrtrim(MiddleName)||'
'||f_lrtrim(Lastname);
IF ((NameType=1) AND (:FirstName IS NOT NULL) AND (:MiddleName IS
NULL)) THEN
FullName = f_lrtrim(Lastname)||', '||f_lrtrim(Firstname);
ELSE
IF ((NameType=2) AND (:FirstName IS NOT NULL) AND (:MiddleName IS
NULL)) THEN
FullName = f_lrtrim(Firstname)||' '||f_lrtrim(Lastname);
IF ((NameType=1) AND (:FirstName IS NULL) AND (:MiddleName IS NULL))
THEN
FullName = f_lrtrim(Lastname);
ELSE
IF ((NameType=2) AND (:FirstName IS NULL) AND (:MiddleName IS
NULL)) THEN
FullName = f_lrtrim(Lastname);
IF ((NameType=1) AND (:FirstName IS NULL) AND (:MiddleName IS NOT
NULL)) THEN
FullName = f_lrtrim(Lastname)||', '||f_lrtrim(MiddleName);
ELSE
IF ((NameType=2) AND (:FirstName IS NULL) AND (:MiddleName IS NOT
NULL)) THEN
FullName = f_lrtrim(MiddleName)||' '||f_lrtrim(Lastname);
SUSPEND;
END
end

>If this won't work, another way to deal with it might be to include CLIENT
>in the query and use the separate context alias there instead:

Tried this and still same problem.

>Or (one more) maybe the DISTINCT restriction is interfering with the
>subselect and you need to replace it with FIRST 1...

I just found out that the DISTINCT restriction is the problem. Now, my
problem is that I do want to restrict the the output to be unique and not
have doubles, as I do know that there will be some, if the DISTINCT
restriction is not applied. I might be able to use a work-around by using
stored procedure to perform the same task, but a query would be better,
since it's only used in one report.

My question:
Why would the DISTINCT restriction affect the usage of a subselect?

Thanks for any insight.


Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.accramed.ca)