KeirMcCann
asked on
SQL align query problem
I have the following query:
The first part of the query works fine however i need to align the second query into the same results. The only condition in the 2nd query is the sitename. I need it to pull all the results from T2.[CardName] where it contains data and align it with the first query.
The first part of the query works fine however i need to align the second query into the same results. The only condition in the 2nd query is the sitename. I need it to pull all the results from T2.[CardName] where it contains data and align it with the first query.
SELECT T2.U_IIS_INADR, T0.[Dscription], T0.U_iis_ddate, T1.[SlpName], T0.U_IIS_EXT as Work_Requiring_attention FROM DLN1 T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8) WHERE T2.[U_IIS_JTYPE] = '6' and T0.[U_iis_ddate] = '[%0]' and T2.[CardName] = '[%1]'
UNION ALL
SELECT T2.U_IIS_INADR, T0.[Dscription], null, null, T0.U_IIS_EXT as Work_Requiring_attention
FROM DLN1 T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8) WHERE T2.[U_IIS_JTYPE] = '6' and T0.[U_iis_ddate] = '[%0]' and T2.[CardName] = '[%1]'
AND T0.U_IIS_EXT is not Null
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
FROM DLN1 T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
WHERE T2.[U_IIS_JTYPE] = '6' and T0.[U_iis_ddate] = '[%0]' and T2.[CardName] = '[%1]'
UNION ALL
SELECT T2.U_IIS_INADR, T0.[Dscription], cast(null as datetime), cast(null as varchar(max), T0.U_IIS_EXT as Work_Requiring_attention
FROM DLN1 T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8) WHERE T2.[U_IIS_JTYPE] = '6' and T0.[U_iis_ddate] = '[%0]' and T2.[CardName] = '[%1]'
AND T0.U_IIS_EXT is not Null