transaction details if the same transaction has multiple status with same file name

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Fan

transaction details if the same transaction has multiple status with same file name

Hi Guys,

 

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!

6 REPLIES
Junior Contributor

Re: transaction details if the same transaction has multiple status with same file name

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)

 

Fan

Re: transaction details if the same transaction has multiple status with same file name

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!

 

 

Junior Contributor

Re: transaction details if the same transaction has multiple status with same file name

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.

Fan

Re: transaction details if the same transaction has multiple status with same file name

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'. 

Fan

Re: transaction details if the same transaction has multiple status with same file name

Hi Guys,

 

Can someone help me on this please ASAP.

 

Thank you!

 

 

Junior Contributor

Re: transaction details if the same transaction has multiple status with same file name

My queries should solve the problem you described.

Otherwise you need to add more details what you want & why.