Блог

Я пользователь MySQL, которому приходится собирать воедино запрос Oracle, и у меня плохо получается…

Я получаю эти ошибки:

 Unable to resolve symbol 'sa'
unexpected 'value'
',', <select into clause> or FROM expected, got 'THEN'
 

Это мой запрос

 SELECT
      sa.STA_AUTO_KEY
     , sa.CREDIT_ACCOUNT_NUMBER
     , sa.INVC_NUMBER
     , sod.ROUTE_CODE
     , inh.INVOICE_DATE
     , sa.SHIP_DATE
     , inh.POST_DATE
     , inh.POST_STATUS
     , sa.TRAN_TYPE
     , sa.NEW_PN as PN
     , stm.DESCRIPTION
     , sa.NEW_SERIAL_NUMBER as SERIAL_NUMBER
     , sa.NEW_UNIT_PRICE as UNIT_PRICE
     , STM.UNIT_FREIGHT_COST
     , sa.NEW_UNIT_COST as UNIT_COST
     , sa.OLD_QTY_OH - sa.NEW_QTY_OH as QTY_INVOICED
     , sa.AMOUNT as GROSS
     , (stm.UNIT_FREIGHT_COST   sa.NEW_UNIT_COST) as COST
     , sod.SOD_AUTO_KEY
     , sa.NEW_CONDITION_CODE as CONDITION_CODE
     , sys.USER_NAME as SALESPERSON_CODE
     , sa.SO_NUMBER
     , cmp.COMPANY_NAME
     , cmp.COMPANY_CODE
     , cnc.CONSIGNMENT_CODE
     , IF sa.NEW_SERIAL_NUMBER IS NULL THEN
            sa.NEW_UNIT_PRICE * (sa.OLD_QTY_OH - sa.NEW_QTY_OH) as GROSS
            sa.NEW_UNIT_PRICE * (sa.OLD_QTY_OH - sa.NEW_QTY_OH) - (STM.UNIT_FREIGHT_COST   sa.NEW_UNIT_COST) as NET
        ELSEIF sa.NEW_SERIAL_NUMBER = 'nsn' THEN
            sa.NEW_UNIT_PRICE * (sa.OLD_QTY_OH - sa.NEW_QTY_OH) as GROSS
            sa.NEW_UNIT_PRICE * (sa.OLD_QTY_OH - sa.NEW_QTY_OH) - (STM.UNIT_FREIGHT_COST   sa.NEW_UNIT_COST) as NET
        ELSEIF sa.NEW_SERIAL_NUMBER = 'NSN' THEN
            sa.NEW_UNIT_PRICE * (sa.OLD_QTY_OH - sa.NEW_QTY_OH) as GROSS
            sa.NEW_UNIT_PRICE * (sa.OLD_QTY_OH - sa.NEW_QTY_OH) - (STM.UNIT_FREIGHT_COST   sa.NEW_UNIT_COST) as NET
        ELSE
         sa.NEW_UNIT_PRICE as GROSS
         sa.NEW_UNIT_PRICE - (STM.UNIT_FREIGHT_COST   sa.NEW_UNIT_COST) as NET
        ENDIF
FROM STOCK_AUDIT sa
    LEFT OUTER JOIN INVC_HEADER inh ON sa.INH_AUTO_KEY = inh.INH_AUTO_KEY
    LEFT OUTER JOIN SO_DETAIL sod ON (sa.SOH_AUTO_KEY = sod.SOH_AUTO_KEY) AND (sa.SOD_AUTO_KEY = sod.SOD_AUTO_KEY)
    LEFT OUTER JOIN STOCK stm ON sa.NEW_STM_AUTO_KEY = stm.STM_AUTO_KEY
    LEFT OUTER JOIN SYS_USERS sys ON sod.SYSUR_AUTO_KEY = sys.SYSUR_AUTO_KEY
    LEFT OUTER JOIN COMPANIES cmp ON inh.CMP_AUTO_KEY = cmp.CMP_AUTO_KEY
    LEFT OUTER JOIN CONSIGNMENT_CODES cnc ON sa.NEW_CNC_AUTO_KEY = cnc.CNC_AUTO_KEY
