issues with subquery sql

All we need is an easy explanation of the problem, so here it is.

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 @UgpEntry VARCHAR(50)

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

IF (@UgpEntry = -1)

SELECT
t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     , 
           (select max(p.Price)
        from ITM1 p 
        where p.ItemCode = t1.ItemCode 
          and p.PriceList = 1) as [price_1]
     ,
         (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 = t0.loginstanc)),
     CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='CID-11418272385'
 
ELSE

SELECT t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     , (select max(p.Price)
        from ITM9 p 
        where p.ItemCode = t1.ItemCode 
          and p.UomEntry = 1 and p.PriceList = 1) as [price_1mananitas],

           (select max(p.Price)
        from ITM1 p 
        where p.ItemCode = t1.ItemCode 
          and p.PriceList = 1) as [preciocajamananitas],
(select max(p.Price)
        from ITM9 p 
        where p.ItemCode = t1.ItemCode 
          and p.UomEntry = 1 and p.PriceList = 2) as [price_2cabra],
               (select max(p.Price)
        from ITM1 p 
        where p.ItemCode = t1.ItemCode 
          and p.PriceList = 2) as [preciocajacabra]

     , CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='CID-11418272385'

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:

issues with subquery sql

what im trying to do, is to add that select inside my main query so will appear as new columns inside the original query. So it should appear like new columns (T2.[U_NAME] and t0.updatedate) at the end like this:

issues with subquery sql

what im missing?

thank you

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

used:

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply