|
View previous topic :: View next topic
|
| Author |
Message |
Susanta
Active User
Joined: 17 Nov 2009
Posts: 126
Location: India
|
|
|
|
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.
|
|
| Back to top |
|
 |
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia
|
|
|
|
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
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
well thought out Phil,
and nicely said. |
|
| Back to top |
|
|
Susanta
Active User
Joined: 17 Nov 2009
Posts: 126
Location: India
|
|
|
|
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 |
|
|
Susanta
Active User
Joined: 17 Nov 2009
Posts: 126
Location: India
|
|
|
|
Now i got it Phil what you said.. sorry i took a long time to understand .
Thanks. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
|
|
