CODEWITHSUNDEEP
sqloracle-database
Jeff Guttry
Jeff Guttry

Reputation: 109

CASE expression "missing keyword"

I am currently trying to run the below SQL statement and am getting stuck on the case expression. I was trying to go off of other questions on here but am having no luck. Error being given when running the below is "missing keyword"

SELECT
   A.EQNO, A.ITEMNO, A.AVG_CYCLE, A.MFG_TYPE, A.ACTCAV, A.STDCAV, 
   A.ORIG_WO_QTY, A.TOTAL_FLOOR_DISPO, A.ORIG_WO_QTY - A.TOTAL_FLOOR_DISPO,

This is where I'm not sure 

   CASE A.ACTCAV 
     WHEN A.ACTCAV = 2 THEN 
       (3600 / A.AVG_CYCLE)*2 
   ELSE 
     WHEN A.ACTCAV = 1 THEN 
       (3600 / A.AVG_CYCLE) 
   ELSE 'UNKNOWN' 
   END A.ACTCAV

This part seems good 

 FROM   V_RT_CYCLE_PART_COUNTS A 
 LEFT OUTER JOIN MFGCELL B ON A.MFGCELL = B.MFGCELL 
 LEFT OUTER JOIN EPLANT C ON B.EPLANT_ID = C.ID
WHERE  A.MFG_TYPE = 'AIP-BLWMLD'
ORDER BY A.MFG_TYPE

Lastly, is it possible to take the A.ORIG_WO_QTY - A.TOTAL_FLOOR_DISPO / CASE Function results?

Trying to take the SQL and formulas from a crystal report I have created and throw it into a BI Dashboard

Upvotes: 0

Views: 382

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You are combining elements of an IF with both simple and searched case expression syntax. You also have a table alias prefix on your column alias, which isn't allowed.

You can either do:

   CASE A.ACTCAV
     WHEN 2 THEN 
       (3600 / A.AVG_CYCLE)*2 
     WHEN 1 THEN 
       (3600 / A.AVG_CYCLE) 
     ELSE 'UNKNOWN' 
   END AS ACTCAV

or

   CASE 
     WHEN A.ACTCAV = 2 THEN 
       (3600 / A.AVG_CYCLE)*2 
     WHEN A.ACTCAV = 1 THEN 
       (3600 / A.AVG_CYCLE) 
     ELSE 'UNKNOWN' 
   END AS ACTCAV

In both of them the stray ELSE in the middle has been removed (you don't need that between WHEN clauses, only right at the end for a default).

However, your two THEN clauses will return numbers, while 'UNKNOWN' is a string, which is going to throw "ORA-00932: inconsistent datatypes" at runtime. You either need to make the default a number, or explicitly convert the numbers to strings.

is it possible to take the A.ORIG_WO_QTY - A.TOTAL_FLOOR_DISPO / CASE Function results?

Not in the same level of query, unless you repeat the calculation. You would need to use an inline view or CTE, something like:

SELECT
   X.EQNO, X.ITEMNO, X.AVG_CYCLE, X.MFG_TYPE, X.ACTCAV, X.STDCAV, 
   X.ORIG_WO_QTY, X.TOTAL_FLOOR_DISPO, X.ORIG_WO_QTY - X.TOTAL_FLOOR_DISPO,
   X.NEW_ACTCAV, (X.ORIG_WO_QTY - X.TOTAL_FLOOR_DISPO) / X.NEW_ACTCAV
 FROM (
    SELECT
       A.EQNO, A.ITEMNO, A.AVG_CYCLE, A.MFG_TYPE, A.ACTCAV, A.STDCAV, 
       A.ORIG_WO_QTY, A.TOTAL_FLOOR_DISPO,
       CASE A.ACTCAV
         WHEN 2 THEN 
           (3600 / A.AVG_CYCLE)*2 
         WHEN 1 THEN 
           (3600 / A.AVG_CYCLE) 
         ELSE 'UNKNOWN' 
       END AS NEW_ACTCAV
     FROM   V_RT_CYCLE_PART_COUNTS A 
     LEFT OUTER JOIN MFGCELL B ON A.MFGCELL = B.MFGCELL 
     LEFT OUTER JOIN EPLANT C ON B.EPLANT_ID = C.ID
    WHERE  A.MFG_TYPE = 'AIP-BLWMLD'
  ) X

The inner query needs to get all the columns you want available in the outer query, plus any calculations you don't want to repeat. The outer query can then refer to any of those, including the alias you assign to any calculated values.

Upvotes: 4

Related Questions