Hi Helen,

Thanks for your help....

All the columns are unique across tables, hence the lack of qualifiers.
But problem actually seems to be with Interbase PlanAnalyzer!! The error
it points to is the line below the one you thought, ie

INTO :ID, :TITLE, :EXP_DATETIME

which I don't believe there is anything wrong with. I tried it with IB
Expert, and it works fine!!

Cheers,

David




Helen Borrie <helebor@...> on 22/10/2003 08:13:49 PM

Please respond to firebird-support@yahoogroups.com

To: firebird-support@yahoogroups.com
cc:
Subject: Re: [firebird-support] Re: Stored Procedures?


At 03:04 PM 22/10/2003 -0400, you wrote:

>Hi Martijn,
>
>Yep, tried that already, and still get the same message!
>
>Any other ideas?

Two things.
1) If you are using Firebird, you need to qualify all columns in join
statements. Even if you are not using Fb, you still should. IB simply
lets you pass join statements with potential ambiguity and makes mad
guesses.
2) The error message points to this line in your code:

ORDER BY UpperTitle
Is this actually a column in either of the tables? If so, then it's the
ambiguity that is causing the problem.

If not, then you could try declaring a local variable for it to include it
in the intermediate set that is operated on before passing the output row
(see second source fragment).

In fact, it's impossible to tell from your SP code which tables are
supplying which columns. I'll guess them, you fix them:

CREATE PROCEDURE MY_PROC(CAT_ID INTEGER)
RETURNS (ID INTEGER, TITLE VARCHAR(100),EXP_DATETIME TIMESTAMP)
AS
BEGIN
FOR SELECT
f.id,
f.title,
fc.ExpirationDateTime
FROM forms f
JOIN formcategory fc
ON f.id=fc.form_id
WHERE fc.category_id=:CAT_ID
ORDER BY f.UpperTitle /* is this really "UpperTitle" i.e.
quoted? */
INTO :ID, :TITLE, :EXP_DATETIME;
DO BEGIN
SUSPEND;
END
END

Possible solution for the ORDER BY problem:
CREATE PROCEDURE MY_PROC(CAT_ID INTEGER)
RETURNS (ID INTEGER, TITLE VARCHAR(100),EXP_DATETIME TIMESTAMP)
AS
declare variable uTitle varchar(100);
BEGIN
FOR SELECT
f.id,
f.title,
f.UpperTitle, /* or f."UpperTitle" if needed */
fc.ExpirationDateTime
FROM forms f
JOIN formcategory fc
ON f.id=fc.form_id
WHERE fc.category_id=:CAT_ID
ORDER BY 3
INTO :ID, :TITLE, :uTitle, :EXP_DATETIME;
DO BEGIN
SUSPEND;
END
END

heLen




To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/