UNION BAQ - Sub Level Data

aarong · September 21, 2021, 1:02pm

Hello everyone,

Not sure if anyone can help with this one? I have a BAQ which does a UNION select on a different table. I have data on the SUB LEVEL select that I would like displayed on the top level but it’s not displaying it? Any reason why?

Here my query

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
select 
	[Customer].[CustID] as [Customer_CustID],
	[OrderRel].[OrderNum] as [OrderRel_OrderNum],
	[OrderRel].[PartNum] as [OrderRel_PartNum],
	[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
	[OrderRel].[LoadNum_c] as [OrderRel_LoadNum_c],
	[OrderRel].[DropSeq_c] as [OrderRel_DropSeq_c],
	[OrderHed].[PONum] as [OrderHed_PONum],
	[JobProd].[JobNum] as [JobProd_JobNum],
	[SerialNo].[SerialNumber] as [SerialNo_SerialNumber],
	[OrderDtl].[ShipComment] as [OrderDtl_ShipComment],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[OrderRel].[SellingReqQty] as [OrderRel_SellingReqQty],
	[OrderRel].[ShipToNum] as [OrderRel_ShipToNum],
	[Customer].[Name] as [Customer_Name],
	[Customer].[Address1] as [Customer_Address1],
	[Customer].[Address2] as [Customer_Address2],
	[Customer].[Address3] as [Customer_Address3],
	[Customer].[City] as [Customer_City],
	[Customer].[State] as [Customer_State],
	[Customer].[Country] as [Customer_Country],
	[Customer].[PhoneNum] as [Customer_PhoneNum],
	[UD100].[LoadName_c] as [UD100_LoadName_c],
	[UD100].[LoadingMeters_c] as [UD100_LoadingMeters_c],
	[UD100].[TrailerType_c] as [UD100_TrailerType_c],
	[UD100].[Carrier_c] as [UD100_Carrier_c],
	[UD100].[Key1] as [UD100_Key1],
	((CASE 
 WHEN OrderRel.OrderNum LIKE '2%' THEN 'MISC' 
 ELSE CONCAT(Customer.Name,'  ',
            Customer.Address1,'  ',
            Customer.City,'  ',
            Customer.State,'  ',
            Customer.Zip,'  ',
            Customer.Country) END) ) as [Calculated_Address],
	(COALESCE(CONVERT(nvarchar, UD100.DateCustContact_c, 105), CONVERT(nvarchar, UD100.CollectionDate_c, 105), CONVERT(nvarchar, UD100.AvailableDate_c, 105), (CASE 
 --Jan
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),1,1) then 'Early January' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),1,14) then 'Mid January'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),1,27) then 'Late January' 
 --Feb  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),2,1) then 'Early Feburary' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),2,14) then 'Mid Feburary'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),2,27) then 'Late Feburary' 
   
 --Mar  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),3,1) then 'Early March' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),3,14) then 'Mid March'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),3,27) then 'Late March' 
 --Apr  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),4,1) then 'Early April' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),4,14) then 'Mid April'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),4,27) then 'Late April' 
 --May  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),5,1) then 'Early May' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),5,14) then 'Mid May'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),5,27) then 'Late May' 
 --Jun  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),6,1) then 'Early June' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),6,14) then 'Mid June'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),6,27) then 'Late June' 
 --Jul  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),7,1) then 'Early July' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),7,14) then 'Mid July'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),7,27) then 'Late July' 
 --Aug  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),8,1) then 'Early August' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),8,14) then 'Mid August'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),8,27) then 'Late August' 
 --Sept  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),9,1) then 'Early September' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),9,14) then 'Mid September'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),9,27) then 'Late September' 
 --Oct  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),10,1) then 'Early October' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),10,14) then 'Mid October'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),10,27) then 'Late October'   
 --Nov  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),11,1) then 'Early November' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),11,14) then 'Mid November'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),11,27) then 'Late November' 
 --Dec  
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),12,1) then 'Early December' 
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),12,14) then 'Mid December'   
   WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),12,27) then 'Late December'   
   else 
   'No date' 
 end))) as [Calculated_PlannedDate],
	[UD100].[LoadDesc_c] as [UD100_LoadDesc_c],
	[ShipTo].[Name] as [ShipTo_Name],
	[ShipTo].[Address1] as [ShipTo_Address1],
	[ShipTo].[Address2] as [ShipTo_Address2],
	[ShipTo].[Address3] as [ShipTo_Address3],
	[ShipTo].[City] as [ShipTo_City],
	[ShipTo].[State] as [ShipTo_State],
	[ShipTo].[ZIP] as [ShipTo_ZIP],
	[ShipTo].[Country] as [ShipTo_Country],
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
	[OrderHed].[OrderNum] as [OrderHed_OrderNum]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on 
	OrderHed.Company = OrderDtl.Company
	and OrderHed.OrderNum = OrderDtl.OrderNum
	and ( not OrderDtl.PartNum like 'CONTRACT%'  )

