Featured post

General Ledger Revaluation

General Ledger Revaluation Account balances denominated in foreign currencies are adjusted through the revaluation procedure. Revaluat...

Tuesday, 14 February 2017

FA Update query attribute 1 from mass addition

SELECT a.asset_number, 
b.life_in_months LIFE_IN_BOOKS, 
m.asset_number, 
m.attribute1 LIFE_IN_MASS_ADD
FROM fa_additions a, 
fa_books b, 
fa_mass_additions m
WHERE a.asset_id = b.asset_id
AND a.asset_number = m.asset_number
AND b.book_type_code LIKE 'BOOK NAME' --- pass the book
AND m.attribute1 IS NOT NULL

once you get , you need to take count
SELECT COUNT(*)
FROM fa_additions a, 
fa_books b, 
fa_mass_additions m
WHERE a.asset_id = b.asset_id
AND a.asset_number = m.asset_number
AND b.book_type_code LIKE 'BOOK NAME' --- pass the book
AND m.attribute1 IS NOT NULL

and finally here is your scripts for Update
UPDATE fa_books b
SET life_in_months = (
SELECT m.attribute1
FROM fa_mass_additions m, fa_additions a
WHERE a.asset_id = b.asset_id
AND a.asset_number = m.asset_number
AND m.book_type_code LIKE 'BOOK NAME' --- pass the book
AND m.attribute1 IS NOT NULL)
WHERE b.book_type_code LIKE 'BOOK NAME' --- pass the book

No comments:

Post a Comment

Please review my topic and update your comments

Note: only a member of this blog may post a comment.