IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Getting an sqlcode of -401 for the following sql in my cobol


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
divya_maddi

New User


Joined: 03 Nov 2005
Posts: 33

PostPosted: Thu Jul 05, 2007 5:02 pm
Reply with quote

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
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India

PostPosted: Thu Jul 05, 2007 5:05 pm
Reply with quote

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
View user's profile Send private message
divya_maddi

New User


Joined: 03 Nov 2005
Posts: 33

PostPosted: Thu Jul 05, 2007 5:52 pm
Reply with quote

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
View user's profile Send private message
divya_maddi

New User


Joined: 03 Nov 2005
Posts: 33

PostPosted: Thu Jul 05, 2007 5:56 pm
Reply with quote

please note that i do not have dba rights
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Thu Jul 05, 2007 11:03 pm
Reply with quote

I don't think you can use SELECT after the THEN. SELECT is not an expression.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Jul 05, 2007 11:41 pm
Reply with quote

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
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Fri Jul 06, 2007 5:58 pm
Reply with quote

-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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Replace each space in cobol string wi... COBOL Programming 2
No new posts COBOL -Linkage Section-Case Sensitive COBOL Programming 1
No new posts COBOL ZOS Web Enablement Toolkit HTTP... COBOL Programming 0
No new posts Calling DFSORT from Cobol, using OUTF... DFSORT/ICETOOL 5
No new posts Generate random number from range of ... COBOL Programming 3
Search our Forums:

Back to Top