Using host variable with IN in SQL



IBM's flagship relational database management system

Using host variable with IN in SQL

Postby nikesh_rai » Thu Mar 06, 2014 10:15 pm

Hi Guys,

I have to write a SQL having where condition as given below:

Where Column_1 IN(:host_variable)

the host_variable is defined as

05 host_variable.
  10 host_1          PIC X(02).
  10 host_2          PIC X(02).
  10 host_3          PIC X(02).
  10 host_4          PIC X(02).
  10 host_5          PIC X(02).


Column_1 can have values IC, NV, MF, MS, GP, SPACES for Column_2 = 'A'
Column_1 can have values BN, SPACES Column_2 = 'B'

We don't want to fetch rows having Column_1 as SPACES.
Now, the issue is arising when I am trying to fetch values for Column_2 = 'B'
The value in host_variable is being passed as 'BN' which is as expected, however SPACES are also being passed here.. and while fetching the rows.. it is also fetching the rows having value Column_1 as SPACES

When I reduced the size of host_variable to 2 char only, it is working fine, however, for Column_2 = 'A', it should be 10 char. I have one more issue that I have to keep the host_variable as 10 char.

Please suggest on this..how I can avoid the rows for which Column_1 is SPACES
Thanks
Nikesh Rai
nikesh_rai
 
Posts: 192
Joined: Tue Oct 18, 2011 1:27 am
Has thanked: 17 times
Been thanked: 0 time

Re: Using host variable with IN in SQL

 

Re: Using host variable with IN in SQL

Postby nikesh_rai » Sat Mar 08, 2014 4:24 pm

yet.. no reply.. :)
Thanks
Nikesh Rai
nikesh_rai
 
Posts: 192
Joined: Tue Oct 18, 2011 1:27 am
Has thanked: 17 times
Been thanked: 0 time

Re: Using host variable with IN in SQL

Postby Robert Sample » Sat Mar 08, 2014 7:14 pm

yet.. no reply.. :)
This is NOT a good idea. People respond on this forum as they have the time and interest -- they are volunteers. For any given post, responses may -- or may not -- EVER be provided. If you are in such a hurry to get an answer, I recommend you pay a consultant for the answer. There are several on this forum and the rate is reasonable (typically about 1000 US dollars per day or equivalent in other currency) -- at least for US and European consultants. Trying to get a response like this is more likely to cause the people who do know the answer to not respond because of the increased chance you will bother them in the future.
Robert Sample
Global moderator
 
Posts: 3367
Joined: Sat Dec 19, 2009 8:32 pm
Location: East Dubuque, Illinois
Has thanked: 1 time
Been thanked: 222 times

Re: Using host variable with IN in SQL

Postby dick scherrer » Sun Mar 09, 2014 9:57 am

Hello,

How about having an additional bit of code that says AND COLUMN_1 not equal 1?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Using host variable with IN in SQL

Postby nikesh_rai » Mon Mar 10, 2014 6:24 pm

Robert Sample wrote:
yet.. no reply.. :)
This is NOT a good idea. People respond on this forum as they have the time and interest -- they are volunteers. For any given post, responses may -- or may not -- EVER be provided. If you are in such a hurry to get an answer, I recommend you pay a consultant for the answer. There are several on this forum and the rate is reasonable (typically about 1000 US dollars per day or equivalent in other currency) -- at least for US and European consultants. Trying to get a response like this is more likely to cause the people who do know the answer to not respond because of the increased chance you will bother them in the future.



Sorry Robert.. I didn't mean that.. :), yes it was little bit urgent.. thats why.. I posted
Thanks
Nikesh Rai
nikesh_rai
 
Posts: 192
Joined: Tue Oct 18, 2011 1:27 am
Has thanked: 17 times
Been thanked: 0 time

Re: Using host variable with IN in SQL

Postby nikesh_rai » Mon Mar 10, 2014 6:28 pm

dick scherrer wrote:Hello,

How about having an additional bit of code that says AND COLUMN_1 not equal 1?


Thanks DIck,

Actually I tried like this..

where Column_1 IN(:host_variable)
and Column_1 not = SPACES


but didn't work, it is throwing SQL error
Thanks
Nikesh Rai
nikesh_rai
 
Posts: 192
Joined: Tue Oct 18, 2011 1:27 am
Has thanked: 17 times
Been thanked: 0 time

Re: Using host variable with IN in SQL

Postby Terry Heinze » Mon Mar 10, 2014 6:59 pm

"it is throwing SQL error" does not tell us much. What IS that SQL error you get? The more information you can provide, the better the answers you will receive.
.... Terry
Terry Heinze
 
Posts: 193
Joined: Wed Dec 04, 2013 11:08 pm
Location: Richfield, MN, USA
Has thanked: 10 times
Been thanked: 11 times

Re: Using host variable with IN in SQL

Postby dick scherrer » Mon Mar 10, 2014 7:44 pm

Hello,

As Terry mentions, "but didn't work, it is throwing SQL error" gives us Nothing to use to help you . . .

The more urgent "something" is the more critical that you provide useful info .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Using host variable with IN in SQL

Postby nikesh_rai » Mon Mar 10, 2014 10:42 pm

Thanks Terry and Dick,

It was -104, however, I resolved this issue. I used the same condition

where Column_1 IN(:host_variable)
and Column_1 <> = SPACES


Previously, I was using NOT instead of <> and it was causing error. and the above where conditions resolved my problem. Now, even the SPACES are being passed to host_variable, the query is not fetching the row having Column_1 = SPACES
Thanks
Nikesh Rai
nikesh_rai
 
Posts: 192
Joined: Tue Oct 18, 2011 1:27 am
Has thanked: 17 times
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post