Passing Value in DB2 Query.

GauravKudesiya · Posted: Thu Nov 19, 2009 10:38 pm

Hello,
Can anyone help me in Passing a variable in DB2 Query.
I write a Query:

t5590ag · Posted: Thu Nov 19, 2009 10:48 pm

try to put '%DSO' in WS variable..but not sure. I dont think putting in WS var is gonna work like that.

GauravKudesiya · Posted: Thu Nov 19, 2009 10:52 pm

Already tried that..

Not working, its saying it must be HOST variable, like :XYZ.
and value of :XYZ must ne '%DSO'

How to implement this..

t5590ag · Posted: Thu Nov 19, 2009 10:57 pm

like wont work in that way I understand..using a host variable..if you have some fixed number of chars in that variable you can use between.

dbzTHEdinosauer · Posted: Thu Nov 19, 2009 11:01 pm

what is the definiton of C_SYS_FAM?

t5590ag · Posted: Thu Nov 19, 2009 11:03 pm

where c_sys_fam bteween 'aaadso' and 'zzzdso'..smthing like that can be used.

dbzTHEdinosauer · Posted: Thu Nov 19, 2009 11:09 pm

and what is the defintion of C-FAM-COD-WILD?

and

GauravKudesiya · Posted: Thu Nov 19, 2009 11:16 pm

C_SYS_FAM is 4 char Value, where 1st char can be any char but last 3 character are in a file.
I took input from a file into a w-s variable.
Input is 3 char, but C_SYS_FAM si 4 char. i am not concerned with 1st char, i want all records with last 3 char which is in a variable.

i take C_SYS_FAM_WILD as PIC x(4) write '%' in place of 1st char and following by the input by using STRING.

Is there any option to execute this....

GauravKudesiya · Posted: Thu Nov 19, 2009 11:18 pm

ALSO FOR

dbzTHEdinosauer · Posted: Thu Nov 19, 2009 11:35 pm

have you verified that the working-storage variable C-FAM-COD-WILD
contains %DSO before the cursor is opened?

what sqlcode are you receiving during OPEN and FETCH?

and are you sure that the values of the column C_SYS_FAM, CHAR 4, are all upper case
and the DSO that you are looking for is 'right justified' in the 4 char column?

also, C-FAM-COD-WILD requires a :

GauravKudesiya · Posted: Thu Nov 19, 2009 11:43 pm

definition of Cursor:
EXEC SQL
DECLARE GVARTRN-CSRF CURSOR FOR
SELECT G.GVARTRN.N_TRANS
,G.GVARTRN.N_DATA_CNTR_ABRV
,G.GVARTRN.C_TRANS_TYP
,G.GVARTRN.C_SYS_FAM
,G.GVARTRN.L_IMS_CICS_IDENT
FROM G.GVARTRN
WHERE G.GVARTRN.C_SYS_FAM LIKE :C-FAM-COD-WILD
END-EXEC.

GauravKudesiya · Posted: Thu Nov 19, 2009 11:55 pm

Following is the error i m getting while compiling, when using the above sceanrio.

dbzTHEdinosauer · Posted: Fri Nov 20, 2009 12:17 am

GauravKudesiya · Posted: Fri Nov 20, 2009 12:33 am

DO you mean

t5590ag · Posted: Fri Nov 20, 2009 1:05 am

gaurav...remove ":" from string statement and ur wrking storage. just use it in ur query ":ur variable name". like this.this is what he meant.am in a hurry..so just chk this out.

t5590ag · Posted: Fri Nov 20, 2009 1:11 am

also the option which i suggested was like this in ur case-

Robert Sample · Posted: Fri Nov 20, 2009 1:20 am

A Gupta, if you're using the EBCDIC collating sequence there are a couple of mistakes in your post:

1. space is not the lowest possible value -- in EBCDIC there are 64 characters with lower values; ASCII only has 32 lower values. They aren't printing characters but they are still defined and exist and may be referenced.
2. <space>DSO to ZDSO will not include 0DSO, 1DSO, ... , 9DSO as EBCDIC has the Z at hex 'E9' in the collating sequence whereas zero is hex 'F0'.

t5590ag · Posted: Fri Nov 20, 2009 12:27 pm

Thanks for inputs Robert. I seriously am not good at DB2, just tried to give him the alternatives. Ok, if we string low-values/high values along with DSO in the host variable, in that case is BETWEEN going to give us desired results ?

t5590ag · Posted: Fri Nov 20, 2009 12:28 pm

also what are actually the lowest and highest values then and their hex ?

GauravKudesiya · Posted: Fri Nov 20, 2009 5:50 pm

The problem is resolved with removing ':' from WS variable ,
and using ':' in The Query.
and using STRING to add '%' in the C-SYS-FAM-WILD.

Thanks a lot to all for their time.

veeramanimurugesan · New User Joined: 03 Nov 2009 Posts: 19 Location: chennai

You could also try this,

SUBSTR(C_SYS_FAM,2,3) = 'DSO' OR

SUBSTR(C_SYS_FAM,2,3) = :C-FAM-COD-WILD

Use of SUBSTR could solve your problem in a much easier way..

Pls correct me if am wrong..