Re: Need help with SQL

In article <NEWTNews.847379219.13325.atyagi_at_sgeier.wsp>, atyagi_at_mc.xerox.com writes:
|>
|>
|> Hi folks,
|>
|> Thanks to all who helped me last time. I have another question for
|> you. I have written a query using UNION operator. I have tested these two
|> queries seprately and they work well, but when I use the UNION an error
|> appears telling me that UNION is an unknown command.. strange!
|>
|> here is my query :
|>
|> SELECT input_date, SUM(totbo) as USCO_TOTBO, SUM (xci_count) as XCI_TOTBO
|> FROM
|> (SELECT input_date, totbo, 0 as xci_count
|> FROM uscobosum
|> WHERE input_date >= ADD_MONTHS(sysdate,-24) and (TO_CHAR(input_date,'DAY') like 'M%')
|>
|> UNION
|>
|> SELECT input_date,0 as totbo, sum(totbo)
|> FROM xci_bo
|> WHERE input_date >=ADD_MONTHS(sysdate,-24)
|> GROUP BY input_date
|> )
|> GROUP BY input_date;
|>
|>
|>
|> I will appreciate any help:
|>
|> Please post or mail to
|>
|> atyagi_at_mc.xerox.com
|>

If I'm not mistaken, I don't believe that you can use a GROUP by clause on a compound query (using it on the simple queries inside of the compound query may be ok, but I'm not sure of that either.) An alternative would be to construct a view with the detail data (no group by clauses) in the union query. Then select from the view with the group by clause.
Jim Wolfe Received on Tue Nov 12 1996 - 00:00:00 CET