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

edited Nov 19 '18 at 23:57

William Robertson

8,20632233

asked Nov 19 '18 at 18:41

Jeff Guttry

227

What is the problem? Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 19 '18 at 18:46

I am getting an error stating "missing keyword" when using the above statement.
– Jeff Guttry
Nov 19 '18 at 18:49

I see, well two things. Next time include it on the question. Then try simplify the query to isolate what is causing the error
– Juan Carlos Oropeza
Nov 19 '18 at 18:51

WHERE A.MFG_TYPE = 'AIP-BLWMLD' ORDER BY A.MFG_TYPE If every row have the same type, no need to sort by type
– Juan Carlos Oropeza
Nov 19 '18 at 18:56

add a comment |

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

edited Nov 19 '18 at 23:57

William Robertson

8,20632233

asked Nov 19 '18 at 18:41

Jeff Guttry

227

What is the problem? Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 19 '18 at 18:46

I am getting an error stating "missing keyword" when using the above statement.
– Jeff Guttry
Nov 19 '18 at 18:49

I see, well two things. Next time include it on the question. Then try simplify the query to isolate what is causing the error
– Juan Carlos Oropeza
Nov 19 '18 at 18:51

WHERE A.MFG_TYPE = 'AIP-BLWMLD' ORDER BY A.MFG_TYPE If every row have the same type, no need to sort by type
– Juan Carlos Oropeza
Nov 19 '18 at 18:56

add a comment |

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

edited Nov 19 '18 at 23:57

William Robertson

8,20632233

asked Nov 19 '18 at 18:41

Jeff Guttry

227

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

sql oracle

edited Nov 19 '18 at 23:57

William Robertson

8,20632233

asked Nov 19 '18 at 18:41

Jeff Guttry

227

edited Nov 19 '18 at 23:57

William Robertson

8,20632233

asked Nov 19 '18 at 18:41

Jeff Guttry

227

edited Nov 19 '18 at 23:57

William Robertson

8,20632233

edited Nov 19 '18 at 23:57

William Robertson

8,20632233

edited Nov 19 '18 at 23:57

William Robertson

8,20632233

asked Nov 19 '18 at 18:41

Jeff Guttry

227

asked Nov 19 '18 at 18:41

Jeff Guttry

227

asked Nov 19 '18 at 18:41

Jeff Guttry

227

What is the problem? Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 19 '18 at 18:46

I am getting an error stating "missing keyword" when using the above statement.
– Jeff Guttry
Nov 19 '18 at 18:49

I see, well two things. Next time include it on the question. Then try simplify the query to isolate what is causing the error
– Juan Carlos Oropeza
Nov 19 '18 at 18:51

WHERE A.MFG_TYPE = 'AIP-BLWMLD' ORDER BY A.MFG_TYPE If every row have the same type, no need to sort by type
– Juan Carlos Oropeza
Nov 19 '18 at 18:56

add a comment |

What is the problem? Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 19 '18 at 18:46

I am getting an error stating "missing keyword" when using the above statement.
– Jeff Guttry
Nov 19 '18 at 18:49

I see, well two things. Next time include it on the question. Then try simplify the query to isolate what is causing the error
– Juan Carlos Oropeza
Nov 19 '18 at 18:51

WHERE A.MFG_TYPE = 'AIP-BLWMLD' ORDER BY A.MFG_TYPE If every row have the same type, no need to sort by type
– Juan Carlos Oropeza
Nov 19 '18 at 18:56

What is the problem? Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 19 '18 at 18:46

I am getting an error stating "missing keyword" when using the above statement.
– Jeff Guttry
Nov 19 '18 at 18:49

I see, well two things. Next time include it on the question. Then try simplify the query to isolate what is causing the error
– Juan Carlos Oropeza
Nov 19 '18 at 18:51

WHERE A.MFG_TYPE = 'AIP-BLWMLD' ORDER BY A.MFG_TYPE If every row have the same type, no need to sort by type
– Juan Carlos Oropeza
Nov 19 '18 at 18:56

add a comment |

1 Answer
1

active

oldest

votes

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

   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.

edited Nov 19 '18 at 19:03

answered Nov 19 '18 at 18:51

Alex Poole

129k6101176

1

Aditionally. Your case cant return two different data type. Right now return a float and a string. Also should use 3600.0 to make sure you get a float result
– Juan Carlos Oropeza
Nov 19 '18 at 18:53

Thank you guys! Works perfectly now.
– Jeff Guttry
Nov 19 '18 at 19:03

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53380809%2fcase-expression-missing-keyword%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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

   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).

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

edited Nov 19 '18 at 19:03

answered Nov 19 '18 at 18:51

Alex Poole

129k6101176

1

Aditionally. Your case cant return two different data type. Right now return a float and a string. Also should use 3600.0 to make sure you get a float result
– Juan Carlos Oropeza
Nov 19 '18 at 18:53

Thank you guys! Works perfectly now.
– Jeff Guttry
Nov 19 '18 at 19:03

add a comment |

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

   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).

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

edited Nov 19 '18 at 19:03

answered Nov 19 '18 at 18:51

Alex Poole

129k6101176

1

Aditionally. Your case cant return two different data type. Right now return a float and a string. Also should use 3600.0 to make sure you get a float result
– Juan Carlos Oropeza
Nov 19 '18 at 18:53

Thank you guys! Works perfectly now.
– Jeff Guttry
Nov 19 '18 at 19:03

add a comment |

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

   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).

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

edited Nov 19 '18 at 19:03

answered Nov 19 '18 at 18:51

Alex Poole

129k6101176

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

   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).

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

edited Nov 19 '18 at 19:03

answered Nov 19 '18 at 18:51

Alex Poole

129k6101176

edited Nov 19 '18 at 19:03

answered Nov 19 '18 at 18:51

Alex Poole

129k6101176

answered Nov 19 '18 at 18:51

Alex Poole

129k6101176

answered Nov 19 '18 at 18:51

Alex Poole

129k6101176

1

Aditionally. Your case cant return two different data type. Right now return a float and a string. Also should use 3600.0 to make sure you get a float result
– Juan Carlos Oropeza
Nov 19 '18 at 18:53

Thank you guys! Works perfectly now.
– Jeff Guttry
Nov 19 '18 at 19:03

add a comment |

1

Aditionally. Your case cant return two different data type. Right now return a float and a string. Also should use 3600.0 to make sure you get a float result
– Juan Carlos Oropeza
Nov 19 '18 at 18:53

Thank you guys! Works perfectly now.
– Jeff Guttry
Nov 19 '18 at 19:03

Aditionally. Your case cant return two different data type. Right now return a float and a string. Also should use 3600.0 to make sure you get a float result
– Juan Carlos Oropeza
Nov 19 '18 at 18:53

Thank you guys! Works perfectly now.
– Jeff Guttry
Nov 19 '18 at 19:03

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu