Hi im getting this error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
this is my current SQL query:
DECLARE @NumInSale int
SELECT @NumInSale = NumInSale FROM OITM t1 WHERE t1.ItemCode='CID-11418386891'
IF (@NumInSale > 1)
select t1.itemcode as sapitemcode, t1.CodeBars as Barcode, t1.ItemName as description, LEFT(t1.ItemName,20) as short_description,
t0.Price*t1.NumInSale as price_1, MAX(case when T0.PriceList = 1 then t0.Price else null end) as precio_tableta,
CASE t0.PriceList when '1' THEN 1 END as Price_level_1,
--CASE t1.SalUnitMsr when 'PZA' THEN 1 WHEN 'UND' THEN 1 when 'CAJA' THEN 1 when '' THEN NULL END as uom_group_id,
case when t1.NumInSale > 1 THEN '1' END as uom_group_id,
CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as Impuesto, t1.U_GRUPOA as Grupo, t1.U_GRUPOB as Departamento, t1.U_GRUPOC as Categoria, t1.NumInSale as ItemsPerSalesUnit from ITM1 T0
inner join oitm t1 on t0.itemcode = t1.itemcode
inner join ouom t2 on t2.uomentry = t0.uomentry where t1.ItemCode='CID-11418386891' and PriceList='1'
GROUP BY t1.itemcode, t1.CodeBars, t1.ItemName, t1.VatGourpSa, t1.SalUnitMsr, t0.PriceList, t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.NumInSale, t0.Price
ELSE
select t1.itemcode as sapitemcode, t1.CodeBars as Barcode, t1.ItemName as description, LEFT(t1.ItemName,20) as short_description,
MAX(case when T0.PriceList = 1 then t0.Price else null end) as price_1, NULL as precio_tableta,
CASE t0.PriceList when '1' THEN 1 END as Price_level_1,
--CASE t1.SalUnitMsr when 'PZA' THEN 1 WHEN 'UND' THEN 1 when 'CAJA' THEN 1 when '' THEN NULL END as uom_group_id,
case when t1.NumInSale > 1 THEN '1' END as uom_group_id,
(SELECT T5.[ItemCode], T6.[U_NAME] as 'User that created Item', t5.updatedate
FROM AITM T5 inner join OUSR T6 on t5.usersign = t6.internal_K
WHERE T5.[ItemCode] = 'CID-11418272385'
group by T5.[ItemCode], T6.[U_NAME], t5.loginstanc, t5.updatedate
having t5.loginstanc = (select min(loginstanc) from AITM where itemcode = 'CID-11418272385')),
CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as Impuesto, t1.U_GRUPOA as Grupo, t1.U_GRUPOB as Departamento, t1.U_GRUPOC as Categoria, t1.NumInSale as ItemsPerSalesUnit, t1.SellItem as SellItem from ITM1 T0
inner join oitm t1 on t0.itemcode = t1.itemcode
inner join ouom t2 on t2.uomentry = t0.uomentry where t1.ItemCode='CID-11418386891' and PriceList='1'
GROUP BY t1.itemcode, t1.CodeBars, t1.ItemName, t1.VatGourpSa, t1.SalUnitMsr, t0.PriceList, t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.NumInSale, t0.Price, t1.SellItem
i just added this subquery:
(SELECT T0.[ItemCode], T2.[U_NAME] as 'User that created Item', t0.updatedate
FROM AITM T0 inner join OUSR T2 on t0.usersign = t2.internal_K
WHERE T0.[ItemCode] = t1.ItemCode
group by T0.[ItemCode], T2.[U_NAME], t0.loginstanc, t0.updatedate
having t0.loginstanc = (select min(loginstanc) from AITM where itemcode = t1.ItemCode))
when i run this alone i get something like this:
Msg 116, Level 16, State 1, Line 36
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
what im missing? please help me
thank you
What I have tried:
full query that fails:
DECLARE @NumInSale int
SELECT @NumInSale = NumInSale FROM OITM t1 WHERE t1.ItemCode='CID-11418386891'
IF (@NumInSale > 1)
select t1.itemcode as sapitemcode, t1.CodeBars as Barcode, t1.ItemName as description, LEFT(t1.ItemName,20) as short_description,
t0.Price*t1.NumInSale as price_1, MAX(case when T0.PriceList = 1 then t0.Price else null end) as precio_tableta,
CASE t0.PriceList when '1' THEN 1 END as Price_level_1,
--CASE t1.SalUnitMsr when 'PZA' THEN 1 WHEN 'UND' THEN 1 when 'CAJA' THEN 1 when '' THEN NULL END as uom_group_id,
case when t1.NumInSale > 1 THEN '1' END as uom_group_id,
CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as Impuesto, t1.U_GRUPOA as Grupo, t1.U_GRUPOB as Departamento, t1.U_GRUPOC as Categoria, t1.NumInSale as ItemsPerSalesUnit from ITM1 T0
inner join oitm t1 on t0.itemcode = t1.itemcode
inner join ouom t2 on t2.uomentry = t0.uomentry where t1.ItemCode='CID-11418386891' and PriceList='1'
GROUP BY t1.itemcode, t1.CodeBars, t1.ItemName, t1.VatGourpSa, t1.SalUnitMsr, t0.PriceList, t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.NumInSale, t0.Price
ELSE
select t1.itemcode as sapitemcode, t1.CodeBars as Barcode, t1.ItemName as description, LEFT(t1.ItemName,20) as short_description,
MAX(case when T0.PriceList = 1 then t0.Price else null end) as price_1, NULL as precio_tableta,
CASE t0.PriceList when '1' THEN 1 END as Price_level_1,
--CASE t1.SalUnitMsr when 'PZA' THEN 1 WHEN 'UND' THEN 1 when 'CAJA' THEN 1 when '' THEN NULL END as uom_group_id,
case when t1.NumInSale > 1 THEN '1' END as uom_group_id,
(SELECT T5.[ItemCode], T6.[U_NAME] as 'User that created Item', t5.updatedate
FROM AITM T5 inner join OUSR T6 on t5.usersign = t6.internal_K
WHERE T5.[ItemCode] = 'CID-11418272385'
group by T5.[ItemCode], T6.[U_NAME], t5.loginstanc, t5.updatedate
having t5.loginstanc = (select min(loginstanc) from AITM where itemcode = 'CID-11418272385')),
CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as Impuesto, t1.U_GRUPOA as Grupo, t1.U_GRUPOB as Departamento, t1.U_GRUPOC as Categoria, t1.NumInSale as ItemsPerSalesUnit, t1.SellItem as SellItem from ITM1 T0
inner join oitm t1 on t0.itemcode = t1.itemcode
inner join ouom t2 on t2.uomentry = t0.uomentry where t1.ItemCode='CID-11418386891' and PriceList='1'
GROUP BY t1.itemcode, t1.CodeBars, t1.ItemName, t1.VatGourpSa, t1.SalUnitMsr, t0.PriceList, t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.NumInSale, t0.Price, t1.SellItem