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 DB2 sql code of -104

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

New User


Joined: 10 Aug 2007
Posts: 38
Location: Chicago

PostPosted: Mon Jan 04, 2010 8:37 pm    Post subject: Getting DB2 sql code of -104
Reply with quote

Hello:

Is this query legal? Obviously, I get a DB2 sql code of -104, this means that something isn't quite right. Can someone point out the problem please?

Code:
  SELECT A.INDIV_KEY
        ,A.EMP_NAME
        ,A.DEPT_NAME
        ,CASE
             WHEN (SELECT 1
                   FROM DINESHS.EMPLOYEE2 N
                   WHERE N.INDIV_KEY <> A.INDIV_KEY) = 1
             THEN
                  (INSERT INTO
                   DINESHS.EMPLOYEE2
                   VALUES (A.INDIV_KEY,A.EMP_NAME,A.DEPT_NAME
                          ,A.REFRESH_KEY,'9999-12-31')) AND
                  (INSERT INTO
                   DINESHS.EMPLOYEE4
                   VALUES (A.INDIV_KEY,A.EMP_NAME,A.DEPT_NAME
                       ,A.REFRESH_KEY,'9999-12-31',CURRENT TIMESTAMP))
             WHEN (SELECT 1
                    FROM DINESHS.EMPLOYEE2 N
                    WHERE N.INDIV_KEY = A.INDIV_KEY) <> 1
             THEN
                  (UPDATE DINESHS.EMPLOYEE2 E2
                   SET VALID_TO = (SELECT (VALID_FROM - 1 DAY)
                                   FROM   DINESHS.EMPLOYEE3 E3
                                   WHERE  E3.INDIV_KEY = (
                                   SELECT E4.INDIV_KEY
                                   FROM   DINESHS.EMPLOYEE4
                                   ORDER BY TIMESTAMP DESC
                                   FETCH FIRST 1 ROW ONLY))
                   WHERE E2.INDIV_KEY = A.INDIV_KEY) AND
                  (INSERT INTO
                   DINESHS.EMPLOYEE2 E2
                   VALUES (A.INDIV_KEY,A.EMP_NAME,A.DEPT_NAME
                          ,A.REFRESH_DATE,NULL)) AND
                  (INSERT INTO
                   DINESHS.EMPLOYEE4 E4
                   VALUES (A.INDIV_KEY,A.EMP_NAME,A.DEPT_NAME
                          ,A.REFRESH_DATE,CURRENT TIMESTAMP))
FROM DINESHS.EMPLOYEE1 A;

Here is the error message:
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "1". SOME SYMBOLS THAT MIGHT BE
LEGAL ARE: < > = <> <= !< !> != >= ^< ^> ^= IN NOT
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR

For the ones who are interested to know what I am trying to achieve:

I have two tables, Table-A has all current data and Table-B has Current+Historical data - out of the latter table, there are rows that are duplicate but with different load_dates.

Both these tables are loaded every second Friday
Table A - Table with Current data (Col1, Col2, Col3 ... VALID_FROM, VALID_TO) - VALID_FROM & VALID_TO are new fields that are just added to the table.
Table B - Table with History + Current data (Col1, Col2, Col3 ... LOAD_DATE - Date when data is loaded)
(This is a temporary table created to store history data. Once, history data is inserted "accordingly" into Table A, Table B will be dropped)

Challenge: Table A should end up having current data PLUS History data only if there's any change in any column.

Also, VALID_FROM date in Table A gets the value of LOAD_DATE (from Table-B) and VALID_TO remains NULL. However, if there is any change that took place to that row - remember, any change in any column - new row will be added to Table A and VALID_TO gets the LOAD_DATE (from Table-B)- 1 associated to the new row LOAD_DATE should be inserted to VALID_FROM of the new row.

For example:
Now - Current

Table A:
Col1 Col2 Col3 Col4 VALID_FROM VALID_TO
1 a A ASDF
2 b B LKJH
3 c C MNBV

Table B:
Col1 Col2 Col3 Col4 LOAD_DATE
1 a A ASDF 01/01/2009
1 a A ASDD 01/15/2009
2 b B LKJH 01/01/2009
2 b B LKJH 01/15/2009
3 c C MNBV 01/01/2009
3 c C CERT 01/15/2009

Later - Future
Table A:
Col1 Col2 Col3 Col4 VALID_FROM VALID_TO
1 a A ASDF 01/01/0001 01/14/2009
1 a A ASDD 01/15/2009 NULL
2 b B LKJH 01/01/0001 NULL
3 c C MNBV 01/01/0001 01/14/2009
3 c C CERT 01/15/2009 NULL

Table B:
To be dropped...

Constraints: NO COBOL, NO PROCEDURES

What am I equipped with: DB2 SQL & SYNC SORT

Please let me know if you need any more information.

-Dinesh
Back to top
View user's profile Send private message

dinesh_deadman
Warnings : 1

New User


Joined: 10 Aug 2007
Posts: 38
Location: Chicago

PostPosted: Mon Jan 04, 2010 8:54 pm    Post subject: Reply to: Is this query legal?
Reply with quote

Forgot to mention:

The OP has END CASE missing, but I later added that and tried but the result was no different.

Also, OP is a little too long. So, I better be short. All I want to know is, "is it allowed to have a SELECT in CASE which is again in SELECT?"


-Dinesh
Back to top
View user's profile Send private message
dinesh_deadman
Warnings : 1

New User


Joined: 10 Aug 2007
Posts: 38
Location: Chicago

PostPosted: Mon Jan 04, 2010 9:34 pm    Post subject: Reply to: Is this query legal?
Reply with quote

Never Mind!

I just figured that I can't code the way I did.

Thanks for those who took a look.



-Dinesh
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Tue Jan 05, 2010 6:16 am    Post subject:
Reply with quote

Please share your solution for the benefit of others, and use BBCode (see FAQ) for readability.
Back to top
View user's profile Send private message
dinesh_deadman
Warnings : 1

New User


Joined: 10 Aug 2007
Posts: 38
Location: Chicago

PostPosted: Wed Jan 06, 2010 12:41 am    Post subject: Is this query legal?
Reply with quote

I never knew that CASE expression had restrictions for it's WHEN conditions.


-Dinesh
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 Job failing with USER = 4093 REASON C... Pradeepa S ABENDS & Debugging 1 Wed May 17, 2017 3:35 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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts IMS region inactive - User abend code... gthmrj IMS DB/DC 5 Tue Mar 07, 2017 12:29 pm


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