inner join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
	and ( OrderRel.OpenRelease = 1  or OrderRel.OpenRelease = 0  )

left outer join Erp.JobProd as JobProd on 
	OrderRel.Company = JobProd.Company
	and OrderRel.OrderNum = JobProd.OrderNum
	and OrderRel.OrderLine = JobProd.OrderLine
	and OrderRel.OrderRelNum = JobProd.OrderRelNum
left outer join Erp.JobHead as JobHead on 
	JobProd.Company = JobHead.Company
	and JobProd.JobNum = JobHead.JobNum
left outer join Erp.SerialNo as SerialNo on 
	JobHead.Company = SerialNo.Company
	and JobHead.JobNum = SerialNo.JobNum
inner join Ice.UD100 as UD100 on 
	OrderRel.Company = UD100.Company
	and OrderRel.LoadNum_c = UD100.Key1
	and ( UD100.Key1 = @LoadNum  )

inner join Erp.ShipTo as ShipTo on 
	OrderHed.Company = ShipTo.Company
	and OrderHed.CustNum = ShipTo.CustNum
	and OrderHed.ShipToNum = ShipTo.ShipToNum
inner join Erp.ShipTo as ShipTo
	and 
	OrderRel.Company = ShipTo.Company
	and OrderRel.ShipToNum = ShipTo.ShipToNum
	and OrderRel.ShipToCustNum = ShipTo.CustNum
inner join Erp.Customer as Customer on 
	OrderHed.Company = Customer.Company
	and OrderHed.CustNum = Customer.CustNum
union
select 
	[Customer_Msc].[CustID] as [Customer_Msc_CustID],
	[MscShpDt].[PackNum] as [MscShpDt_PackNum],
	[MscShpDt].[PartNum] as [MscShpDt_PartNum],
	[MscShpDt].[LineDesc] as [MscShpDt_LineDesc],
	[MscShpHd].[LoadNum_c] as [MscShpHd_LoadNum_c],
	[MscShpHd].[DropSeq_c] as [MscShpHd_DropSeq_c],
	(convert(nvarchar(15), MscShpHd.PONum)) as [Calculated_PONum],
	[JobHead_Msc].[JobNum] as [JobHead_Msc_JobNum],
	[SerialNo_Msc].[SerialNumber] as [SerialNo_Msc_SerialNumber],
	[MscShpHd].[ShipComment] as [MscShpHd_ShipComment],
	[MscShpDt].[PackLine] as [MscShpDt_PackLine],
	(0) as [Calculated_MiscLine],
	[MscShpDt].[Quantity] as [MscShpDt_Quantity],
	[MscShpDt].[ShipToNum] as [MscShpDt_ShipToNum],
	[MscShpHd].[Name] as [MscShpHd_Name],
	[MscShpHd].[Address1] as [MscShpHd_Address1],
	[MscShpHd].[Address2] as [MscShpHd_Address2],
	[MscShpHd].[Address3] as [MscShpHd_Address3],
	[MscShpHd].[City] as [MscShpHd_City],
	[MscShpHd].[State] as [MscShpHd_State],
	[MscShpHd].[Country] as [MscShpHd_Country],
	[Customer_Msc].[PhoneNum] as [Customer_Msc_PhoneNum],
	[UD100_Msc].[LoadName_c] as [UD100_Msc_LoadName_c],
	[UD100_Msc].[LoadingMeters_c] as [UD100_Msc_LoadingMeters_c],
	[UD100_Msc].[TrailerType_c] as [UD100_Msc_TrailerType_c],
	[UD100_Msc].[Carrier_c] as [UD100_Msc_Carrier_c],
	[UD100_Msc].[Key1] as [UD100_Msc_Key1],
	('') as [Calculated_Dummyfield],
	('') as [Calculated_DummyField2],
	[UD100_Msc].[LoadDesc_c] as [UD100_Msc_LoadDesc_c],
	('') as [Calculated_Dummy3],
	('') as [Calculated_Dummy4],
	('') as [Calculated_Dummy5],
	('') as [Calculated_Dummy6],
	('') as [Calculated_Dummy7],
	('') as [Calculated_Dummy8],
	('') as [Calculated_Dummy9],
	('') as [Calculated_Dummy10],
	(0) as [Calculated_Dummy11],
	(0) as [Calculated_Dummy12],
	(0) as [Calculated_Dummy13]
