Solved

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

Posted on 2008-06-26
8
282 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

717 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