Home » SQL & PL/SQL » SQL & PL/SQL » sql
sql [message #302655] Tue, 26 February 2008 08:24 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All

I need help from you people in creating a Select query for the following case:

Suppose there are three columns in a table: Col1, Col2 and Col3. These columns can have data in the following manner:
...........................................................
Col1        Col2            Col3
3            5                    8
5            7                    9
4            6                    
1            7        
8
2
............................................................  
 

There is no such case in which Col2 is empty and Col1 and Col3 contain data, etc. That means the above mentioned rows are the only cases possible for the data in the table.

Now, I need to retreive the rows in the following manner:

Case 1: If there are such rows in which all three columns contain data then I need only those rows to be retreived. If there is no such row then Case 2 applies.

-------------------------------------------------------
Col1        Col2            Col3
3            5                    8
5            7                    9
......................................................

Case 2:
If there is no such row as mentioned in Case 1 then retreive those rows which contain the data for Col1 and Col2.
-------------------------------------------------------
Col1        Col2            Col3
4            6                    
1            7  
--------------------------------------------------------

Case 3: If case 2 also fails then retreive only those rows which contain the data for Col1.
......................................................
Col1        Col2            Col3
8
2
............................................................  
 

Thanks in advance. Smile
Re: sql [message #302658 is a reply to message #302655] Tue, 26 February 2008 08:35 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Post a test case. Post what you have already tried, post version.
Re: sql [message #302660 is a reply to message #302655] Tue, 26 February 2008 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Study the following topic, it will help you:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12864646978683#78016400346482976

Regards
Michel
Re: sql [message #302766 is a reply to message #302660] Wed, 27 February 2008 00:01 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,

I have tried in the following way.But I didn't get required result.
 

 (SELECT <COL1>, <COL2>, <COL3> FROM <TABLE> WHERE <COL2> IS NOT NULL AND <COL3> IS NOT NULL)UNION

(SELECT <COL1>, <COL2>, <COL3> FROM <TABLE> WHERE <COL2> IS NOT NULL AND <COL3> IS NULL)UNION

(SELECT <COL1>, <COL2>, <COL3> FROM <TABLE> WHERE <COL2> IS NULL AND <COL3> IS NULL)


So please send me the solution for this query..

Thank you.
Re: sql [message #302774 is a reply to message #302766] Wed, 27 February 2008 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post the requested information.
pablolee wrote on Tue, 26 February 2008 15:35
Post a test case. Post what you have already tried, post version.


Regards
Michel

[Updated on: Wed, 27 February 2008 00:25]

Report message to a moderator

Re: sql [message #302808 is a reply to message #302774] Wed, 27 February 2008 01:16 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member


SELECT * FROM <TABLE> -- CASE 1
WHERE col1 IS NOT NULL AND col2 IS NOT NULL AND col3 IS NOT NULL
UNION
SELECT * FROM <TABLE> -- CASE 2
WHERE col2 IS NOT NULL AND col3 IS NULL
AND NOT EXISTS ( SELECT * FROM <TABLE> -- CHECK IF CASE 1 EXISTS
WHERE col1 IS NOT NULL AND col2 IS NOT NULL AND col3 IS NOT NULL)
UNION
SELECT * FROM <TABLE> -- CASE 3
WHERE COL1 IS NOT NULL
AND NOT EXISTS ( -- CHECK IF CASE 2 EXISTS
SELECT * FROM <TABLE> -- CASE 2
WHERE col2 IS NOT NULL AND col3 IS NULL
AND NOT EXISTS ( SELECT * FROM <TABLE> -- CHECK IF CASE 1 EXISTS
WHERE col1 IS NOT NULL AND col2 IS NOT NULL AND col3 IS NOT NULL) 
)
AND NOT EXISTS (-- CHECK IF CASE 1 EXISTS
SELECT * FROM <TABLE> -- CASE 1
WHERE col1 IS NOT NULL AND col2 IS NOT NULL AND col3 IS NOT NULL 
)
Re: sql [message #302810 is a reply to message #302808] Wed, 27 February 2008 01:20 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Post a test case. Post what you have already tried, post version.

So far, you have posted the bit in bold
Re: sql [message #302822 is a reply to message #302766] Wed, 27 February 2008 02:03 Go to previous message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
user71408 wrote on Wed, 27 February 2008 07:01
Hi,

I have tried in the following way.But I didn't get required result.
 (SELECT <COL1>, <COL2>, <COL3> FROM <TABLE> WHERE <COL2> IS NOT NULL AND <COL3> IS NOT NULL)UNION
(SELECT <COL1>, <COL2>, <COL3> FROM <TABLE> WHERE <COL2> IS NOT NULL AND <COL3> IS NULL)UNION
(SELECT <COL1>, <COL2>, <COL3> FROM <TABLE> WHERE <COL2> IS NULL AND <COL3> IS NULL)


So please send me the solution for this query..

Thank you.


But I didn't get required result.

What result did you get?
What was really wrong with the result you got?

Maybe the answers on these two questions could give you a hint on how to solve it.

I could spoonfeed you with the solution, but I'd like to give you the opportunity to find it yourself Wink

Previous Topic: SQL query for finding out the depentent tables
Next Topic: To find the Unique Indexes
Goto Forum:
  


Current Time: Fri Dec 09 03:58:25 CST 2016

Total time taken to generate the page: 0.06410 seconds