from Erp.MscShpHd as MscShpHd
inner join Erp.MscShpDt as MscShpDt on 
	MscShpHd.Company = MscShpDt.Company
	and MscShpHd.PackNum = MscShpDt.PackNum
left outer join Erp.JobHead as JobHead_Msc on 
	MscShpDt.Company = JobHead_Msc.Company
	and MscShpDt.JobNum = JobHead_Msc.JobNum
left outer join Erp.SerialNo as SerialNo_Msc on 
	JobHead_Msc.Company = SerialNo_Msc.Company
	and JobHead_Msc.JobNum = SerialNo_Msc.JobNum
inner join Erp.Customer as Customer_Msc on 
	Customer_Msc.Company = MscShpHd.Company
	and Customer_Msc.CustNum = MscShpHd.CustNum
inner join Ice.UD100 as UD100_Msc on 
	UD100_Msc.Key1 = MscShpHd.LoadNum_c
	and UD100_Msc.Company = MscShpHd.Company
	and ( UD100_Msc.Key1 = @LoadNum  )

where (MscShpHd.ShipDate >= dateadd (year, -2, Constants.Today))
order by OrderRel.OrderNum Desc

I need the following data displayed within my Calculated_Address field that includes both TopLevel Address and

[MscShpHd].[Address1] as [MscShpHd_Address1],
[MscShpHd].[Address2] as [MscShpHd_Address2],
[MscShpHd].[Address3] as [MscShpHd_Address3],
[MscShpHd].[City] as [MscShpHd_City],
[MscShpHd].[State] as [MscShpHd_State],
[MscShpHd].[Country] as [MscShpHd_Country],

Thanks
Aaron.

ckrusen · September 21, 2021, 1:27pm

“Not displaying” - like the top level runs, but the UNION’d parts aren’t appended?

Or doesn’t run at all?

I’m no SQL expert, but I’m fairly sure that the fields in the UNION’d table must have the same column names as the data it’s being UNION’d to. If it is just the same number and type of columns, that is all that is required, then maybe that WHERE clause at the end is is confused.

Does the WHERE clause apply to the 2nd table, or to the combined tables?

Where did you put the criteria MscShpHd.ShipDate >= dateadd (year, -2, Constants.Today) in your design?

Is it on the 2nd subquery as a table criteria? As a subquery criteria on the 2nd subquery? or as a Subquery criteria on the top query?

Camren360 · September 21, 2021, 3:40pm

Do you have any subquery criteria on the top level?

Also is it somehow mistaking the lines as duplicates? What happens if you do a Union All?

aarong · September 21, 2021, 9:43pm

The columns within a UNION have to be the same data type e.g INT (TOPLEVEL) and INT(SUBLEVEL) with the same position.

The criteria is on the sublevel query within

“Not Displaying” that’s a poor explanation. It runs the query fine and I get data from both queries (top and sub) but for some reason there is no data within the address field just for the sub query but there is data within the address field of the top level.

Maybe I am missing something?

Banderson · September 21, 2021, 10:09pm

Did you check the table for the records that they are missing on? Are you sure there are addresses in there?

aarong · September 22, 2021, 1:37pm

There is data within the table.

SELECT Address1, Address2, Address3, City, State, Country, ZIP
FROM Erp.MscShpHd
WHERE PackNum = N'21474'

aarong · September 22, 2021, 2:22pm

I got around by creating a dummy field on the top query and it displayed the data from the bottom level then in SSRS I created a expression with the following…

=IIF(Fields!OrderRel_OrderNum.Value LIKE "2*", Fields!Calculated_Dummy.Value, Fields!Calculated_Address.Value)

As you can see it uses my Dummy field to display the text out of the BAQ.

This works perfectly but I see a slight pitful if we have an order that starts with 2 it will display the wrong address so I’m going to create a case when on the bottom level that will make sure it knows it’s a MISC order.

After bit of hard thinking and playing… fully there!