View previous topic :: View next topic
|
Author |
Message |
divya_maddi
New User
Joined: 03 Nov 2005 Posts: 33
|
|
|
|
Hi,
I am getting an sqlcode of -401 for the following sql in my cobol program. Please point out the mistake.
Code: |
EXEC SQL
SELECT
CASE WHEN TABLE1_TYP_CD = 'M' THEN
(SELECT TABLEM_FE_EFF_DT
FROM TABLEM, TABLE1
WHERE TABLE1_EFF_DT = '2006-09-12'
AND TABLEM_EFF_DT = '2006-09-12')
WHEN TABLE1_TYP_CD = 'N' THEN
(SELECT TABLEN_EFF_DT
FROM TABLEN, TABLE1
WHERE TABLE1_EFF_DT = '2006-09-12'
AND TABLEN_EFF_DT = '2006-09-12')
END INTO :TABLE1-EFF-DT
FROM TABLE1, TABLEM, TABLEN
WHERE TABLE1_EFF_DT = '2006-09-12'
END-EXEC. |
where TABLE1 is the parent table and TABLEM and TABLEN are its child tables. |
|
Back to top |
|
|
murmohk1
Senior Member
Joined: 29 Jun 2006 Posts: 1436 Location: Bangalore,India
|
|
|
|
Divya,
Explanation for -401.
Quote: |
Explanation: An arithmetic operation appearing within the SQL statement
contains a mixture of numeric and non-numeric operands, or the operands of
a comparison operation are not compatible. |
Also post the spool messages. |
|
Back to top |
|
|
divya_maddi
New User
Joined: 03 Nov 2005 Posts: 33
|
|
|
|
that error got solved, it was a data type mismatch, but now i have another problem when i am trying to select multiple columns inside the case statement. It is giving an sqlcode of -412. Is there any way to still proceed with the multiple select in the case statement? Note that I specifically want to use case statement for testing the efficiency of my code with case statement. |
|
Back to top |
|
|
divya_maddi
New User
Joined: 03 Nov 2005 Posts: 33
|
|
|
|
please note that i do not have dba rights |
|
Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007 Posts: 199 Location: Sri Lanka
|
|
|
|
I don't think you can use SELECT after the THEN. SELECT is not an expression. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Back to the original question:
Is there some reason to try to do all of this in one somewhat convoluted SELECT statement?
If you describe what you have in the 3 tables and what you want as the outcome when your process runs, we may be able to offer suggestions. |
|
Back to top |
|
|
prasadvrk
Active User
Joined: 31 May 2006 Posts: 200 Location: Netherlands
|
|
|
|
-412:Subquery with more than one result column not valid
SELECT TABLEM_FE_EFF_DT
FROM TABLEM, TABLE1
WHERE TABLE1_EFF_DT = '2006-09-12'
AND TABLEM_EFF_DT = '2006-09-12'
Quote: |
SELECT TABLEM_FE_EFF_DT
FROM TABLEM, TABLE1
WHERE TABLE1_EFF_DT = '2006-09-12'
AND TABLEM_EFF_DT = '2006-09-12'
|
Since result of the sub query can be multiple it seems you can not use this in the present scenario. |
|
Back to top |
|
|
|