Using variable in Where clause

[mcauliff]

Is it possible to do the below.

ws-prd-codes is 'AH', 'AJ', 'A8', 'AK', 'AM', 'AO'

The SQL is

EXEC SQL
SELECT COUNT(*)
INTO :WS-NUMBER-CLOSED
FROM CALL
WHERE CLIENT_NO = :SP017-CLIENT-NO
AND SITE_NO = :SP017-SITE-NO
AND PRD_BASE_CD IN WS-PRD-CODES)
AND CALL_STATUS_CD IN ('C', 'U')
AND CALL_CLOSE_DT
BETWEEN :WS-START-MONTH AND :WS-END-MONTH
END-EXEC

When I execute this SQL, the count is zero. When I hard code the code in the SQL, the count is 10.

Please help.

 

[MarcLodge]

Mcauliff,
You can't do what you are trying to do, at least not the way you are trying to do it.

To explain your results, when you execute the sql DB2 will look for PRD_BASE_CD to be equal to one field whose value is "'AH', 'AJ', 'A8', 'AK', 'AM', 'AO'". This obviously comes back with 0 matches.

When it is hard coded, DB2 will recognise each separate field and examine them one by one, hence you get a result of 10.

Marc

 

[mcauliff]

Thanks for the reply Marc.

Is there a way of doing what I wnat to do? I'm hoping to allow the user to supply the codes via an input and then built the where clause portion.

 

[MarcLodge]

You can do it if you are building dynamic sql, but some installations don't like/allow this as it allows potentially unchecked Sql to be run against prod.

Do you have any rules on what the user is to input? Like maximum/minimum number of codes?

 

[mcauliff]

The max is 10 codes, which is 20 characters. The input would look like this; AHAJA8AKAMAO

 

[MarcLodge]

The 'easiest' way to do this is to have 10 variables and include all of them in the where clause. Where the user only specifies 3 variables, then the remaining 7 should be initialised to something that the code cannot be, like spaces.

Another way is to create a temporary table on the fly, and insert each of the user values into it, and use the temporary table in the query.

Another way is to go down the dynamic SQL route and build the query at runtime.

Each of these methods have their positive and their negative side, for instance your installation might not allow temporary tables or dynamic sql. You will need to decide which one works best for you.

It's also quite possible that I have forgotten or overlooked another easier method, so maybe somebody else will come up with a better solution.

Regards,

Marc

 

[sathyarams]

Have a look at this recent thread ..

http://www.dbforums.com/showthread.php?t=1615301

I have posted a solution for your scenario (though for the original poster of the thread, it didn't help ;-( )

BTW, my solution in the thread will work only if you are on DB2 LUW or atleast on V8 DB2 zOS

Thanks
Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[mcauliff]

Thank you for the reply.

We are on DB2 V7 for zOS. No current intention of upgrading this year.