?
Solved

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

Posted on 2008-06-26
8
Medium Priority
?
285 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

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 1500 total points
ID: 21875505
See attached.
get-records.txt
0
 

Author Closing Comment

by:Suriyaraj_Sudalaiappan
ID: 31470946
Good One
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

771 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