Gilbert V Gilbert V - 16 days ago 7
SQL Question

Teradata using Top 10 and Distinct

I am getting an error saying that I cannot use Top 10 with distinct I am wondering if there is any way I can get my query to work on that fashion. this is what the error is saying .

[Teradata Database] [6916] TOP N Syntax error: Top N option is not supported with DISTINCT option.

Query Below:
Thank you.

Select Distinct TOP 10 t1.Adjustment_ID, t1.OfficeNum, t1.InvoiceNum, t1.PatientNum,
t1.CurrentStatus, t1.AdjustmentTotal, t1.SubmittedOn, t1.UserSubmitted,
t1.Invoice_Type, t1.Pat_First_Name, t1.Pat_Last_Name,t2.Reason_Code FROM App_UnityAdj_AdjInfo_Tbl t1
Left Join RCM_WORK_PRD.App_UnityAdj_AdjRecord_Tbl t2
On t1.Adjustment_ID = t2.Adjustment_ID
Where t1.UserSubmitted = 'Name' AND (t1.CurrentStatus = 'Pending' OR t1.CurrentStatus = 'Deny')

Answer

What are you trying to do? You have columns in the SELECT that are not in the GROUP BY. You also have TOP without ORDER BY, which is suspicious.

One simple method is to move all the SELECT columns to the GROUP BY:

select TOP 10  t1.Adjustment_ID, t1.OfficeNum, t1.InvoiceNum, t1.PatientNum,
       t1.CurrentStatus, t1.AdjustmentTotal, t1.SubmittedOn, t1.UserSubmitted,
       t1.Invoice_Type, t1.Pat_First_Name, t1.Pat_Last_Name, t2.Reason_Code
from App_UnityAdj_AdjInfo_Tbl t1 Left Join  
     RCM_WORK_PRD.App_UnityAdj_AdjRecord_Tbl t2 
     On t1.Adjustment_ID = t2.Adjustment_ID
where t1.UserSubmitted = 'Name' AND
      t1.CurrentStatus in ('Pending', 'Deny')
group by t1.Adjustment_ID, t1.OfficeNum, t1.InvoiceNum, t1.PatientNum,
         t1.CurrentStatus, t1.AdjustmentTotal, t1.SubmittedOn, t1.UserSubmitted,
         t1.Invoice_Type, t1.Pat_First_Name, t1.Pat_Last_Name,t2.Reason_Code;