union existing values from 1st query

58 Views
Hi,
We have made the below query but the input field gives existing values only from 1st query of the union and not the second query of the union.How do we do it that existing values shows from both queries.
/* SELECT FROM [dbo].[PDN1] T1 */
declare @Itmcode1 as NVARCHAR(20)
Set @Itmcode1  = /* T1.Itemcode */ 'test1'

SELECT DISTINCT @Itmcode1"BP Name",T1.ItemCode"Part No.", T1.Dscription"Part Description",T6.FirmName "Manufacturer",T0.SuppSerial"DateCode",T0.Quantity"Qty",ISNULL(T2.Custom, 0)"Customs",T1.Price,
 ISNULL(T2.Cost, 0)"Cost",((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))"CVD", (ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))) 'Total' ,T0.U_Batch_Discount "Discount", T0.U_PRICELIST,T8.U_QTY1"SLAB1",CASE WHEN T5.QryGroup1 = 'N' THEN 
(((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN1)/100)
- (((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN1)/100) * (ISNULL(T0.U_Batch_Discount, 0)))/100)
 ELSE 0 END "SLAB1 Sale Price",T8.U_QTY2"SLAB2",CASE WHEN T5.QryGroup1 = 'N' THEN 
(((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN2)/100)
- (((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN2)/100) * (ISNULL(T0.U_Batch_Discount, 0)))/100)
 ELSE 0 END "SLAB2 Sale Price",T8.U_QTY3"SLAB3",CASE WHEN T5.QryGroup1 = 'N' THEN 
(((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN3)/100)
- (((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN3)/100) * (ISNULL(T0.U_Batch_Discount, 0)))/100)
 ELSE 0 END "SLAB3 Sale Price",
T8.U_QTY4"SLAB4",CASE WHEN T5.QryGroup1 = 'N' THEN 
(((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN4)/100)
- (((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN4)/100) * (ISNULL(T0.U_Batch_Discount, 0)))/100)
 ELSE 0 END "SLAB4 Sale Price",
(SELECT AltItem FROM OALI WHERE Remarks = 1 AND Origitem = T1.ItemCode) [AltItem1],
(SELECT T5.onHand FROM OITM T5 WHERE T5.Itemcode = (SELECT DISTINCT AltItem FROM [DBO].[OALI] T4 WHERE T4. Remarks = 1 AND T4.Origitem = T1.ItemCode)) [Stock],
(SELECT AltItem FROM OALI WHERE Remarks = 2 AND Origitem = T1.ItemCode) [AltItem2],
(SELECT T5.onHand FROM OITM T5 WHERE T5.Itemcode = (SELECT DISTINCT AltItem FROM [DBO].[OALI] T4 WHERE T4. Remarks = 2 AND T4.Origitem = T1.ItemCode)) [Stock],
(SELECT AltItem FROM OALI WHERE Remarks = 3 AND Origitem = T1.ItemCode) [AltItem3],
(SELECT T5.onHand FROM OITM T5 WHERE T5.Itemcode = (SELECT DISTINCT AltItem FROM [DBO].[OALI] T4 WHERE T4. Remarks = 3 AND T4.Origitem = T1.ItemCode)) [Stock],
T5.QryGroup1"Hot Device" 
FROM PDN1 T1 
LEFT JOIN IPF1 T2 ON T2.BaseEntry = T1.DocEntry AND 
T1.ItemCode = T2.ItemCode AND T2.Origline = T1.LineNum 
LEFT JOIN OIBT T0 ON T1.LineNum = T0.BaseLinNum AND 
T0.BaseNum = T1.DocEntry AND T0.ItemCode = T1.ItemCode
LEFT JOIN OALI T4  on T4.OrigItem = T1.ItemCode
LEFT JOIN [dbo].[@PRICE_MASTER] T8 ON T0.U_PRICELIST = T8.NAME,
OITM T5 inner join OMRC T6 on  T5.FirmCode = T6.FirmCode,ITM1 T3
WHERE  T3.Itemcode = T1.Itemcode  and T3.PriceList = 2 AND T5.Itemcode = T1.Itemcode AND T0.Quantity > 0 AND (T1.ITEMCODE like '%'+'[%0]'+'%')
union
SELECT T1.CardName "BP Name", T0.U_PartNo"Part No.",Null "Part Description", T0.U_Make"Manufacturer", T0.U_Datecode"DateCode", T0.U_Qty"Qty",0,0,0,0, T0.U_Price"Total",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null FROM [dbo].[OSCN]  T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode WHERE (T0.U_Nulify is null ) and T0.U_PartNo LIKE  '%'+'[%0]'+'%'
Regards,
Rajeev