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
 

 

how to handle nullable columns ( may have null or values )

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

New User


Joined: 17 Jun 2010
Posts: 72
Location: Chennai

PostPosted: Wed Jun 05, 2013 3:42 pm    Post subject: how to handle nullable columns ( may have null or values )
Reply with quote

Hi Friends,

I have situvation to select value using nullable colums.But that nullable columns may have values .

exp

select t1.c1,t1.c2 from table1 t1 where
t1.c3=:t1.c3
and t1.c4=:t1.c4
and t1.c5=:t1.c5 ;

here C5 is a nullable columns.But some record have value ,some records have null

while using this query
what value we need to pass t.c5 ,if value is null ?
what value we need to pass t.c5 ,if the columns have value ?

Both condition we are using same query.

Note : I have moved -1 value to that null idicator value while C5 have value spaces.
Back to top
View user's profile Send private message

chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Jun 05, 2013 4:32 pm    Post subject:
Reply with quote

Hi,

If its null then you have to populate Null indicator column with -1 so that it will treat the column as null irrespective what value actual host variable contains...also here you need to decide when you want to treat the column as null

If it's not null then you have to populate Actual Host variable with the input value and null indicator with 0.

You need to pass Null indicator in your query which I can't see in the query provided.

What you have tried so far and what issues you are facing?

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

New User


Joined: 17 Jun 2010
Posts: 72
Location: Chennai

PostPosted: Wed Jun 05, 2013 5:09 pm    Post subject: Reply to: how to handle nullable columns ( may have null or
Reply with quote

Hi chandan,

I have handle like the below,But i got +100 while select nullable column row

if move t1.c5 = spaces
move -1 to ws-nul-c5
end-if
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Jun 05, 2013 5:23 pm    Post subject:
Reply with quote

As I mentioned you need to provie null indicator in query which I can't see

Your query needs to be like below

Code:
select t1.c1,t1.c2 from table1 t1 where
t1.c3=:t1.c3
and t1.c4=:t1.c4
and t1.c5=:t1.c5 :ws-nul-c5
Back to top
View user's profile Send private message
cmsmoon

New User


Joined: 17 Jun 2010
Posts: 72
Location: Chennai

PostPosted: Wed Jun 05, 2013 5:32 pm    Post subject:
Reply with quote

I tryed like this also .but still i got +100
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Jun 05, 2013 6:25 pm    Post subject:
Reply with quote

Then I would say there is no data in table which satisfies the all WHERE conditions..

Can you have some display's in program for input values and check if you are passing all things correctly to your query
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Wed Jun 05, 2013 8:15 pm    Post subject:
Reply with quote

I am afraid -1 comes to picture while updating/insert of the table and not while select

And As I see you are using a host variable so how do you know if the table has null or not null values for the combination of c4 & c5 fields?

so I belive instead of Null indicator may be an additional IS NULL check needed
Back to top
View user's profile Send private message
cmsmoon

New User


Joined: 17 Jun 2010
Posts: 72
Location: Chennai

PostPosted: Wed Jun 05, 2013 8:18 pm    Post subject:
Reply with quote

ya.I used like below

select t1.c1,t1.c2 from table1 t1 where
t1.c3=:t1.c3
and t1.c4=:t1.c4
and ( t1.c5 is NILL
or t1.c5=:t1.c5 ) ;
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Wed Jun 05, 2013 8:35 pm    Post subject:
Reply with quote

And did it solve the issue?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Wed Jun 05, 2013 8:47 pm    Post subject:
Reply with quote

It should be IS NULL and not IS NILL
Back to top
View user's profile Send private message
cmsmoon

New User


Joined: 17 Jun 2010
Posts: 72
Location: Chennai

PostPosted: Wed Jun 05, 2013 8:57 pm    Post subject:
Reply with quote

cmsmoon wrote:
ya.I used like below

select t1.c1,t1.c2 from table1 t1 where
t1.c3=:t1.c3
and t1.c4=:t1.c4
and ( t1.c5 is NILL
or t1.c5=:t1.c5 ) ;


The above changes has been working fine
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Wed Jun 05, 2013 8:59 pm    Post subject:
Reply with quote

Thanks for letting us know
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 06, 2013 12:34 am    Post subject:
Reply with quote

Hello,

Quote:
ya.I used like below

select t1.c1,t1.c2 from table1 t1 where
t1.c3=:t1.c3
and t1.c4=:t1.c4
and ( t1.c5 is NILL
or t1.c5=:t1.c5 ) ;

Quote:
The above changes has been working fine
I suspect this is not quite true . . .

There is no NILL in DB2 . . . At least not that i'm aware of.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Thu Jun 06, 2013 12:38 am    Post subject:
Reply with quote

Dick,

I believe those are very bad effects of type than copy paste
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 06, 2013 12:45 am    Post subject:
Reply with quote

Yup, i believe so also . . .

Bummer icon_neutral.gif

d
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Thu Jun 06, 2013 7:44 am    Post subject:
Reply with quote

cmsmoon wrote:


select t1.c1,t1.c2 from table1 t1 where
t1.c3=:t1.c3
and t1.c4=:t1.c4
and ( t1.c5 is NULL -- Corrected NILL
or t1.c5=:t1.c5 ) ;


I just suspect of possibility of returning multiple rows with above query.

if for a given combination of C3 and C4 what if there are two rows where one rows has valid value for C5 and other has Null for C5?

Learning lesson for me that Null indicator comes in picture for Insert and update I should have tested it if it works with select . I will check it today.

For Above query to avoid multiple rows can you check below. This is untested as I don't have access to mainframes right now

Code:
select t1.c1,t1.c2 from table1 t1 where
t1.c3=:t1.c3
and t1.c4=:t1.c4
and t1.c5 =  case when :t1.c5 = spaces then null else :t1.c5 end
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Thu Jun 06, 2013 8:52 am    Post subject:
Reply with quote

As a friend of mine once said, "Knowing only one way to spell a word shows a lack of creativity." icon_lol.gif
Back to top
View user's profile Send private message
cmsmoon

New User


Joined: 17 Jun 2010
Posts: 72
Location: Chennai

PostPosted: Thu Jun 06, 2013 10:16 am    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

Quote:
ya.I used like below

select t1.c1,t1.c2 from table1 t1 where
t1.c3=:t1.c3
and t1.c4=:t1.c4
and ( t1.c5 is NILL
or t1.c5=:t1.c5 ) ;

Quote:
The above changes has been working fine
I suspect this is not quite true . . .

There is no NILL in DB2 . . . At least not that i'm aware of.


SORRY
it should be NULL


select t1.c1,t1.c2 from table1 t1 where
t1.c3=:t1.c3
and t1.c4=:t1.c4
and ( t1.c5 is NULL
or t1.c5=:t1.c5 ) ;
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Thu Jun 06, 2013 3:57 pm    Post subject:
Reply with quote

Hi,
Apologies..

The CASE statement provided by me will not work and also Null Indicator in WHERE clause also not work

Just you need to take care of possibility of returning multiple rows with OR clause in final query

Regards,
Chandan
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
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 Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am


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