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

cmsmoon · New User Joined: 17 Jun 2010 Posts: 75 Location: Chennai

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.

chandan.inst · Posted: Wed Jun 05, 2013 4:32 pm

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

cmsmoon · New User Joined: 17 Jun 2010 Posts: 75 Location: Chennai

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

chandan.inst · Posted: Wed Jun 05, 2013 5:23 pm

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

Your query needs to be like below

cmsmoon · New User Joined: 17 Jun 2010 Posts: 75 Location: Chennai

I tryed like this also .but still i got +100

chandan.inst · Posted: Wed Jun 05, 2013 6:25 pm

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

Pandora-Box · Posted: Wed Jun 05, 2013 8:15 pm

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

cmsmoon · New User Joined: 17 Jun 2010 Posts: 75 Location: Chennai

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 ) ;

Pandora-Box · Posted: Wed Jun 05, 2013 8:35 pm

And did it solve the issue?

Pandora-Box · Posted: Wed Jun 05, 2013 8:47 pm

It should be IS NULL and not IS NILL

cmsmoon · New User Joined: 17 Jun 2010 Posts: 75 Location: Chennai

Pandora-Box · Posted: Wed Jun 05, 2013 8:59 pm

Thanks for letting us know

dick scherrer · Posted: Thu Jun 06, 2013 12:34 am

Hello,

Pandora-Box · Posted: Thu Jun 06, 2013 12:38 am

Dick,

I believe those are very bad effects of type than copy paste

dick scherrer · Posted: Thu Jun 06, 2013 12:45 am

Yup, i believe so also . . .

Bummer

d

chandan.inst · Posted: Thu Jun 06, 2013 7:44 am

Terry Heinze · Posted: Thu Jun 06, 2013 8:52 am

As a friend of mine once said, "Knowing only one way to spell a word shows a lack of creativity."

cmsmoon · New User Joined: 17 Jun 2010 Posts: 75 Location: Chennai

chandan.inst · Posted: Thu Jun 06, 2013 3:57 pm

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