Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Query to select Null row on priority
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Thu Jan 21, 2016 5:45 pm    Post subject: Query to select Null row on priority
Reply with quote

There is a table, let's say for sake of simplicity,Date_Table with two columns: Key_Column and a Date_column. My requirement:
To fetch that row where the date is NULL or else with oldest date.

Sample data:
Code:
AA 2014-01-06
AA 1984-01-06
AA ----------


I tried with the query:
Code:
SELECT Key_Column, COALESCE(Date_Column,'0001-01-01')
FROM Date_table
WHERE Key_Column = 'AA'
ORDER BY Date_Column ASC
FETCH FIRST ROW ONLY


But it's not returning the required row.

Expected row:
Code:
AA 0001-01-01


Is there any way I can get the desired row without using cursor.
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Thu Jan 21, 2016 7:40 pm    Post subject:
Reply with quote

"AND date_column = NULL" ?
If you want the null value row then you have to specify it just like you specified AA for the row key - duh!
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Jan 22, 2016 10:13 am    Post subject:
Reply with quote

Hi,

Change the order by as below

Code:
ORDER BY 2 ASC


Thanks,
Chandan
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Jan 22, 2016 4:08 pm    Post subject:
Reply with quote

Nicc,

With due respect should it be = NULL or IS NULL?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Sat Jan 23, 2016 4:56 am    Post subject:
Reply with quote

Dunno - just doing it off the top of my head without looking at any manual (hence the '?' at the end!).
Back to top
View user's profile Send private message
DavidMig

New User


Joined: 22 Jan 2016
Posts: 1
Location: Spain

PostPosted: Sat Jan 23, 2016 12:49 pm    Post subject:
Reply with quote

Should be IS NULL.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sat Jan 23, 2016 1:06 pm    Post subject:
Reply with quote

I'm sorry for not being clear. I'm getting the key from a file and have to fetch the date from the table. What I meant was I have to fetch the oldest date for a key. But in case if for a key a null value is present with other values then I want fetch the row with null.

For ex..
if the data is as below
Code:
AA 2014-01-06
AA 1984-01-06
AA 1957-01-06

then expected row to be returned is
Code:
AA 1957-01-06

And if data is as below
Code:
AA 2014-01-06
AA 1984-01-06
AA ----------

then expected row to be returned is
Code:
AA ----------


So I can't use IS NULL.

Hope I'm clear now. Please let me know if you have any follow up questions.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Sat Jan 23, 2016 9:54 pm    Post subject: Reply to: Query to select Null row on priority
Reply with quote

Use 2 queries. First one with Where condition as 'DATE IS NULL'. If you get SQL code as 100 then second query with Order by Date asc (fetch first row only) or you can use MIN function.

.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Mon Jan 25, 2016 7:14 pm    Post subject:
Reply with quote

Thanks Rahul. This is really a good idea rather than using a cursor and running a perform loop. Thanks a lot.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Jan 27, 2016 11:06 am    Post subject:
Reply with quote

Hi Mistath,

I am not clear why the earlier solution not working for you?

If you use Order by 2 like below instead of ORDER BY DATE_COLUMN it should work. This is what I have suggested in my earlier post.


Code:
SELECT Key_Column, COALESCE(Date_Column,'0001-01-01')
FROM Date_table
WHERE Key_Column = 'AA'
ORDER BY 2 ASC
FETCH FIRST ROW ONLY


Let me know if I am missing anything here. Just curious to know do we really need two queries here?

Also what if there are two rows with NULL value?

Thanks,
Chandan
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Jan 27, 2016 2:32 pm    Post subject:
Reply with quote

Quote:
Code:
Order by 2 like below instead of ORDER BY DATE_COLUMN

I don't understand why do you think this will behave differently.

When there are null value present, the query ignore the null and returns the row with oldest value.

Quote:
Also what if there are two rows with NULL value?

That's fine. As they are identical, they won't have nay impact on further processing. Same in case of row with two valid and equal dates.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Jan 27, 2016 2:48 pm    Post subject:
Reply with quote

mistah kurtz wrote:

I don't understand why do you think this will behave differently.


have you tried to execute the query with giving ORDER BY 2? Are you getting the same results?

it will work differently because ORDER BY DATE_COLUMN will apply the order by clause on value of DATE_COLUMN and it will still have NULL even you are giving COALESCE in query

Where as with ORDER BY 2 order by clause will be applicable to value returning as result of COALESCE function which will '0001-01-01' for a null column
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Jan 27, 2016 3:52 pm    Post subject:
Reply with quote

Quote:
have you tried to execute the query with giving ORDER BY 2?


Yes. I'm getting the same results?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Wed Jan 27, 2016 4:12 pm    Post subject: Reply to: Query to select Null row on priority
Reply with quote

the order by clause does not give the expected result because You ( all ) did not read the manual

Quote:
...
Ordering is performed in accordance with the comparison rules described in Language elements. The null value is higher than all other values. If your ordering specification does not determine a complete ordering, rows with duplicate values of the last identified sort-key have an arbitrary order. If you do not specify ORDER BY, the rows of the result table have an arbitrary order.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Jan 27, 2016 5:25 pm    Post subject:
Reply with quote

Hi Enrico,

with due respect, I am aware about above from manual.

I have posted above solution after testing it out in SPUFI and its working for me

Also I had worked on similar queries and those did work for me

I am just wondering why its not working e results for Mistah..

Regards,
Chandan
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Wed Jan 27, 2016 5:53 pm    Post subject:
Reply with quote

Code:
ORDER BY 2 ASC

Quote:
The null value is higher than all other values

therefore the sequence is 1,2,3,NULL so returning first row returns 1 not NULL.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Wed Jan 27, 2016 8:36 pm    Post subject: Reply to: Query to select Null row on priority
Reply with quote

Agree with Chandan. There is a difference between having Order by with 'Date_Column' and having it as Order by '2'.

Mistah, I think you haven't tried what Chandan had suggested. Run it and you'll get your desired result.

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Jan 27, 2016 10:23 pm    Post subject:
Reply with quote

Why not use a CASE WHEN NULL then low date else min(date)?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Thu Jan 28, 2016 12:54 am    Post subject:
Reply with quote

Rohit Umarjikar wrote:
Why not use a CASE WHEN NULL then low date else min(date)?
Because he doesn't want the date.

He wants the row (all column values) when the date is null / min value when not null, through a single select query.

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Jan 28, 2016 8:14 am    Post subject:
Reply with quote

Yes ,so Group by on first column should work then.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us