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
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Getting an sqlcode of -401 for the following sql in my cobol
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: 1439
Location: Bangalore,India

PostPosted: Thu Jul 05, 2007 5:05 pm    Post subject:
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    Post subject: Re: Getting an sqlcode of -401 for the following sql in my c
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    Post subject: Re: Getting an sqlcode of -401 for the following sql in my c
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    Post subject:
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

Site Director


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

PostPosted: Thu Jul 05, 2007 11:41 pm    Post subject:
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    Post subject:
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    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
This topic is locked: you cannot edit posts or make replies. Cobol GUI (like Editor) Kala Mainframe COBOL Programming 14 Wed May 10, 2017 12:30 pm
No new posts IMS T-Pipe queue counts in a COBOL Pr... Siva NKK Kothamasu IMS DB/DC 0 Tue May 09, 2017 6:31 pm
No new posts COBOL Code Parsers and Lineage Establ... balimanja COBOL Programming 2 Tue May 02, 2017 3:30 am
No new posts Cobol upgrade - source code missing f... gthmrj IBM Tools 1 Wed Apr 26, 2017 6:04 pm
No new posts COBOL Programming Sandpit? jodrisco COBOL Programming 6 Wed Apr 12, 2017 3:47 am


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