SQL query with variables?

DB2 DBA

SQL query with variables?
Hello:

DB2 V7
z/OS

I have a rather interesting yet strange problem. I thought it thru but with
the limited available sources (SQL & SORT), I just couldn't arrive at a
solution. Then decided to ask the 'Gurus' for some help.

I have two tables. 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 (NULLable) 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 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 - 1 associated to the new row
LOAD_DATE should be inserted to VALID_FROM of the new row.

I would be surprised if you understand what I mean from above text. Which is
why you have an example below - hopefully it will reach you in the intended
format.

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...

Note: Latest rows will have NULL in VALID_TO col

Constraints: NO COBOL, NO PROCEDURES
What am I equipped with: DB2 SQL & SYNC SORT

Is this possibel at all with SQL alone?



-Josh

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Balachandran Chandrasekaran1

Re: SQL query with variables?
(in response to DB2 DBA)
Hi Josh,

I am afraid you might not be able to achieve that in SQL while in DB2 v7.
But, DB2 v8 offers recursive SQL using Common table expressions. If you
can not wait til v8, is it possible to use QMF programming interface ? I
am sure you could write logics with QMF procedure while maintaining the
code/logic outside quality controlled environment (viz, changeman,
endevor, SCLM ). Below is the SQL that would do it.

WITH CTE (LEVEL ,C1, C2, C3, C4, VALID_FROM , VALID_TO) AS --- common
table expression
(SELECT 0, ROOT.COL1,ROOT.COL2,ROOT.COL3,ROOT.COL4, ROOT.LOAD_DATE
,'12/31/9999' FROM BALATB ROOT
WHERE ROOT.LOAD_DATE = (SELECT MAX(INR.LOAD_DATE)
FROM BALATB INR
WHERE ROOT.COL1 = INR.COL1
AND ROOT.COL2 = INR.COL2
AND ROOT.COL3 = INR.COL3) --- root record
(or recent row)
UNION ALL
SELECT PARENT.LEVEL+1 , CHILD.COL1, CHILD.COL2, CHILD.COL3, ---
join between CTE and the recursive tbl
CHILD.COL4,
CHILD.LOAD_DATE , PARENT.VALID_FROM
FROM CTE PARENT, BALATB CHILD
WHERE PARENT.C1 = CHILD.COL1
AND PARENT.C2 = CHILD.COL2
AND PARENT.C3 = CHILD.COL3
and CHILD.LOAD_DATE = (SELECT MAX(INR.LOAD_DATE) -- SUBQ to omit
recentmost row(s)
FROM BALATB INR
WHERE INR.LOAD_DATE < PARENT.VALID_FROM
AND PARENT.C1 = INR.COL1
AND PARENT.C2 = INR.COL2
AND PARENT.C3 = INR.COL3)
)
SELECT C1, C2, C3, C4 ,MIN(VALID_FROM) AS "VALID_FROM", -- min and
max functions would help to CASE WHEN -- get
single row if subsequent LOADs
(MAX(VALID_TO) - 1 DAY)= '9999-12-30' THEN NULL -- retained
the same value for col4
ELSE MAX(VALID_TO) - 1 DAY
END AS "VALID_TO"
FROM CTE
GROUP BY C1,C2,C3,C4
ORDER BY C1,C2,C3;

REsult:

C1 C2 C3 C4 VALID_FROM VALID_TO
---------+---------+---------+---------+------
1 a A ASDD 2009-01-15 ----------
1 a A ASDF 2009-01-01 2009-01-14
2 b B LKJH 2009-01-01 ----------
3 c C CERT 2009-01-15 ----------
3 c C MNBV 2009-01-01 2009-01-14

There may be simpler way or this SQL may be wrong, if so, please share
with me.

Regards, Bala.





DB2 DBA <[login to unmask email]>
Sent by: IDUG DB2-L <[login to unmask email]>
30/12/2009 03:08
Please respond to
IDUG DB2-L <[login to unmask email]>


To
[login to unmask email]
cc

Subject
[DB2-L] SQL query with variables?






Hello:

DB2 V7
z/OS

I have a rather interesting yet strange problem. I thought it thru but
with the limited available sources (SQL & SORT), I just couldn't arrive at
a solution. Then decided to ask the 'Gurus' for some help.

I have two tables. 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 (NULLable) 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 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 - 1 associated to the new row
LOAD_DATE should be inserted to VALID_FROM of the new row.

I would be surprised if you understand what I mean from above text. Which
is why you have an example below - hopefully it will reach you in the
intended format.
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...

Note: Latest rows will have NULL in VALID_TO col
Constraints: NO COBOL, NO PROCEDURES
What am I equipped with: DB2 SQL & SYNC SORT

Is this possibel at all with SQL alone?



-Josh



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Dale Hardy

Re: SQL query with variables?
(in response to Balachandran Chandrasekaran1)
If the tables aren't too big, how about defining after insert trigger(s)
on Table B.



1. Sort the input file to Table B by the key columns and
LOAD_DATE.

2. Load TABLE B with SHRLEVEL CHANGE.

The trigger could check table b for a match on col1, col2, col3, col4
and valid_from is null

If cols 1-3 and col4 match found and valid_from is null then update
valid_from = load_date

Else If cols 1-3 and col4 match found and valid from is not null then do
nothing.

Else If cols 1-3 match and col4 does not match then update valid_to_date
= load_date - 1 day and insert new row to table B

Else if no match then insert new row







From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of DB2 DBA
Sent: Tuesday, December 29, 2009 4:39 PM
To: [login to unmask email]
Subject: [DB2-L] SQL query with variables?



Hello:



DB2 V7

z/OS



I have a rather interesting yet strange problem. I thought it thru but
with the limited available sources (SQL & SORT), I just couldn't arrive
at a solution. Then decided to ask the 'Gurus' for some help.



I have two tables. 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 (NULLable) 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 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 - 1 associated to the new row
LOAD_DATE should be inserted to VALID_FROM of the new row.



I would be surprised if you understand what I mean from above text.
Which is why you have an example below - hopefully it will reach you in
the intended format.

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...



Note: Latest rows will have NULL in VALID_TO col

Constraints: NO COBOL, NO PROCEDURES

What am I equipped with: DB2 SQL & SYNC SORT



Is this possibel at all with SQL alone?







-Josh



________________________________

< http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >



*****************************************************************
IF YOU RECEIVED THIS EMAIL IN ERROR, YOU SHOULD NOTIFY THE SENDER
BY REPLY EMAIL AND THEN DELETE IT (INCLUDING ANY ATTACHMENTS).
*****************************************************************

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L