Issues with subquery SQL

Question

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 24-Apr-21 8:11am

Alexis Gaitan

Updated 24-Apr-21 8:39am

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Accepted Answer · 2021-04-24T08:30:00

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