I am new to Teradata, working on SQL assist. We receive daily file from third party, we want to validate if same transaction has multiple status for same file.
Ex: TRX Status File Name
123 A ABC01192018
123 C ABC01192018
This scenario should be failed in validation. Your help would be appreciated. Thank you!
If cou simply want to know which TRX/FileName combination has multiple Status values:
select TRX, FileName from tab group by 1,2 having count(distinct Status) > 1
DISTINCT leads to two aggregate steps, in your case you can also use
select TRX, FileName from tab group by 1,2 having min(Status) <> max(Status) -- at least two different values
If you want to get the matching rows you can apply the same logic usinf Windowed Aggregates (which don't allow DISTINCT):
select * from tab group by 1,2 having min(Status) over (partition by TRX, FileName) <> max(Status) over (partition by TRX, FileName)
Thanks Dnoeth, there can be multiple lines with status ='A' this case is acceptable.
when there are multilple lines with status ='A' and <>'A' then it has to error out. This count would be more than one not restricted to any number.
at least two different values
I am testing this. If I have ten lines min and max of transactions are with status A but middle one can be status <> A, if this is the case can I achieve with this query.
I appreciate your help. Thank you!
COUNT(DISTINCT) counts the distinct number of values, a million 'A's will be counted as one.
And MIN returns the minimum value, it's not based on sorting, there's no middle one.
If MAX is different from MIN there must be at least two different values.
Thanks Dnoeth, I need to make sure that in that list one of the status ='A' when there are multiple rows returned.
it shoud return rows when there multple rows with status ='A' and Status <>'A'.
My queries should solve the problem you described.
Otherwise you need to add more details what you want & why.