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.
Joined: 26 Nov 2002 Posts: 12369 Topics: 75 Location: San Jose
Posted: Mon Feb 17, 2020 6:24 pm Post subject: Re: DB2 query to fetch last known value.
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
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
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
Joined: 26 Nov 2002 Posts: 12369 Topics: 75 Location: San Jose
Posted: Tue Feb 18, 2020 9:58 am Post subject:
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. _________________ Kolusu
www.linkedin.com/in/kolusu
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
Joined: 26 Nov 2002 Posts: 12369 Topics: 75 Location: San Jose
Posted: Wed Feb 19, 2020 2:58 pm Post subject:
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
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
will correct it from next time 