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
 

 

Passing Value in DB2 Query.
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Thu Nov 19, 2009 10:38 pm    Post subject: Passing Value in DB2 Query.
Reply with quote

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

Code:
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 [color=red]C-FAM-COD-WILD [/color]
 END-EXEC.           




C-FAM-COD-WILD is a variable in declared as a Normal Variable in W-S-Section .

Let me explain the scenario:
I have 3 char input in file, eg DSO
and I have to run a query for
G.GVARTRN.C_SYS_FAM LIKE %DSO

and so on for many (3 Char)inputs.

How to pass this value in EXEC SQL section.

Thanks & Regards,
Back to top
View user's profile Send private message

t5590ag

Active User


Joined: 21 May 2009
Posts: 139
Location: United States

PostPosted: Thu Nov 19, 2009 10:48 pm    Post subject:
Reply with quote

try to put '%DSO' in WS variable..but not sure. I dont think putting in WS var is gonna work like that.
Back to top
View user's profile Send private message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Thu Nov 19, 2009 10:52 pm    Post subject:
Reply with quote

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

How to implement this..
Back to top
View user's profile Send private message
t5590ag

Active User


Joined: 21 May 2009
Posts: 139
Location: United States

PostPosted: Thu Nov 19, 2009 10:57 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Nov 19, 2009 11:01 pm    Post subject:
Reply with quote

what is the definiton of C_SYS_FAM?
Back to top
View user's profile Send private message
t5590ag

Active User


Joined: 21 May 2009
Posts: 139
Location: United States

PostPosted: Thu Nov 19, 2009 11:03 pm    Post subject:
Reply with quote

where c_sys_fam bteween 'aaadso' and 'zzzdso'..smthing like that can be used.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Nov 19, 2009 11:09 pm    Post subject:
Reply with quote

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


and

Quote:
where c_sys_fam bteween 'aaadso' and 'zzzdso'..smthing like that can be used.
12.gif 36_11_6.gif
Back to top
View user's profile Send private message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Thu Nov 19, 2009 11:16 pm    Post subject:
Reply with quote

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....
Back to top
View user's profile Send private message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Thu Nov 19, 2009 11:18 pm    Post subject:
Reply with quote

ALSO FOR
Quote:
where c_sys_fam bteween 'aaadso' and 'zzzdso'..smthing like that can be used.


'DSO' is in a W-S-VARIABLE WHICH CAME TO COBOL FROM A FILE.

SO ITS SAME AS USING %DSO....
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Nov 19, 2009 11:35 pm    Post subject:
Reply with quote

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 :
Back to top
View user's profile Send private message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Thu Nov 19, 2009 11:43 pm    Post subject:
Reply with quote

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.

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


Code:
 STRING '%' DELIMITED BY SIZE                           
       FAMLI-COD DELIMITED BY SPACE                     
       INTO :C-FAM-COD-WILD                             
PERFORM 0120-FETCH-GVARTRN-CSRF.


FAMLI-COD is a w-s variable which is used to store the input from FILE, like in this case FAMLI-COD = DSO

Hope it explained my senarieo.

Regards,
Back to top
View user's profile Send private message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Thu Nov 19, 2009 11:55 pm    Post subject:
Reply with quote

Following is the error i m getting while compiling, when using the above sceanrio.
Quote:
DSNHSMUD LINE 148 COL 45 UNDEFINED OR UNUSABLE HOST VARIABLE "C-FAM-COD-WILD"


Thanks.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Nov 20, 2009 12:17 am    Post subject:
Reply with quote

Quote:
INTO :C-FAM-COD-WILD


you need the : as the leading character of a working-storage (or linkage) item used within an SQL statement.

you don't need it in COBOL statements.

the string statement will cause a COBOL error once you fix the below error:

the error you displayed is an SQL syntax error displayed by the DB2 precompiler.

you probably coded your SQL 'LIKE C-FAM-COD-WILD'
instead of 'LIKE :C-FAM-COD-WILD'

and since you need to recode/recompile, put a display statement
DISPLAY C-FAM-COD-WILD
immediately after the STRING statement and before the PERFORM.
Back to top
View user's profile Send private message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Fri Nov 20, 2009 12:33 am    Post subject:
Reply with quote

DO you mean
Code:
WORKING-STORAGE SECTION.
01 :C-FAM-COD-WILD PIC X(4).


And

Code:
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.


And

Code:
STRING '%' DELIMITED BY SIZE         
       FAMLI-COD DELIMITED BY SPACE 
       INTO :C-FAM-COD-WILD           
PERFORM 0110-OPEN-GVARTRN-CSRF       
PERFORM 0120-FETCH-GVARTRN-CSRF UNTIL


Now the Error is:
Code:
ILLEGAL SYMBOL "C-FAM-COD-WILD". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: :



Or is there something else you are saying?

And what do you mean by
Quote:
you don't need it in COBOL statements.


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

Active User


Joined: 21 May 2009
Posts: 139
Location: United States

PostPosted: Fri Nov 20, 2009 1:05 am    Post subject: Reply to: Passing Value in DB2 Query.
Reply with quote

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.
Back to top
View user's profile Send private message
t5590ag

Active User


Joined: 21 May 2009
Posts: 139
Location: United States

PostPosted: Fri Nov 20, 2009 1:11 am    Post subject:
Reply with quote

also the option which i suggested was like this in ur case-
Code:
                                 
 WHERE G.GVARTRN.C_SYS_FAM between ' DSO' and 'ZDSO'


this will fetch u evrything like- 1dso, 2dso, vdso, )dso. I understand. You just have to check what is the highest value of ur 1st character in that 4 character variable. use that in place of 'zdso'.

' dso' is the lowest possible value. hope u understand. Now u dont need a like.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7914
Location: Bellevue, IA

PostPosted: Fri Nov 20, 2009 1:20 am    Post subject:
Reply with quote

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'.
Back to top
View user's profile Send private message
t5590ag

Active User


Joined: 21 May 2009
Posts: 139
Location: United States

PostPosted: Fri Nov 20, 2009 12:27 pm    Post subject:
Reply with quote

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 ?
Back to top
View user's profile Send private message
t5590ag

Active User


Joined: 21 May 2009
Posts: 139
Location: United States

PostPosted: Fri Nov 20, 2009 12:28 pm    Post subject:
Reply with quote

also what are actually the lowest and highest values then and their hex ?
Back to top
View user's profile Send private message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Fri Nov 20, 2009 5:50 pm    Post subject: Thanks All
Reply with quote

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.
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
Goto page 1, 2  Next
Page 1 of 2

 

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 FTP - JCL failed while passing FTP co... Suneetha1612 JCL & VSAM 12 Wed Nov 16, 2016 7:33 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


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