#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    1
    Rep Power
    0

    Expressions inside CASE stmts


    Are compound expressions not allowed?....

    Error: Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 325 >
    SQL was: SELECT INVENTORY.*, LOCATION.*, s.SUPPLIER_NAME, SUBSTRING(INV_SEASONSTART FROM 1 FOR 2) AS SDAY, SUBSTRING(INV_SEASONSTART FROM 3 FOR 2) AS SMONTH, SUBSTRING(INV_SEASONEND FROM 1 FOR 2) AS EDAY, SUBSTRING(INV_SEASONEND FROM 3 FOR 2) AS EMONTH, '2012' as SYEAR, '2012' as EYEAR, '11' as CMONTH, case SYEAR When ((SMONTH > EMONTH) AND (SMONTH > CMONTH)) THEN 2012 - 1 end, case EYEAR When ((SMONTH > EMONTH) AND (SMONTH < CMONTH)) THEN 2012 + 1 end, SYEAR || '-' || SMONTH || '-' || SDAY as STARTDATE, EYEAR || '-' || EMONTH || '-' || EDAY as ENDDATE from INVENTORY LEFT JOIN LOCATION ON LOC_ID = INV_LOCATIONID LEFT JOIN INVENTORY_SUPPLIER ON INVSUP_INVID = INV_ID LEFT JOIN SUPPLIER s ON SUPPLIER_NUMBER = INVSUP_SUPPNUM WHERE ((INV_DEPTID LIKE '3') and (s.SUPPLIER_NUMBER like '3009') and ((INV_MINSTOCK * 100 / 100) > INV_STOCKONHAND)and (INV_DELETED = '0'))
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    There are two forms of the case expression (a case statement is something else) and you are mixing those.

    It should be

    Code:
    case When SMONTH > EMONTH AND SMONTH > CMONTH THEN 2012 - 1 end
    There are some other issues in your code. You are using correlation names such as SMONTH in the select list where they also are defined, which is not allowed in standard SQL. The normal way of solving this is to put those expressions within a derived table after which it is possible to refer to them in the outer select

    Code:
    select INVENTORY.*,
           LOCATION.*, 
           s.SUPPLIER_NAME,
           SDAY, 
           SMONTH, 
           EDAY, 
           EMONTH,
           case When SMONTH > EMONTH AND SMONTH > CMONTH THEN 2012 - 1 end,
           case When SMONTH > EMONTH AND SMONTH < CMONTH THEN 2012 + 1 end,
           SYEAR || '-' || SMONTH || '-' || SDAY as STARTDATE,
           EYEAR || '-' || EMONTH || '-' || EDAY as ENDDATE 
      from (select INVENTORY.*,
                   LOCATION.*, 
                   s.SUPPLIER_NAME, 
                   SUBSTRING(INV_SEASONSTART FROM 1 FOR 2) AS SDAY, 
                   SUBSTRING(INV_SEASONSTART FROM 3 FOR 2) AS SMONTH, 
                   SUBSTRING(INV_SEASONEND FROM 1 FOR 2) AS EDAY, 
                   SUBSTRING(INV_SEASONEND FROM 3 FOR 2) AS EMONTH,
                   '2012' as SYEAR,
                   '2012' as EYEAR,
                   '11' as CMONTH
              from INVENTORY 
              left
              JOIN LOCATION
                ON LOC_ID = INV_LOCATIONID
              left
              JOIN INVENTORY_SUPPLIER
                ON INVSUP_INVID = INV_ID
              left
              JOIN SUPPLIER s
                ON SUPPLIER_NUMBER = INVSUP_SUPPNUM
               and s.SUPPLIER_NUMBER = 3009
             WHERE INV_DEPTID = 3
               and INV_MINSTOCK*100/100 > INV_STOCKONHAND
               and INV_DELETED = 0) dt
    I moved the condition on the supplier from the where clause since as that would have caused your outer joins to become inner joins. I also removed the like predicate and the unnecessary apostrophes (assuming that those columns are defined as integers).

IMN logo majestic logo threadwatch logo seochat tools logo