CODEWITHSUNDEEP
sqlteradata
user1768029
user1768029

Reputation: 425

How to write sql query in teradata based on multiple conditions

I have two tables.Table Bill has following fields:

Field_Name    Field_Type
===============================
Bill_Sts_Sk   decimal(18)   PK
epn_id        bigint        child key
epn_seq_id    bigint        child key
ref_id        integer       child key

Table CLM_Crg has following fields:

Field_Name    Field_Type
===============================
Bill_Sts_Sk   decimal(18)   
epn_id        bigint       PK
epn_seq_id    bigint       PK
ref_id        integer      PK

I need to look up Bill_Sts_Sk against parent key(Bill_Sts_Sk) against BILL table.Following is the condition for look up:

Lookup on BILL matching on epn_id,
epn_seq_id and ref_id

if not found, try again only using the first 2 fields.

If not found use default value, -1.

If more than 1 key is found, use the maximum value

How can we achieve it by writing a sql query ? I have written following query for first part:

select Bill_Sts_Sk 
from Bill bl
left join CLM_CRG crg
ON bl.epn_id = crg.epn_id
and bl.epn_seq_id = crg.epn_seq_id
and bl.ref_id = crg.ref_id

Can any any please help me to write sql query in Teradata(14.10.06.05) for the above conditions

Upvotes: 0

Views: 991

Answers (1)

dnoeth
dnoeth

Reputation: 60462

You can join on the first two columns and then look for the best match using a ROW_NUMBER:

SELECT bl.*, COALESCE(crg.Bill_Sts_Sk, -1)
FROM Bill bl
LEFT JOIN CLM_CRG crg
ON bl.epn_id = crg.epn_id
AND bl.epn_seq_id = crg.epn_seq_id
QUALIFY
   ROW_NUMBER() 
   OVER (PARTITION BY bl.epn_id, bl.epn_seq_id 
         ORDER BY CASE WHEN bl.ref_id = crg.ref_id THEN 1 ELSE 2 END -- best: matching ref_ids
                  ,crg.Bill_Sts_Sk DESC) = 1  -- 2nd best: highest Bill_Sts_St

The best PI for this would be on (epn_id, epn_seq_id) for both tables.

Upvotes: 2

Related Questions