Início / dba / Perguntas / 291190
Accepted
alexistkd
Asked: 2021-04-25 06:00:43 +0800 CST

problemas com subconsulta sql

  • 772

Oi estou recebendo este erro:

Apenas uma expressão pode ser especificada na lista de seleção quando a subconsulta não é introduzida com EXISTS.

esta é minha consulta SQL atual:

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'

acabei de adicionar esta subconsulta:

(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))

quando eu executo isso sozinho, recebo algo assim:

insira a descrição da imagem aqui

o que estou tentando fazer é adicionar esse select dentro da minha consulta principal para que apareça como novas colunas dentro da consulta original. Portanto, deve aparecer como novas colunas (T2.[U_NAME] e t0.updatedate) no final assim:

insira a descrição da imagem aqui

o que estou faltando?

obrigada

sql-server-2008 query

1 respostas

  1. Best Answer

    usado:

    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
    • 0

relate perguntas