asked on

Oracle

TL_ID      VALID_FROM        BATCH_ID      CURRENT_FLAG      SOURCE      PUBLISHED_FLAG
10001      01-SEP-15 22.00.13.000000000      100      Y        5        Y
10002      01-SEP-15 22.00.13.000000000      100      Y        5        Y
10003 02-SEP-15 09.00.13.000000000 101 Y 8 Y
......
10001      29-SEP-15 15.36.02.354673000      129      Y        8        N
10002      01-OCT-15 22.00.02.354673000      131      Y        8        N
10003 01-OCT-15 09.00.13.000000000 131 Y 8 N
......
10004 02-OCT-15 11.00.02.354673000 132 Y 8 N

I have a table called lookup and it has fixed records from older database which can be identified by SOURCE = '5'. We used to receive new records from daily batch and those records can be identified by SOURCE = '8'.

TL_ID & VALID_FROM are primary key for this table.

My requirement is,

1. Whenever a new record comes it should check the table for any match using TL_ID (For ex, 10001, 10002,10003 in the above case). If it matches it should simply update the following column in existing record
CURRENT_FLAG = 'N'
and
PUBLISHED_FLAG = 'Y' in the new record.

2. Suppose if no matches found (for ex, TL_ID 10004) then simply update the PUBLISHED_FLAG = 'Y'.

The table might have 1 to 2 million records and chance for growing upto 5m. So I am looking for optimal solution and planning to schedule this function/procedure to run on daily basis.

slightwv (䄆 Netminder)

Will the valid_from date be the date it was inserted into the database?

What I'm trying to find is if there is a way to identify a "new" record without having to go through ALL rows.

Subhashini Elango

ASKER

Yes, VALID_FROM is a systimestamp.

slightwv (䄆 Netminder)

It looks like you want current_flag and published_flag set to 'Y' for the maximum valid_from date and the rest set to 'N'.

Correct?

Subhashini Elango

ASKER

Please see attached spreadsheet incase if the above example is not aligned properly.
table_export1.xls

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

This might run faster depending on how many new rows you get in a day.

This version has the requirement that it MUST run daily since it only updates the previous days rows.

begin
	for i in (select tl_id, valid_from from tab1 where valid_from >= trunc(sysdate)-1 and valid_from < trunc(sysdate) ) loop
		update tab1 set current_flag='N', published_flag='N' where tl_id=i.tl_id;
		update tab1 set current_flag='Y', published_flag='Y' where tl_id=i.tl_id and valid_from=i.valid_from;
	end loop;
	commit;
end;
/

Open in new window

Subhashini Elango

ASKER

Hi Pal

Many thanks for the solutions first. I feel the first solution will be good as the second one only updates the previous day rows. It will be problem if the previous day batch was failed for some reasons.

I accept your first solution but the requirements are slightly changed. Any help would be much appreciated.

1. The current_flag should be set to 'Y' for maximum valid_from date and 'N' for the rest (your case statement do this correctly). However for published_flag, it should be set to 'Y' for all the records.

2. We have an another column called VALID_TO which has the default value of '31-12-9999' always. The VALID_TO field in the second record should be changed to Maximum VALID_FROM value of the first record.

3. If the record is an intra-day, ex 2 or more records come on the same day (batch_id) then keep the most recent one and delete the other records for that TL_ID

4. If it is new record (TL_ID), simply update the PUBLISHED_FLAG = 'Y'

Please see expected results in the attached file. Could you please suggest.
table_export2.xls

slightwv (䄆 Netminder)

>>However for published_flag, it should be set to 'Y' for all the records.

I don't see the problem. Just remove the case statement and hard-code a 'Y'. It does seem strange to have a column that only stores one specific value...

Since the question asked was answered and was not accurate, for the rest of the requirements, I suggest you close this one and ask a new question.

Some of the new requirements don't make sense... #4 for example. current can be 'N' for a "new" record? published is already a 'Y' based on #1 (everything is a 'Y').

Subhashini Elango

ASKER

Hi Slightwv

Thanks for the reply. I accept your solution to the current scenario and will raise a new question for the updated requirements.

Much appreciated for the speedy response.

Thanks.