Click here to Skip to main content
15,031,495 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 24-Apr-21 8:39am
v3

1 solution

Because the return value of a scalar query is presented as a column in the result, you cannot return two values from the query. In other words, one column can contain only one value, not two or three.

In order to fetch all the values you have several options but two most typical ones are
- Create three separate scalar queries one for each column. This may lead to performance issues and forces to use repetitive logic because all three statements need to have same WHERE clause
- Use join in the main query. If possible join the tables to the main query and fetch the values just like any other values. Just ensure that you use outer join and check that the results are not multiplied (one-to-many situations)

----- EDIT -----

Example added, taken that this is SQL Server.

Probably this contains a lot of typos since I don't have your database and not able to verify the query but this should get you started.

Another point is that the group by is probably easiest to do once you have done fetching all the data. Because of this I divided the query to two sections. The CTE in the beginning fetches the data and the outer query does the grouping.

SQL
WITH sub AS (
	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,  
		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]
			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')) AS col1,
		(	SELECT T6.[U_NAME] as 'User that created Item'
			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')) aS col2,
		(	SELECT 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')) AS col3,
		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'
)
SELECT sub.sapitemcode, 
	sub.Barcode, 
	sub.description, 
	sub.short_description,
	MAX(sub.price_1),  
	sub.precio_tableta,
	sub.Price_level_1, 
	sub.uom_group_id,
	sub.col1,
	sub.col2,
	sub.col3,
	sub.Impuesto, 
	sub.Grupo, 
	sub.Departamento, 
	sub.Categoria, 
	sub.ItemsPerSalesUnit, 
	sub.SellItem 
FROM sub
GROUP BY sub.Barcode, 
	sub.description, 
	sub.short_description,
	sub.precio_tableta,
	sub.Price_level_1, 
	sub.uom_group_id,
	sub.col1,
	sub.col2,
	sub.col3,
	sub.Impuesto, 
	sub.Grupo, 
	sub.Departamento, 
	sub.Categoria, 
	sub.ItemsPerSalesUnit, 
	sub.SellItem 
   
v2
Comments
Alexis Gaitan 24-Apr-21 14:34pm
   
can you show me some example or part of it? thank you
Wendelius 24-Apr-21 14:37pm
   
Can you post the whole query you currently have, the one that fails?
Alexis Gaitan 24-Apr-21 14:40pm
   
i just post it in What I have tried section on main post. thank you
Wendelius 24-Apr-21 14:42pm
   
I can't seem to find where exactly did you try to put the query (SELECT T0.[ItemCode]..)?
Alexis Gaitan 24-Apr-21 14:43pm
   
after the ELSE statement theres a subquery just after this line

case when t1.NumInSale > 1 THEN '1' END as uom_group_id,

--------- subquery starts
(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
Alexis Gaitan 24-Apr-21 14:43pm
   
this is the part that doesnt works

(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')),
Wendelius 24-Apr-21 15:00pm
   
See the updated solution
Alexis Gaitan 24-Apr-21 15:14pm
   
thank you, one question as im using this at the beginning

DECLARE @NumInSale int

SELECT @NumInSale = NumInSale FROM OITM t1 WHERE t1.ItemCode='CID-11418272385'

IF (@NumInSale > 1)

and im using ELSE too should i add that code after ELSE statement ? and leave the other as is?
Wendelius 24-Apr-21 15:20pm
   
As far as I can see you should leave everything else as-is. I only modified the single query so you still need to declare the variables, have the if-else structure and so forth.
Alexis Gaitan 24-Apr-21 16:09pm
   
works perfect, thank you
Wendelius 25-Apr-21 1:42am
   
You're welcome :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900