Hi All,
I have a scenario which i have solution but it appears to be not smart one. Its running for long hours with out returning any data.
Below is the requirement.
Source table: Stage_Americas (Join column: Seq_id and Region_id)
Lookup table 1: Stage_Country (Join column: Seq_id and Region_id, return value: Country)
Lookup table 2: Stage_State. (Join column: Seq_id and Region_id, return value: 'State_Name' )
First i need to join the source table and lookup table 1 with joining columns and if the condition matches then it should return value as 'COUNTRY' (Value 'Country' will be hardcore)
If the above condition fails then, match the source table and the lookup table 2 using the joining columns and it should return the value present in the column 'State_name'
I tried the logic using 'Case-Then' statement and it worked for few sample records.
select distinct src.seq_id ,src.Region_id
case when
(select count(*) from Stage_Country lkp1 where lkp1.seq_id=src.seq_id and lkp1.Region_id=src.Region_id)>=1
then 'COUNTRY'
WHEN (SELECT COUNT(*) FROM Stage_State lkp2 WHERE lkp2.seq_id=src.seq_id and lkp2.Region_id=src.Region_id)>=1
THEN (SELECT SOURCE_SYS_NAME FROM Stage_State lkp2 WHERE lkp2.seq_id=src.seq_id and lkp2.Region_id=src.Region_id and rownum=1)
ELSE 'NA' END AS SOURCE_SYSTEM
from Stage_Americas src;
If the join condition matches and if the return value is 1 or >1 then the value is present and we are printing 'Country' and the loop ends.
If the first condition fails then it goes to second case statement and since the join condition has multiple values we are fetching only the first value (rownum=1) and printing it.
The code works but for 1 billion records this operation is costly and running for long hours . Please give me some other alternate efficient way to handle this situation.
Please let me know if there is any query or clarification needed on the requirement.
Awaiting response and advance thanks to experts.
Thanks
Bhaskar.S