WHERE inh.POST_DATE >= TO_DATE ('2020-09-01', 'YYYY-MM-DD')
    AND inh.POST_DATE <= TO_DATE ('2020-09-30', 'YYYY-MM-DD')
    AND (sa.TRAN_TYPE = 'Post Invoice' OR sa.TRAN_TYPE = 'Invoice Return')
    AND sod.ROUTE_CODE IN ('M', 'S')
 

Любая помощь приветствуется

IF ... THEN ... ELSE недопустимый синтаксис SQL в Oracle (но вы можете использовать этот синтаксис в PL / SQL).

Вы хотите использовать CASE или DECODE . Вот так:

 SELECT
      sa.STA_AUTO_KEY
     , sa.CREDIT_ACCOUNT_NUMBER
     , sa.INVC_NUMBER
     , sod.ROUTE_CODE
     , inh.INVOICE_DATE
     , sa.SHIP_DATE
     , inh.POST_DATE
     , inh.POST_STATUS
     , sa.TRAN_TYPE
     , sa.NEW_PN as PN
     , stm.DESCRIPTION
     , sa.NEW_SERIAL_NUMBER as SERIAL_NUMBER
     , sa.NEW_UNIT_PRICE as UNIT_PRICE
     , STM.UNIT_FREIGHT_COST
     , sa.NEW_UNIT_COST as UNIT_COST
     , sa.OLD_QTY_OH - sa.NEW_QTY_OH as QTY_INVOICED
     , sa.AMOUNT as GROSS
     , (stm.UNIT_FREIGHT_COST   sa.NEW_UNIT_COST) as COST
     , sod.SOD_AUTO_KEY
     , sa.NEW_CONDITION_CODE as CONDITION_CODE
     , sys.USER_NAME as SALESPERSON_CODE
     , sa.SO_NUMBER
     , cmp.COMPANY_NAME
     , cmp.COMPANY_CODE
     , cnc.CONSIGNMENT_CODE
     , CASE
       WHEN sa.NEW_SERIAL_NUMBER IS NULL OR sa.NEW_SERIAL_NUMBER IN ( 'nsn', 'NSN' )
       THEN sa.NEW_UNIT_PRICE * (sa.OLD_QTY_OH - sa.NEW_QTY_OH)
       ELSE sa.NEW_UNIT_PRICE
       END AS gross
     , CASE
       WHEN sa.NEW_SERIAL_NUMBER IS NULL OR sa.NEW_SERIAL_NUMBER IN ( 'nsn', 'NSN' )
       THEN sa.NEW_UNIT_PRICE * (sa.OLD_QTY_OH - sa.NEW_QTY_OH) - (STM.UNIT_FREIGHT_COST   sa.NEW_UNIT_COST)
       ELSE sa.NEW_UNIT_PRICE - (STM.UNIT_FREIGHT_COST   sa.NEW_UNIT_COST)
       END AS net
FROM STOCK_AUDIT sa
    LEFT OUTER JOIN INVC_HEADER inh ON sa.INH_AUTO_KEY = inh.INH_AUTO_KEY
    LEFT OUTER JOIN SO_DETAIL sod ON (sa.SOH_AUTO_KEY = sod.SOH_AUTO_KEY) AND (sa.SOD_AUTO_KEY = sod.SOD_AUTO_KEY)
    LEFT OUTER JOIN STOCK stm ON sa.NEW_STM_AUTO_KEY = stm.STM_AUTO_KEY
    LEFT OUTER JOIN SYS_USERS sys ON sod.SYSUR_AUTO_KEY = sys.SYSUR_AUTO_KEY
    LEFT OUTER JOIN COMPANIES cmp ON inh.CMP_AUTO_KEY = cmp.CMP_AUTO_KEY
    LEFT OUTER JOIN CONSIGNMENT_CODES cnc ON sa.NEW_CNC_AUTO_KEY = cnc.CNC_AUTO_KEY
WHERE   inh.POST_DATE >= DATE '2020-09-01'
    AND inh.POST_DATE <  DATE '2020-10-01'
    AND sa.TRAN_TYPE IN ( 'Post Invoice', 'Invoice Return' )
    AND sod.ROUTE_CODE IN ('M', 'S')
 

Не используйте 2020-09-30 в качестве верхней границы, если ваши даты имеют компонент времени, отличного от полуночи, так как вы не получите никаких данных между 2020-09-30 00:00:01 и 2020-09-30 23:59:59 . Вместо этого фильтр включен < DATE '2020-10-01' .