Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 WHERE FIELD IN CLAUSE with variable arguments.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Susanta

Active User


Joined: 17 Nov 2009
Posts: 125
Location: India

PostPosted: Mon May 02, 2011 5:47 pm    Post subject: DB2 WHERE FIELD IN CLAUSE with variable arguments.
Reply with quote

Hi,

In my declare cursor i need to use a Where Filed IN clause .

Quote:
select name from tbl1 where field IN ( val1,val2)


In this case the arguments are variable, depending on a if condition it can be either
a) IN ('A','B') or b) IN('C')

Can i use a same alphanumeric variable to concatenate the two arguments ?

I know it can be done by using two variables (:A , :B) and db2 CASE statement. but i want to use a single variable by cobol string function, is it possible?

Thanks.

Quote:
Back to top
View user's profile Send private message

Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 965
Location: Richmond, Virginia

PostPosted: Mon May 02, 2011 6:59 pm    Post subject:
Reply with quote

I must admit to being confused by the question.

I do not see any concatenation in your example. Instead I see you want to look for either one value or two values, both constants.

Perhaps you mean, as your last line indicates, either one or two variable values. In that case, would you want


Code:
in (:A, :B)


where host vars A and B might have the same value? DB2/COBOL/anyone certainly doesn't care if two vars have the same value, and you should know that, so that can't be your question.

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

Global Moderator


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

PostPosted: Mon May 02, 2011 8:49 pm    Post subject:
Reply with quote

well thought out Phil,
and nicely said.
Back to top
View user's profile Send private message
Susanta

Active User


Joined: 17 Nov 2009
Posts: 125
Location: India

PostPosted: Tue May 03, 2011 10:40 am    Post subject:
Reply with quote

My situation is ..

My in clause is not same it depends on a condition.

one solution can be by writing two different declare cursor .
but i am not opting for that as the actual sql is very big and for a small change i need to write two almost repetative cursor.

I need some thing like below but it gives syntax error.
EXEC SQL
DECLARE CURSOR CSR1 FOR
SELECT NAME ,ADDRESS FROM TABLA1
WHERE ADDRESS IN ( CASE WHEN :TERM-TYP=1 THEN (:ADD1,:ADD2)
WHEN :TERM-TYP=2 THEN (:ADD3)
END)
END-EXEC

If i use only (:add1,:add2) for both cases,in case TERM-TYPE= 2 then if i use two arguments and if i pass value to one argument then resultant row set can be anything depending on the 2nd argument which i do not want.

one more doubt can i use cobol variable (:TERM-TYP) after WHEN ?

Please help.
Back to top
View user's profile Send private message
Susanta

Active User


Joined: 17 Nov 2009
Posts: 125
Location: India

PostPosted: Tue May 03, 2011 11:10 am    Post subject:
Reply with quote

Now i got it Phil what you said.. sorry i took a long time to understand .
icon_redface.gif

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

Global Moderator


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

PostPosted: Tue May 03, 2011 11:11 am    Post subject:
Reply with quote

Quote:
for that as the actual sql is very big

so what?

forcing DB2 to make decisions based on host variables (other than equality/non-equality) means access path is not going to be optimum.

making the decision with an COBOL IF statement and then processing one of several potential cursors is easier to follow than a complicated SQL which may, due to the CASE, result in a not-so-optimal access path.

if we were still using source cards as input for programs
(something that has not been required since the 70's)
you could have a case for smaller program (1 complicated sql).

but we are not, you do not.

and if you have to ask such as question:

Quote:
one more doubt can i use cobol variable (:TERM-TYP) after WHEN ?

you db2 skills are not sufficient to write a complicated sql.
stick with simple, make it easy to follow, and probably perform better.
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
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts Partial color change of a field in CI... waseem0424 CICS 5 Fri Sep 29, 2017 7:56 pm
No new posts Moving values to a variable of copybo... Vignesh Sid COBOL Programming 6 Wed Sep 06, 2017 1:04 pm
No new posts Remove leading spaces from numeric field rexx77 SYNCSORT 6 Wed Sep 06, 2017 2:15 am
No new posts JES2 job size field matching Windows ... SRICOBSAS All Other Mainframe Topics 4 Tue Sep 05, 2017 5:49 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us