Sql-server – issues with subquery sql

querysql-server-2008

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:

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:

what im missing?

thank you

Best Answer

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

Related Solutions

Mysql – Help optimizing MySQL slow query

I would like to get rid of "Using temporary; Using filesort"

One of the problems I see is that you're using different GROUP BY and ORDER BY clauses. From the manual on how MySQL uses temporary tables:

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

As soon as you create a temporary table, it will need to be sorted according to your ORDER BY clause, indicated by 'using filesort'.

This execution plan at leasts uses the indexes to appropriately limit the number of rows found.

I would also look through the docs on ORDER BY optimization.

Sql-server – better way to create dynamic time averaged series over a dynamic frequency

Your join between the tables is embedded in a function making it really hard for the optimizer to do anything smart with it. I guess it has to compare every row in one table against every other row in the other table.

Rewriting your join with a range check should be a lot faster. I also added a primary key to your table variable to remove a sort operation from the query plan and I turned your table variable into a temp table instead. The difference in my tests was that the query plan started to use Parallelism.

declare @start datetime;
declare @end datetime;
set @start = '20120301';
set @end = '20120303';
declare @interval int;
set @interval = 300;

create table #tpart
(
  dt datetime primary key
);

with CTE_TimeTable
as
(
  select @start as [date]
  union all
  select dateadd(second ,@interval, [date])
  from CTE_TimeTable
  where dateadd(second, @interval, [date]) <= @end
)
insert into #tpart
select [date]
from CTE_TimeTable
option (maxrecursion 0);

select t.dt, avg(c.x1), min(c.x1), max(c.x2), avg(c.x2), min(c.x2), max(c.x2)
from clean.data c
  inner join #tpart t 
    on c.Date >= t.dt and
       c.Date < dateadd(second, @interval, t.dt)
group by t.dt;

drop table #tpart;

Note: This query does not return exactly the same intervals as your query does. It will divide the date range into equally sized parts where your query had one half interval at the start and one half interval at the end of the range. It is of course possible to modify the query to do be equivalent to your query if that is desired.

Update

I tested on a table with a total of 1036801 rows and with 34560 in the interval 2012-03-01 to 2012-03-03. In my tests the original query takes 4.1 seconds. The query above takes 0.1 seconds.

Script to generate the test data:

create table clean.data
(
    Date datetime primary key,
    x1 int,
    x2 int
);

go

with C as
(
  select cast('20120201' as datetime) as D
  union all
  select dateadd(second, 5, D)
  from C
  where D < '20120401'
)
insert into clean.data(Date, x1, x2)
select D, checksum(newid()) % 1000, checksum(newid()) % 1000
from C
option (maxrecursion 0);

Best Answer

Related Solutions

Mysql – Help optimizing MySQL slow query

Sql-server – better way to create dynamic time averaged series over a dynamic frequency

Related Question