Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2008-06-26
8
Medium Priority
?
292 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

580 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