Solved

How to get the null and non null records from the table?

Posted on 2008-06-26
8
278 Views
Last Modified: 2013-12-19
Hi,
    I have one table sample_table. It contains the records like below. This is just sample table
ID        PAR_ID      NAME      SURNAME
100       1000          SURI        
200       1000         RAJ           XXX
300      1000       SURIYA    
        The above sample table contains some sort of records. I want to get the records if the surname column is not null i should return that not null row for that particular PAR_ID. If the SURNAME column is null, I should return any one of the row from table for that particular PAR_ID. For example the above table contains the XXX record in SURNAME column. So that column does not contain null value. So i need to return that XXX row from the table for that particular PAR_ID. But if that XXX is not avaiable, all the rows are null for that column, i need to return any of the row from that table for particular PAR_ID.
           For this we have to write SQL query. Can anyone share with me if you get something.

Thanks
0
Comment
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 21874538

And you want us to do this homework for you?
0
 
LVL 2

Expert Comment

by:Alexey_Varlamov
ID: 21874614
The idea to return exactly one row from first or second condition:
Oracle syntax:
  SELECT * FROM
   (SELECT * FROM sample_table WHERE SURNAME IS NOT NULL AND ROWNUM <= 1
     union
     SELECT * FROM sample_table WHERE SURNAME IS NULL AND ROWNUM <= 1
   )  
  WHERE ROWNUM <= 1

MS SQL Syntax:

  SELECT TOP 1 * FROM
   (SELECT TOP 1 * FROM sample_table WHERE SURNAME IS NOT NULL
     union
     SELECT TOP 1 * FROM sample_table WHERE SURNAME IS NULL
   )  
 
0
 
LVL 2

Expert Comment

by:Alexey_Varlamov
ID: 21874629
and add the  " AND PAR_ID = ... " to the first and second subquery...

0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21874631
               This is not homework for you mike. This is our thoughts you know. I was thinking this since from the morning. But this problem will be really interesting you know.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 21874992

Well, I'm sceptic because I have recently seen similar requests.
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21875115
      hI ALEX, Thanks for your reply. We have to use this in where condition. For example

SELECT  SURNAME
FROM SAMPLE_TABLE
WHERE PAR_ID = 100
AND (             )

                 Based on this where condition SURNAME will return value if it contains. If it is not return any value we have to consider as null.
0
 
LVL 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 21875505
See attached.
get-records.txt
0
 

Author Closing Comment

by:Suriyaraj_Sudalaiappan
ID: 31470946
Good One
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question