| UNION in SQL [message #20063] |
Sat, 27 April 2002 03:10  |
atul
Messages: 17
Registered: September 1999
|
Junior Member |
|
|
I've some problem in SQL which is as follows,
We are using four UNIONS in a query for a Matrix Report.
These four UNIONS returns Data for four different headers (e.g Four Cities).
Out of these, If one of these 4 UNIONs returns "NO ROWS SELECTED" means no data then
in that case we want some default output (e.g 0 , 0 ,0 .....) which indicates that
"No Data for Particular City".
Is it possible? Can you suggest as early as possible?
Regards,
Atul
|
|
|
|
|
|
| Re: UNION in SQL [message #20070 is a reply to message #20065] |
Sun, 28 April 2002 09:53  |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
Since ORACLE expect the UNION operator to join the columns of same type and number in count and you are using more than one UNION in your select statement, giving a SELECT statement that returns a row stating 'No Data for Particular City' is not a good idea. As you may be aware of that the columns given for two or more SELECTs that are UNIONed should be identical in type and number of columns, then adding a string stating the above statement may not be possible unless your other SELECT statements also return a string per row. Think about an alternate solution. I guess, the UNION may not work as you expect in this matter. Hope you understand my point.
Good luck :)
|
|
|
|
| Re: UNION in SQL [message #20110 is a reply to message #20065] |
Tue, 30 April 2002 03:33 |
Amit
Messages: 166
Registered: February 1999
|
Senior Member |
|
|
It is the prime condition of the UNION that datatype in the various select statements are same.
Regarding this query, I do agree that 'No Data for Particular City' can exceed the size of varchar from the cityname; but this was just the example to show that how u can get the result.
By assigning the TEXTMSG less than or equal to the size of cityname the desired functionality can be maintained.
eg. This query is working fine
select ATTR_USERNAME from attribute --size varchar2(30)
where
rownum<16
union
select nvl(ATTR_FORMAT_MASK,'12345') from attribute where rownum<10
union
select distinct nvl(ATTR_FORMAT_MASK,'5678901srerr') from attribute
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
