Home » SQL & PL/SQL » SQL & PL/SQL » Query to find number of NULLs for each record. (Oracle 11g)
Query to find number of NULLs for each record. [message #593554] Sun, 18 August 2013 10:07 Go to next message
ramya_162
Messages: 97
Registered: August 2013
Location: Banglore
Member
Hi Experts,

We have data as below in the table.
I need the to display the records in the order based on number of NULL values
and position for each record.

Can you please provide a simple query using case in ORDER BY clause.

ID     CLASS  NAME   DIST_ID  DIST_NAME
0      NULL   KIRAN   0        AP
0      C1213  NULL    0        AP
0      NULL   NULL    0        AP
NULL   C1234  NULL    0        AP
0      NULL   NULL    0        AP
NULL   NULL   NULL    NULL     NULL
0      C123   RAJESH  0        AP
NULL   C123   RAVI    NULL     AP



We have to give the rank based on NULL values and NULL value column position.
Let us assume column positions as
1 2 3 4 5
ID CLASS NAME DIST_ID DIST_NAME

for the follwing three records number of NULL values are same.
but positions are different.

0 NULL NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
NULL C123 RAVI NULL AP

Based on the column postions the ranks as
2*2+3*3=13
1*1+3*3=10
2*2+3*3=13
1*1+4*4=17
Which is having high rank(gratest number) that record should come last
The record which is having all values that should come first
The record which is having all NULL values should come last.
The out put I want as
ID     CLASS  NAME   DIST_ID  DIST_NAME
0      C123   RAJESH  0        AP
0      NULL   KIRAN   0        AP
0      C1213  NULL    0        AP
NULL   C1234  NULL    0        AP
0      NULL   NULL    0        AP
0      NULL   NULL    0        AP
NULL   C123   RAVI    NULL     AP
NULL   NULL   NULL    NULL     NULL


Please help me.

Thanks.
Re: Query to find number of NULLs for each record. [message #593555 is a reply to message #593554] Sun, 18 August 2013 10:12 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
Just use function NVL2 in ORDER BY.

SY.
Re: Query to find number of NULLs for each record. [message #593557 is a reply to message #593555] Sun, 18 August 2013 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 22899
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Query to find number of NULLs for each record. [message #593559 is a reply to message #593557] Sun, 18 August 2013 11:01 Go to previous messageGo to next message
ramya_162
Messages: 97
Registered: August 2013
Location: Banglore
Member
Extremely sorry for violation forum guidelines.

Please help me.

Thanks.
Re: Query to find number of NULLs for each record. [message #593560 is a reply to message #593554] Sun, 18 August 2013 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59400
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Query to find number of NULLs for each record. [message #593561 is a reply to message #593559] Sun, 18 August 2013 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59400
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please help me.


See my mail just below your latest one.
Also Solomon gage you a hint, try to apply it and come back with your tries.

Regards
Michel

[Updated on: Sun, 18 August 2013 11:03]

Report message to a moderator

Re: Query to find number of NULLs for each record. [message #593564 is a reply to message #593561] Sun, 18 August 2013 11:10 Go to previous messageGo to next message
BlackSwan
Messages: 22899
Registered: January 2009
Senior Member
>Extremely sorry for violation forum guidelines.
Just post the necessary details to correct the situation.
Re: Query to find number of NULLs for each record. [message #593565 is a reply to message #593561] Sun, 18 August 2013 11:28 Go to previous messageGo to next message
ramya_162
Messages: 97
Registered: August 2013
Location: Banglore
Member
Hi All,

I am posting the required details.

My version is 11.2.0.3

I am posting test cases.

CREATE TABLE class_data
(
  ID                 NUMBER,
  CLASS         VARCHAR2(10 BYTE),
  NAME          VARCHAR2(25 BYTE),
  DIST_ID         VARCHAR2(10 BYTE),
  DIST_NAME           VARCHAR2(10 BYTE));

INSERT INTO class_data VALUES(0,NULL,'KIRAN','0','AP');
INSERT INTO class_data VALUES(0,'C1213',NULL,'0','AP');
INSERT INTO class_data VALUES(0,NULL,NULL,'0','AP');
INSERT INTO class_data VALUES(NULL,'C1234',NULL,'0','AP');
INSERT INTO class_data VALUES(0,NULL,NULL,'0','AP');
INSERT INTO class_data VALUES(NULL,NULL,NULL,NULL,NULL);
INSERT INTO class_data VALUES(0,'C123','RAJESH','0','AP');
INSERT INTO class_data VALUES(NULL,'C123','RAVI',NULL,'AP');

The output should be

ID     CLASS  NAME   DIST_ID  DIST_NAME
0      C123   RAJESH  0        AP
0      NULL   KIRAN   0        AP
0      C1213  NULL    0        AP
NULL   C1234  NULL    0        AP
0      NULL   NULL    0        AP
0      NULL   NULL    0        AP
NULL   C123   RAVI    NULL     AP
NULL   NULL   NULL    NULL     NULL


Please help me.

Thanks.
Re: Query to find number of NULLs for each record. [message #593567 is a reply to message #593565] Sun, 18 August 2013 11:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
I gave you a hint. Use it. Show us what you came up with.

SY.
Re: Query to find number of NULLs for each record. [message #593568 is a reply to message #593567] Sun, 18 August 2013 11:45 Go to previous messageGo to next message
ramya_162
Messages: 97
Registered: August 2013
Location: Banglore
Member
Hi,

I have tried this but not giving expected result.

SELECT * FROM class_data
ORDER BY NVL2(id,0,1),
NVL2(class,0,1),NVL2(name,0,1),NVL2(dist_id,0,1),
NVL2(dist_name,0,1)


Please help me

Thanks.
Re: Query to find number of NULLs for each record. [message #593570 is a reply to message #593568] Sun, 18 August 2013 11:55 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
Why comma-separated list in ORDER BY if your formula says +? Also, check what NVL2 agrument is used to replace null value and what NVL2 agrument is used to replace not null value. And your formula uses column_number * column_number while your NVL2 doesn't.

SY.

[Updated on: Sun, 18 August 2013 11:56]

Report message to a moderator

Re: Query to find number of NULLs for each record. [message #593575 is a reply to message #593570] Sun, 18 August 2013 13:03 Go to previous messageGo to next message
ramya_162
Messages: 97
Registered: August 2013
Location: Banglore
Member
Thank you very much for your suggestion.
Re: Query to find number of NULLs for each record. [message #593576 is a reply to message #593575] Sun, 18 August 2013 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59400
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you then find a solution?
If so, please, post it.

Regards
Michel
Re: Query to find number of NULLs for each record. [message #593578 is a reply to message #593576] Sun, 18 August 2013 15:09 Go to previous messageGo to next message
ramya_162
Messages: 97
Registered: August 2013
Location: Banglore
Member
Hi,

I have done like this.

SELECT * FROM class_data
ORDER BY ((NVL2(id,0,1)*NVL2(id,0,1))+
(NVL2(class,0,2)*NVL2(class,0,2))+(NVL2(name,0,3)*NVL2(name,0,3))+(NVL2(dist_id,0,4)*NVL2(dist_id,0,4))+
(NVL2(dist_name,0,5)*NVL2(dist_name,0,5)))

I have a question we are performing some math operation I think
it will hampers the performance.

The table is having 90000 records.

Is there any way to achieve this with best performance.

Please help me
Thanks.
Re: Query to find number of NULLs for each record. [message #593579 is a reply to message #593578] Sun, 18 August 2013 15:47 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
You overcomplicated things:

SELECT  *
  FROM  class_data
   ORDER BY NVL2(id,0,1) + NVL2(class,0,4) + NVL2(name,0,9) + NVL2(dist_id,0,16) + NVL2(dist_name,0,25)
/


SY.
Re: Query to find number of NULLs for each record. [message #593584 is a reply to message #593578] Mon, 19 August 2013 00:04 Go to previous message
Michel Cadot
Messages: 59400
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The table is having 90000 records.


This is really tiny.

Regards
Michel
Previous Topic: Help in using record type and object type
Next Topic: ORA-00905, ORA-06550
Goto Forum:
  


Current Time: Mon Oct 20 08:11:24 CDT 2014

Total time taken to generate the page: 0.12593 seconds