×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
On 11-Jun-2015 08:51 -0600, Alan Shore wrote:
I tried the following
SELECT OPER,
case BOX_SIZE when '1'then SEL0003 end as Count1,
case BOX_SIZE when '2'then SEL0003 end as Count2,
case BOX_SIZE when '3'then SEL0003 end as Count3,
case BOX_SIZE when '4'then SEL0003 end as Count4,
case BOX_SIZE when '5'then SEL0003 end as Count5,
case BOX_SIZE when '6'then SEL0003 end as Count6,
case BOX_SIZE when '7'then SEL0003 end as Count7,
case BOX_SIZE when '8'then SEL0003 end as Count8,
case BOX_SIZE when '9'then SEL0003 end as Count9,
case BOX_SIZE when '0'then SEL0003 end as Count0,
case BOX_SIZE when not between '0' and '9' then
SEL0003 end as Count_Other
FROM ORDSPCKEDZ
And received the error
Keyword BETWEEN not expected. Valid tokens: + - THEN.
Now seeing the /problem/ shown as the error message [msg SQL0199],
the issue is now conspicuous to me.
There are two forms for the CASE expression. One is the
simple-when-clause and the other uses a searched-when-clause; all of the
above are written as simple-when-clause, but unfortunately the /simple/
is limited to just an implicit equal-predicate. Although I knew that,
without knowing\seeing the error, I had completely overlooked that;
first because the conspicuous errors for the missing and extraneous
commas in the OP, and second because both the IN and BETWEEN predicates
just appear to fit\read so well despite not being supported.
Anyhow, when using the CASE-expression whereby the first token after
CASE is *not* WHEN, because that is the simple-when form for which there
is an implicit equivalence test, what follows the WHEN must be a
scalar-expression. Therefore the the CASE-expression used to define the
result-column of Count_Other needs to use the searched-when form of the
CASE-expression, such as one of the following [assuming BETWEEN
predicate is inclusive]:
case when BOX_SIZE not between '0' and '9' then
SEL0003 end as Count_Other
case when BOX_SIZE not in ( '0', '1', '2', '3', '4'
, '5', '6', '7', '8', '9') then
SEL0003 end as Count_Other
For reference:
[
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzcaseexp.htm]
_CASE expression_
"CASE expressions allow an expression to be selected based on the
evaluation of one or more conditions.
..."
midrange.com
