SQL query with variables?

DB2 DBA

SQL query with variables?
Bala & Dale - Thanks for your potential solutions. Apologies, it took long
for my response.


-Josh



On Wed, Dec 30, 2009 at 5:24 PM, Hardy, Dale <[login to unmask email]> wrote:

> 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
>
>
> ------------------------------
>
> [image: Image removed by sender. IDUG - The Worldwide DB2 User Community! ] < 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).
> *****************************************************************
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> 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 >
>

_____________________________________________________________________

* 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