SSRS - Select Distinct - Error in dataset

Kinetic ERP
1

Hey folks,

I need to get a single record back from a table in the SSRS query. Here’s the tail end of the query:

. . .
Date,T3.RptLiteralsLblOf, T3.RptLiteralsLblContinueOnNext, T3.RptLiteralsLblComeFrom, T3.RptLiteralsLblPCID, T3.RptLiteralsLCommodityCode, T3.RptLiteralsLEORINumber,
		  T3.RptLiteralsLblAttributeSet, T7.OrderDate, T5.ShortChar03 AS Outbound, T6.Description AS CarrierName, T8.City AS ShipFromCity  
		  
          FROM ShipHead_" + Parameters!TableGuid.Value + " T1
          LEFT OUTER JOIN ShipDtl_" + Parameters!TableGuid.Value + " T2
          ON T1.Company = T2.Company AND T1.PackNum = T2.PackNum
          LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T3
          ON T1.RptLanguageID = T3.RptLanguageID 
		  
 **LEFT OUTER JOIN  ( SELECT Company, Number01, Number02, Number03 FROM UD30_" + Parameters!TableGuid.Value + " ) T4  **
** ON T2.Company = T4.Company AND T2.OrderNum = T4.Number01 AND T2.OrderLine = T4.Number02 AND T2.OrderRelNum = T4.Number03** 
 
 LEFT OUTER JOIN UD106_" + Parameters!TableGuid.Value + " T5  
 ON T4.Company = T5.Company AND T4.ShortChar02 = T5.Key1 
 LEFT OUTER JOIN Carrier_" + Parameters!TableGuid.Value + " T6  
 ON T5.Company = T6.Company AND T5.CarrierID_c = T6.Carrier 
 LEFT OUTER JOIN OrderHed_" + Parameters!TableGuid.Value + " T7  
 ON T2.Company = T7.Company AND T2.OrderNum = T7.OrderNum
 LEFT OUTER JOIN Company_" + Parameters!TableGuid.Value + " T8  
 ON T1.Company = T8.Company  "

I’m getting an error on the UD30 section. It works okay if I take out the select distinct code and change that line to the following:

 LEFT OUTER JOIN UD30_" + Parameters!TableGuid.Value + " T4  
 ON T2.Company = T4.Company AND T2.OrderNum = T4.Number01 AND T2.OrderLine = T4.Number02 AND T2.OrderRelNum = T4.Number03

I’m flailing. Any observations?

Thanks,

Joe

2

Here was the error.

image
image860×124 44.7 KB

I had a reference to T4.ShortChar02 that I didn’t originally have in the select distinct.

Ah well.

Thanks,

Joe

1 Like