SQL logic in teradata sql

Database
Highlighted
Enthusiast

SQL logic in teradata sql

Hi All

 

Have two tables 

1) Release 

2)Partial 

Lst_updt_dt  is  date& timestamp column exist in both tables and doing where clause to return max (lst_updt_dt).

here's now i couldnt figure out rest of the logic.

Release table should be right outer join on Partial table.

is this logic correct...?

SyntaxEditor Code Snippet

select
PF1.WBS_No,PF1.Year_No,PF1.Month_No,PF1.Category_Code,PF1.CapOp_Code,PF1.PF_Amount,RL.Release_dollars,PF1.Status,PF.Lst_Updt_Dt
from 
(Select
PF.WBS_No,PF.Year_No,PF.Month_No,PF.Category_Code,PF.CapOp_Code,PF.PF_Amount,PF.Status
from  ZFINIT_Test.PF_Cost PF 
where  PF.lst_updt_dt in (select max(lst_updt_dt) from  ZFINIT_Test.PF_Cost)) PF1 
right outer join  (select RL1.WBS_No,RL1.Year_No,RL1.Month_No,RL1.Category_Code,RL1.CapOp_Code,RL1.Release_dollars,RL1.Status
from (SelectRL.WBS_No,RL.Year_No,RL.Month_No,RL.Category_Code,RL.CapOp_Code,RL.Release_dollars,'Released' as Status from  ZFINIT_Test.Release_Dollars RL where RL.lst_updt_dt in (select max(lst_updt_dt) from  ZFINIT_Test.Release_Dollars)) RL1
on  PF1.WBS_No = RL1.WBS_No

Accepted Solutions
Teradata Employee

Re: SQL logic in teradata sql

You should not get a cross product. You match both your tables on several conditions.

You may have a simili cross product if you have multiple lines per (WBS_No, Year_No, Month_No, Category_Code, CapOp_Code) on each table (after the qualify).

 

Maybe you'd like an agregate for Release_dollars and PF_Amount ?

 

1 ACCEPTED SOLUTION
5 REPLIES
Teradata Employee

Re: SQL logic in teradata sql

Hi vsgsxr,

 

    select ...
      from TableA
right join TableB on ...

means you always take rows from TableB even if there is no match in TableA.

It's the same thing that :

    select ...
      from TableB
 left join TableA on ...

I prefer the left join syntax because it's more human readable, but for the database engine it's the same.

 

For the max filter, you take the last updated data from both tables.

You may achieve better performance using qualify, you should try :

   from ZFINIT_Test.PF_Cost
qualify rank() over(order by lst_updt_dt desc) = 1

 

 

Enthusiast

Re: SQL logic in teradata sql

Let me re write logic again 

 

thanks

Enthusiast

Re: SQL logic in teradata sql

i'm getting cross product

 

 

SyntaxEditor Code Snippet

Select
PF.WBS_No,PF.Year_No,PF.Month_No,PF.Category_Code,PF.CapOp_Code,PF.PF_Amount,RL.Release_dollars,PF.Status,PF.Lst_Updt_Dt
 from  ZFINIT_Test.IT_Release RL qualify rank() over(order by RL.lst_updt_dt desc) = 1
right outer join ( Select PF.WBS_No,PF.Year_No,PF.Month_No,PF.Category_Code,PF.CapOp_Code,PF.PF_Amount,PF.Lst_Updt_Dt from  ZFINIT_Test.PF_Cost PF  qualify rank() over(order by PF.lst_updt_dt desc) = 1) PF1
ON  RL.WBS_No=PF1.WBS_No
AND  RL.Year_No=PF1.Year_No
and RL.Month_No=PF1.Month_No
and RL.Category_Code=PF1.Category_Code
and RL.CapOp_Code =PF1.CapOp_Code

 

 

Release table has data of 6500 rows Partial table has 2500 rows. 

Teradata Employee

Re: SQL logic in teradata sql

You should not get a cross product. You match both your tables on several conditions.

You may have a simili cross product if you have multiple lines per (WBS_No, Year_No, Month_No, Category_Code, CapOp_Code) on each table (after the qualify).

 

Maybe you'd like an agregate for Release_dollars and PF_Amount ?

 

Enthusiast

Re: SQL logic in teradata sql

Thanks All, issue resolved.