Controlling order of evaluation?

On Wed, 29 Sep 2004 08:16 am, Jerry LeVan wrote:

I have an srf sql function "annual_report(<year>)" that
as 14 columns, a category, 12 month numeric columns, and a total
numeric column.

The function finds monthly totals for each category (which is the
target of "order by")
and the grand total for the specified year.

I have another sql function annual_profit_loss(<year>) that summarizes
the total
for each month and the grand total for each year. ( There is a text
column that
serves a label.) It generates a summary for the annual_report function
in essence.

I have hoped that

select * from annual_report(2003)
union
select * from annual_profit_loss(2003)

Order by title_column = 'Grand Total', month

that will put all rows not containing grand total at the top, sorted by month.
Then grand total's sorted by month. something like that.

Jerry LeVan wrote:

I have hoped that

select * from annual_report(2003)
union
select * from annual_profit_loss(2003)

would print the last select last ;( but it inserts the last selection
alphabetically into the rows of the annual_report depending on the label
field... I suppose I could use a label "zzGrand Totals", but that just
does not look right.

I guess that it's the UNION operator that needs to elimiate duplicate rows
by definition and therefore does sorting to do this. From your
description it seems that you could use UNION ALL instead which might
work ok for you.

Is there any way I can force the last select to appear last?

AFAIK the only sure way is to use ORDER BY if you have
some suitable column or can make one up. Usually using expressions
one can do pretty complicated stuff but it seems that in case of
UNION only select list items are allowed in ORDER BY. But then again you
can do it as subselect and then ....

select a from (
select a,b from ...
union all
select a,b from ...
) u
order by case when b='Last' then 'zzzz' else b end;

Andre