DB2 query to fetch last known value.
Select messages from
 # through # FAQ
[/[Print]\]

MVSFORUMS.com -> Database
#1: DB2 query to fetch last known value. Author: Magesh_J,  PostPosted: Mon Feb 17, 2020 2:49 pm
    —
DB2 query to fetch and update the last known value.
Table as follows

Code:

Row id Account    Cured  Code1   Date                         field1    field2
1231   123        0             2020-01-15-22.43.12.986358   xxxx      xxxx
1232   124        4      RC00   2020-01-15-22.43.12.986358   xxxx      xxxx
1234   123        3      RC00   2020-01-16-22.43.12.986358   xxxx      xxxx 
1235   124        0             2020-01-16-22.43.13.986358   xxxx      xxxx
1236   123        4      RC11   2020-01-17-22.43.14.986358   xxxx      xxxx
1237   125        4      RC11   2020-01-17-22.43.14.986358   xxxx      xxxx
1238   126        4      RC11   2020-01-17-22.43.14.986358   xxxx      xxxx
1239   123                      2020-01-18-22.43.12.986358   xxxx      xxxx
1240   124                      2020-01-18-22.43.12.986358   xxxx      xxxx
1241   123                      2020-01-19-22.43.14.986358   
1242   124                      2020-01-19-22.43.14.986358     


the table loaded on 2020-01-19 got messed up, and hence I need to update the table with last known good value of fields cured, code,field1,filed2 of the table.

ROW id is the index, and we know row id for 19th load starts from 1241.

for all the accounts where row-id > 1241, I need to update last known good values, there are two conditions.

if cured=0 then meaning account is cured, so field code1 is not generated and that has to be updated in 19th day, else last known code1 value should be updated on 19th loaded fields.

in the above sample

for account 123 Cured=4 Code1=RC11 and corresponding Field1=xxxx field2=xxxx should be updated in 19th record
for account 124 Cured=0 Code1= and corresponding Field1=xxxx field2=xxxx should be updated in 19th record.

is it possible to do it in SQL itself?

Thanks
Magesh
#2: Re: DB2 query to fetch last known value. Author: kolusu, Location: San Jose PostPosted: Mon Feb 17, 2020 6:24 pm
    —
Magesh_J wrote:


the table loaded on 2020-01-19 got messed up, and hence I need to update the table with last known good value of fields cured, code,field1,filed2 of the table.

is it possible to do it in SQL itself?

Thanks
Magesh


Magesh,

You have 4 different fields to be updated which would require the max date field.

So unload the table for such records using this sql

Code:

SELECT X.*                               
  FROM table X                       
      ,(SELECT ACCOUNT                   
              ,MAX(DATE) AS DATE 
          FROM table                 
         WHERE ROWID  < 1241         
         GROUP BY ACCOUNT) Y             
  WHERE X.ACCOUNT    = Y.ACCOUNT         
    AND X.DATE       = Y.DATE         
    AND X.ROWID < 1241               
   ORDER BY ROWID                   
           ,ACCOUNT


And you can generate the UPDATE SQL statement as shown in this topic

http://www.mvsforums.com/helpboards/viewtopic.php?t=7779
#3:  Author: Magesh_J,  PostPosted: Mon Feb 17, 2020 7:40 pm
    —
Hi Kolusu,

Thanks for the response.

But we need to know last good values for accounts having row_num > 1241.

The below query don't have ROW_NUM >= 1241 condition.

kolusu wrote:
Code:

SELECT X.*                               
  FROM table X                       
      ,(SELECT ACCOUNT                   
              ,MAX(DATE) AS DATE
          FROM table                 
         WHERE ROWID  < 1241         
         GROUP BY ACCOUNT) Y             
  WHERE X.ACCOUNT    = Y.ACCOUNT         
    AND X.DATE       = Y.DATE         
    AND X.ROWID < 1241               
   ORDER BY ROWID                   
           ,ACCOUNT


