IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Query to select Null row on priority


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
mistah kurtz

Active User


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

PostPosted: Thu Jan 21, 2016 5:45 pm
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: 2455
Location: Hampshire, UK

PostPosted: Thu Jan 21, 2016 7:40 pm
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: 275
Location: Mumbai

PostPosted: Fri Jan 22, 2016 10:13 am
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

Global Moderator


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

PostPosted: Fri Jan 22, 2016 4:08 pm
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: 2455
Location: Hampshire, UK

PostPosted: Sat Jan 23, 2016 4:56 am
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
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: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sat Jan 23, 2016 1:06 pm
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: 446
Location: USA

PostPosted: Sat Jan 23, 2016 9:54 pm
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: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Mon Jan 25, 2016 7:14 pm
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: 275
Location: Mumbai

PostPosted: Wed Jan 27, 2016 11:06 am
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: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Jan 27, 2016 2:32 pm
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: 275
Location: Mumbai

PostPosted: Wed Jan 27, 2016 2:48 pm
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: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Jan 27, 2016 3:52 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Jan 27, 2016 4:12 pm
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: 275
Location: Mumbai

PostPosted: Wed Jan 27, 2016 5:25 pm
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: 2455
Location: Hampshire, UK

PostPosted: Wed Jan 27, 2016 5:53 pm
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: 446
Location: USA

PostPosted: Wed Jan 27, 2016 8:36 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Jan 27, 2016 10:23 pm
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: 446
Location: USA

PostPosted: Thu Jan 28, 2016 12:54 am
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

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

Yes ,so Group by on first column should work then.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Thu Jan 28, 2016 9:13 am
Reply with quote

Rohit Umarjikar wrote:
Yes ,so Group by on first column should work then.


If he wants to select all Columns then he will need to group by on all columns which I feel will be the overhead.

CASE function will also work same as COALESCE.

@Mistah..is it possible for you to provide queries and output for both using ORDER BY DATE_COLUMN and ORDER BY 2

Thanks,
Chandan
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Jan 29, 2016 3:01 am
Reply with quote

Let us not guess how many columns he has in select.

However , this should work.

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

Quote:

@Mistah..is it possible for you to provide queries and output for both using ORDER BY DATE_COLUMN and ORDER BY 2


Results would be different, Null is lower which comes first .
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Fri Jan 29, 2016 9:07 am
Reply with quote

Rohit Umarjikar wrote:


Results would be different, Null is lower which comes first .


I guess you have missed below post from Enrico

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.


Also the solution is provided by you is the same as per provided by me and both will work, but as per Mistah it's not working for him

Anyways this post is going way longer, let wait for Mistah to get back


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

Active User


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

PostPosted: Fri Jan 29, 2016 1:18 pm
Reply with quote

Hi Chandan

Sorry for the delay and confusion. I have restricted access to internet, so could not reply earlier. ORDER BY 2 is working just as I needed.

So as I understand the deifference is:
ORDER BY 2 ASC - ordering is done after the values are updated by COALESCE function
ORDER BY Date_Column ASC - ordering is done before COALESCE function is applied.

However if I don't use the COALESCE function, resulting rows will be same in both the cases.

Thanks a lot. It was new to me.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Fri Jan 29, 2016 1:47 pm
Reply with quote

No worries. Glad to know its working for you and I could help
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Null values are considered in Total c... DFSORT/ICETOOL 6
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
Search our Forums:

Back to Top