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.
