All Forums Database
pritpal 5 posts Joined 03/06
14 Dec 2006
Showing the latest record with indicator 'C' and old with 'H' after the loading the table

Hello to all,I am new to this and need a little help in the below case as they need to show the proper indicators, when the records are loaded in the table as per the query some of the records are still showing the indicator 'C' insted of 'H' when they are updated, and we need all the records to show when user access it, but with the proper indicator: Table ABC is populated wrong. # SSN USERID SYSDATE TIME IND - -------- ------ ------ ---- --- . xxxxxxxxx . 04/05/03 1100 H . xxxxxxxxx 07 06/01/05 1007 H . xxxxxxxxx 01 06/01/05 1422 H . xxxxxxxxx 07 06/16/05 1354 H . xxxxxxxxx 07 06/16/05 1355 C . xxxxxxxxx 07 08/16/05 1215 H ABC xxxxxxxxx 07 10/19/05 0958 H ABC xxxxxxxxx 07 10/19/05 0958 C ABC xxxxxxxxx 07 12/12/05 1344 C XYZ xxxxxxxxx 26 06/02/06 1757 H XYZ xxxxxxxxx 01 11/02/06 1001 C XYZ xxxxxxxxx 01 11/02/06 1002 C ABC xxxxxxxxx 26 06/02/06 1757 H XYZ xxxxxxxxx 01 10/02/06 1001 C XYZ xxxxxxxxx 01 12/02/06 1003 CIn Last column Note that the last two Rows-- both have current history indicator of "C".current indicator in table 'ABC' is not accurate. We should only have one current indicator per craft(one for ABC and one for XYZ under each SS#)not multiple current indicators. Create a view which will show two current history indicator for each ss# as 'C'for latest data (one for ABC and one for XYZ under each SS#) and all other as 'H' for old data .I really appreciate your help and thanks in advanceThanks & RegardsP

Barry-1604 176 posts Joined 07/05
15 Dec 2006

CREATE VIEW ABC_VIEW AS SELECT "#" ,SSN ,USERID ,SYSDATE ,"TIME" ,CASE WHEN SUM(1) OVER (PARTITION BY "#", SSN ORDER BY SYSDATE DESC, "TIME" DESC ROWS UNBOUNDED PRECEDING) = 1 THEN 'C' ELSE 'H' END AS IND FROM ABC;Note that when this view is used, the entire view will be materialized before any constraints will be applied, so hopefully, this is not a large table that will be accessed frequently through this view. If it is, it would be better to do this correction into a new table.

pritpal 5 posts Joined 03/06
16 Dec 2006

Thank you Barry very much

You must sign in to leave a comment.