Thanx for the prompt reply.
But even if I put one order by clause after all select it is giving me 'invalid order by clause' error.

create procedure...
begin
for
select (current_date - col1) , col2 from table1 where col1< current_date
union
select (current_date - col1), col2 from table1 where col1< current_date
order by col1
do
suspend;
end
/*col1 is a date field*/

Firebird version :
ISQL Version: WI-T1.5.0.3481 Firebird 1.5 Release Candidate 3
Firebird/x86/Windows NT (access method), version "WI-T1.5.0.3481 Firebird 1.5 Release Candidate 3"
on disk structure version 10.1

Is this query fine?

Mahesh.
Helen Borrie <helebor@...> wrote:
At 05:06 AM 22/01/2004 +0000, you wrote:

>Hello All,
> May be this is easy, but I'm not able to figure it out, as to to how
> this can be solved.
>I've a select procedure, which returns me a resultset
>
>create procedure...
>begin
> for select col1, col2 from table1 where col1= order by col1
> do
> suspend;
>end
>
>This is working fine. But if a union query instead of a single query is
>used, the stored procedure gives compilation error
>'invalid ORDER BY clause' or 'Token unknown union'
>
>create procedure...
>begin
> for
> select col1, col2 from table1 where col1= order by col1
> union
> select col1, col2 from table1 where col1= order by col1
> do
> suspend;
>end
>
>If I remove the order by clause, it works fine. What I want, is that the
>total resulset should be sorted.
>Can someone help me out to figure this?

Yup - you can only have one ORDER BY clause and it has to come after all of
the unions (and before the INTO of course).

/hb




Yahoo! Groups Links

To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

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/



---------------------------------
Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now

[Non-text portions of this message have been removed]