Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (Oracle 10g)
SQL Query [message #647002] Mon, 18 January 2016 00:05 Go to next message
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 #647005 is a reply to message #647002] Mon, 18 January 2016 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Just a row generator with 2 lines.
In 11g+ you could use UNPIVOT.

And once more, align your columns in the result you post which means you have to use code or pre to it.

Re: SQL Query [message #647006 is a reply to message #647005] Mon, 18 January 2016 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

... and at least feedback if not thanks in your previous topics.

Re: SQL Query [message #647009 is a reply to message #647006] Mon, 18 January 2016 03:10 Go to previous messageGo to next message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
Don't think row generators are what you want. Union all and case seems to be what is needed.
Re: SQL Query [message #647011 is a reply to message #647009] Mon, 18 January 2016 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Isn't UNION ALL on the same table and row generator with 2 lines the same thing? Wink

SQL> select dummy from dual
  2  union all
  3  select dummy from dual
  4  /
D
-
X
X

2 rows selected.

SQL> select dummy
  2  from dual,
  3       (select 1 l from dual union all select 2 from dual)
  4  /
D
-
X
X

2 rows selected.

Re: SQL Query [message #647012 is a reply to message #647011] Mon, 18 January 2016 04:19 Go to previous messageGo to next message
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 Go to previous message
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.

Previous Topic: ANSI SQL STATEMENTS
Next Topic: CHAR '
Goto Forum:
  


Current Time: Sun Jul 05 23:54:04 CDT 2026