Oracle to SQL Server syntax change

I’m having some problems with the following query:

select * from
(
  select inventory.location, inventory.itemnum as itemnum, item.description as itemdesc,
  inventory.minlevel as olevel , invcost.avgcost from inventory join invcost on inventory.itemnum = invcost.itemnum join item on inventory.itemnum = item.itemnum
  where inventory.location = 'KHSTORE' AND inventory.itemnum BETWEEN '1221' and '1221' and invcost.location = 'KHSTORE'
) table1
  join
  (select SUM(balvalues) as balance, itemnum from
    (select ib.CURBAL as balvalues, inventory.itemnum as itemnum from invbalances ib join inventory on ib.itemnum = inventory.itemnum and ib.location = inventory.location where inventory.itemnum BETWEEN '1221' and '1221'
    UNION ALL
    SELECT  SUM(-1*QUANTITY) , itemnum from matrectrans where itemnum BETWEEN '1221' and '1221' and TRANSDATE >= '05-MAY-2015' and tostoreloc = 'KHSTORE' group by itemnum
    UNION ALL
    SELECT  SUM(-1*QUANTITY) , itemnum from matusetrans where itemnum BETWEEN '1221' and '1221' and TRANSDATE >= '05-MAY-2015' and storeloc = 'KHSTORE' group by itemnum
    )group by itemnum
  ) table2 on table1.itemnum = table2.itemnum;

The query runs fine on an Oracle database but when I try to run the same on an SQL Server instance I get the following error:

  • SQL Server 2008 R2: Prepare dynamic WILDCARD
  • Search a varchar field that contains all words from another string
  • Join two tables in sql-server 2008
  • Trigger on UPDATE of Specific Field
  • Using bcp.exe to successfully import data, even if the column order in the format file differs to that of the table
  • mssql+php prepared statements incorrect syntax using %
  • Msg 156, Level 15, State 1, Line 14
    Incorrect syntax near the keyword 'group'.
    

    I’m looking for a way to change this query and make it compatible with both Orace and SQLS, or if not then at least make it run on SQL Server

  • How can I generically detect if a database is 'empty' from Java
  • SQL: Is it possible to SUM() fields of INTERVAL type?
  • Oracle drop constraint cascade equivalent in Sql Server
  • how to select n rows
  • Sharing activerecord classes between oracle and ms sql server
  • Optimising a SELECT query that runs slow on Oracle which runs quickly on SQL Server
  • One Solution collect form web for “Oracle to SQL Server syntax change”

    Try to add alias to subquery:

    select * from
    (
      select inventory.location, inventory.itemnum as itemnum, item.description as itemdesc,
      inventory.minlevel as olevel , invcost.avgcost from inventory join invcost on inventory.itemnum = invcost.itemnum join item on inventory.itemnum = item.itemnum
      where inventory.location = 'KHSTORE' AND inventory.itemnum BETWEEN '1221' and '1221' and invcost.location = 'KHSTORE'
    ) table1
      join
      (select SUM(balvalues) as balance, itemnum from
        (select ib.CURBAL as balvalues, inventory.itemnum as itemnum from invbalances ib join inventory on ib.itemnum = inventory.itemnum and ib.location = inventory.location where inventory.itemnum BETWEEN '1221' and '1221'
        UNION ALL
        SELECT  SUM(-1*QUANTITY) , itemnum from matrectrans where itemnum BETWEEN '1221' and '1221' and TRANSDATE >= '05-MAY-2015' and tostoreloc = 'KHSTORE' group by itemnum
        UNION ALL
        SELECT  SUM(-1*QUANTITY) , itemnum from matusetrans where itemnum BETWEEN '1221' and '1221' and TRANSDATE >= '05-MAY-2015' and storeloc = 'KHSTORE' group by itemnum
        ) AS t  /* HERE */
      group by itemnum
      ) table2 on table1.itemnum = table2.itemnum;
    

    Using CTE for more readability:

    WITH table1 AS 
    (
      SELECT  inventory.location, inventory.itemnum as itemnum, item.description as itemdesc,
      inventory.minlevel as olevel , invcost.avgcost
      FROM inventory
      JOIN invcost
        ON inventory.itemnum = invcost.itemnum
      JOIN item
        ON inventory.itemnum = item.itemnum
      WHERE inventory.location = 'KHSTORE'
          AND inventory.itemnum BETWEEN '1221' and '1221'
          AND invcost.location = 'KHSTORE'
    ),
     table2 AS
    (
     SELECT SUM(balvalues) as balance,itemnum
     FROM
        (SELECT ib.CURBAL as balvalues, inventory.itemnum as itemnum
         FROM invbalances ib
         JOIN inventory 
           ON ib.itemnum = inventory.itemnum
           AND ib.location = inventory.location
         WHERE inventory.itemnum BETWEEN '1221' AND '1221'
        UNION ALL
        SELECT  SUM(-1*QUANTITY) , itemnum
        FROM matrectrans
        WHERE itemnum BETWEEN '1221' AND '1221'
           AND TRANSDATE >= '05-MAY-2015'
           AND tostoreloc = 'KHSTORE'
        GROUP BY itemnum
        UNION ALL
        SELECT  SUM(-1*QUANTITY) , itemnum
        FROM matusetrans
        WHERE itemnum BETWEEN '1221' AND '1221'
           AND TRANSDATE >= '05-MAY-2015'
           AND storeloc = 'KHSTORE'
        GROUP BY itemnum
        ) AS t   /* HERE */
      GROUP BY itemnum
    )
    SELECT *
    FROM table1
    JOIN table2
     ON table2 on table1.itemnum = table2.itemnum;
    

    EDIT

    Using CTE no subqueries

    WITH table1 AS 
        (
          SELECT  inventory.location, inventory.itemnum as itemnum, item.description as itemdesc,
          inventory.minlevel as olevel , invcost.avgcost
          FROM inventory
          JOIN invcost
            ON inventory.itemnum = invcost.itemnum
          JOIN item
            ON inventory.itemnum = item.itemnum
          WHERE inventory.location = 'KHSTORE'
              AND inventory.itemnum BETWEEN '1221' and '1221'
              AND invcost.location = 'KHSTORE'
        )
        ,table2_helper AS
        (
             SELECT ib.CURBAL as balvalues, inventory.itemnum as itemnum
             FROM invbalances ib
             JOIN inventory 
               ON ib.itemnum = inventory.itemnum
               AND ib.location = inventory.location
             WHERE inventory.itemnum BETWEEN '1221' AND '1221'
             UNION ALL
             SELECT  SUM(-1*QUANTITY) , itemnum
             FROM matrectrans
             WHERE itemnum BETWEEN '1221' AND '1221'
               AND TRANSDATE >= '05-MAY-2015'
               AND tostoreloc = 'KHSTORE'
             GROUP BY itemnum
             UNION ALL
             SELECT  SUM(-1*QUANTITY) , itemnum
             FROM matusetrans
             WHERE itemnum BETWEEN '1221' AND '1221'
               AND TRANSDATE >= '05-MAY-2015'
               AND storeloc = 'KHSTORE'
             GROUP BY itemnum
        )
         ,table2 AS
        (
         SELECT SUM(balvalues) as balance,itemnum
         FROM table2_helper
         GROUP BY itemnum
        )
        SELECT *
        FROM table1
        JOIN table2
         ON table2 on table1.itemnum = table2.itemnum;
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.