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
 

 

DB2:facing problem while using null in where clause

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
l.nethaji

New User


Joined: 16 Mar 2008
Posts: 90
Location: tamil nadu

PostPosted: Thu Mar 27, 2008 7:26 pm    Post subject: DB2:facing problem while using null in where clause
Reply with quote

This is the brief description of the problem scenario :

Lets consider the following is the table T1 with some 6 records.


c1 c2 c3 c4 C5 C6
31 A null 4 null 6/19/2007
11 A null 4 null 3/12/2008
22 A null 4 0 6/27/2007
41 A 0 4 0 3/17/2008
45 A 0 4 0 6/28/2008
54 A 0 4 null 6/28/2007


I have to group the similar records based on( c2,c3,c4,c5) c1 is the primary key.If all these columns from c2 to c5 are similar I must group them and get the max(c6) data in it.
once i get the record having the max timestamp ie max(c6) i must delete the remaining records which i got in the group by clause.

The columns from c2 to c5 is nullable columns
I am using select using the following query

Query Q1:

Select c2,c3,c4,c5 ,max(c6),count(*) as cn
From t1 group by c2,c3,c4,c5
Having count(*) >1


The result set I get is


c2 c3 c4 C5 C6 CN
A null 4 null 3/12/2008 2
A 0 4 0 6/28/2008 2




I am fetching this into temp working storage variable wc2,wc3,wc4,wc5,wc6,wcn
if the values are null i am moving this to null indicator (ind-c2,ind-c3,ind-c4,ind-c5)
Now if I want to write a query to delete the remaining records got in the group by clause.
ie remaining records which are to be deleted are


c1 c2 c3 c4 C5 C6
31 A null 4 null 6/19/2007
41 A 0 4 0 3/17/2008


Since these records only come under group by clause

Query Q2:

The following is the query I am using for deletion where I am giving the input from query Q1 to query Q2.

delete from t 1
where
C2= :wc2 :ind-c2 and
C3=:wc3 :ind-c3 and
C4=:wc4 :ind-c4 and
C5=:wc5 :ind-c5 and
C6 < : wc6 ( timestamp field)


If I am using this query I am not able to get the correct result as c3 and c5 can be null or any numeric value
The query must work for both null and numeric value .
I am getting -305 error as I am not able to fetch the null values.

I am not able to delete the required records . the records that should not be deleted are

The records that should not be deleted are these records doesn’t come under group by clause

c1 c2 c3 c4 C5 C6
22 A null 4 0 6/27/2007
54 A 0 4 null 6/28/2007

Please help me out to resolve this issue.

Thanks in advance

L.Nethaji
Back to top
View user's profile Send private message

Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 788
Location: Chennai, India

PostPosted: Thu Mar 27, 2008 8:11 pm    Post subject:
Reply with quote

Please use null indicators for all nullable columns and see Programmer Response for -305 code from MVS QW...
Back to top
View user's profile Send private message
l.nethaji

New User


Joined: 16 Mar 2008
Posts: 90
Location: tamil nadu

PostPosted: Mon Mar 31, 2008 4:23 pm    Post subject: Hi gnanam
Reply with quote

Please see the new post having the subject : facing problem with null values.

So that u can get the scenario i am facing .
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10231
Location: italy

PostPosted: Mon Mar 31, 2008 4:40 pm    Post subject: Reply to: DB2:facing problem while using null in where claus
Reply with quote

You started three threads on the same problem/subject ...

not everybody is able to see the attachments, and this reduces the number of people who can help

take a decision on which thread You want to keep alive...
posting ... "I WANT TO KEEP THIS ONE"
and I' ll delete the others
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
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm
No new posts ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts SYMNAMES problem jacobdng DFSORT/ICETOOL 7 Thu Dec 22, 2016 7:47 am
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm


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