Re: Oracle Reports 2.5 using Dev2000 4.5

Marc,
Unions do order, but you can CHANGE that order. It just requires an order. What I usually do is place the order by columns as column 1,2,3 etc. in my select union. That is, what I want to order by comes first in my Select clause. Then, after doing the union, the default behaviour of the union is to sort by the columns in the order they are mentioned ... 1,2,3 etc.

If you don't want that, then simply place an ORDER BY clause as the LAST STATEMENT of your union (not after each select) and use the column position reference - not the column name. Ei. write ORDER BY 2,3,1 if you want to sort by the 2nd column, then the 3rd column and then the 1st column.

Hope this helps.

Btw. Why not just include 2 conditions in the same SELECT ??? If you include the AND clause in your in-SQL assignment (the & variable) you just assign the variable or not, depending on your needs. And the syntax will always be correct.

SELECT COL1, COL2, COL3
FROM TAB1
WHERE 1=1
&COND1
&COND2 This will work - if you remember to assign the COND variables with prefixing AND's. (like COND1 := "AND COL1='MYSEARCH'").

• Peter H. Larsen

mmartin-ez_at_hiv.hjf.org wrote:

> Help!
>
> I am having trouble getting the output of a report to print with the ranking
> order I want. What makes this a little more complicated is that in the data
> model I am using is a union onto itself so I can include two conditions for
> one of the columns. When you use a union, you cannot use an order by clause
> in the select statement. I then tried to re-order the items in my data model
> (manully putting them into order) thinking that this would make a
> difference...it did not. Does anyone have a suggestion (s) on what I could do
> to order my data? Thank you! Please respond to: mmartin-ez_at_hiv.hjf.org
>
> -Marc.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Wed Jun 10 1998 - 15:46:50 CDT