Thanks
Magesh
#4:  Author: Magesh_J,  PostPosted: Mon Feb 17, 2020 9:20 pm
    —
Hi Kolusu,

Did you mean like below?

Code:

SELECT X.*                               
  FROM table X                       
      ,(SELECT ACCOUNT                   
              ,MAX(DATE) AS DATE
          FROM table                 
         WHERE ROWID  < 1241         
         GROUP BY ACCOUNT) Y,
       (SELECT ACCOUNT FROM TABLE WHERE ROWID > 1241) Z,     
  WHERE Y.ACCOUNT    = Z.ACCOUNT
    AND X.ACCOUNT    = Y.ACCOUNT         
    AND X.DATE       = Y.DATE         
    AND X.ROWID < 1241               
   ORDER BY ROWID                   
           ,ACCOUNT


Thanks
Magesh
#5:  Author: kolusu, Location: San Jose PostPosted: Tue Feb 18, 2020 9:58 am
    —
Magesh_J wrote:
Hi Kolusu,

Thanks for the response.

But we need to know last good values for accounts having row_num > 1241.



Magesh_J,

The query I posted will give you last Known values and you use these values to update the rows with ROWID > 1241 . You would add the ROWID > 1241 in the WHERE clause when you are generating the SQL update statements from the results of the query I have shown.
#6:  Author: Magesh_J,  PostPosted: Tue Feb 18, 2020 8:15 pm
    —
Hi Kolusu,

Thank you very much for your suggestion.

instead of updating, I am thinking to insert and delete the old record.

Please advise.

Code:

INSERT INTO TABLE(ACCOUNT,CURED,CODE1,FIELD1,FIELD2)
SELECT Z.ACCOUNT,X.CURED,X.CODE1,X.FIELD1,X.FIELD2
FROM TABLE X,
(SELECT ACCOUNT,MAX(DATE) AS DATE
FROM TABLE WHERE ROWID  < 1241         
         GROUP BY ACCOUNT) Y,
(SELECT ACCOUNT FROM TABLE WHERE ROWID >= 1241) Z,     
  WHERE Y.ACCOUNT    = Z.ACCOUNT
    AND X.ACCOUNT    = Y.ACCOUNT         
    AND X.DATE       = Y.DATE         
    AND X.ROWID < 1241               
   ORDER BY ROWID                   
           ,ACCOUNT

 


Code:

DELETE FROM TABLE WHERE ROW_NUM >=1241 AND ROW_NUM =<1242


Thanks
Magesh
#7:  Author: kolusu, Location: San Jose PostPosted: Wed Feb 19, 2020 2:58 pm
    —
Magesh_J,

You need to write queries which can be easily understandable and readable. Something like this

Code:

INSERT
  INTO TABLE(ACCOUNT
            ,CURED
            ,CODE1
            ,FIELD1
            ,FIELD2)
SELECT Z.ACCOUNT
      ,X.CURED
      ,X.CODE1
      ,X.FIELD1
      ,X.FIELD2
  FROM TABLE X
      ,(SELECT ACCOUNT
              ,MAX(DATE) AS DATE
          FROM TABLE
         WHERE ROWID  < 1241         
         GROUP BY ACCOUNT) Y
      ,(SELECT ACCOUNT
          FROM TABLE
         WHERE ROWID >= 1241) Z     
 WHERE Y.ACCOUNT      = Z.ACCOUNT
   AND X.ACCOUNT      = Y.ACCOUNT         
   AND X.DATE         = Y.DATE         
   AND X.ROWID        < 1241               
 ORDER BY ROWID                   
         ,ACCOUNT

;
#8:  Author: Magesh_J,  PostPosted: Wed Feb 19, 2020 3:18 pm
    —
Hi Kolusu,
I Agree will correct it from next time Thank You

Regards,
Magesh
MVSFORUMS.com -> Database


output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1

Powered by phpBB © 2001, 2005 phpBB Group