| SQL Query [message #647002] |
Mon, 18 January 2016 00:05  |
swapnil_naik
Messages: 269 Registered: December 2009 Location: Mumbai
|
Senior Member |

|
|
Hi Gurus,
Need your help.
I have requirement like this
suppose I have table like this
CREATE TABLE A (num NUMBER, name VARCHAR2(10), grade varchar2(1), marks NUMBER(10));
INSERT INTO A VALUES(1, 'AAKSHAY','C',20) ;
commit;
Now I am inserting only one rows.
My Requirement like this I need to report those column whose grade not 'A' or marks less than 50.
Suppose grade not 'A' then action column shows "GRADE NOT A"
and mark less than 50 the action column shows "mark less 50"
I want to make report like this
num name action
------------------------------
1 'AAKSHAY' GRADE NOT A
1 'AAKSHAY' mark less 50
For this single rows I want to print those two rows, because this num falls in requirement category.
How this kind of situation handle.
In real scenario, column is more with 3000 rows.
How to handle this ??
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL Query [message #647012 is a reply to message #647011] |
Mon, 18 January 2016 04:19   |
cookiemonster
Messages: 13975 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
While the OPs single example row has to be listed twice you've got to assume that other rows will only match one of the two rows.
At that point a row generator isn't the same as union all and is just extra overhead.
|
|
|
|
| Re: SQL Query [message #647013 is a reply to message #647012] |
Mon, 18 January 2016 04:24  |
 |
Michel Cadot
Messages: 68776 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
But with a row generator you only scan once the table when you scan it twice with UNION ALL.
|
|
|
|