3

I'm trying to edit a code someone wrote some months ago, but I can't understand some parts, for example:

CASE
   WHEN #PROMPT('SEL_TYPE')# = '%' then 1
   WHEN #PROMPT('SEL_TYPE')# = 'ALL' then 1
   WHEN e.evt_job = #PROMPT('SEL_TYPE')# then 1
   ELSE 0
END = 1

Wherever I read about how CASE .. WHEN works, it's like:

CASE A 
    WHEN 'ok' THEN C = 'ok'
    WHEN 'bad' THEN C = 'bad'

But im my example it's just THEN 1 or ELSE 0.

Whats the meaning of that 1 or 0? It's something I'm missing on the code, or that 1 or 0 means something?

Thanks all, and sorry for my English :)

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
Nanaki
  • 187
  • 1
  • 4
  • 13
  • I have to say, the code is part of a reporting tool, so that #PROMPT code is something the program asks when running the report. – Nanaki Sep 08 '15 at 07:08
  • You shouldn't ever need to comment *directly* to your own questions or answers here - if you have more to add, you can [edit] your question (there's also always an edit link at the bottom of your question, just below the tags) – Damien_The_Unbeliever Sep 08 '15 at 07:18

2 Answers2

5

You should understand the difference between the CASE expression and the CASE statement. This is a CASE expression:

CASE
   WHEN #PROMPT('SEL_TYPE')# = '%' then 1
   WHEN #PROMPT('SEL_TYPE')# = 'ALL' then 1
   WHEN e.evt_job = #PROMPT('SEL_TYPE')# then 1
   ELSE 0
END = 1

This is an incomplete CASE statement (Supported by other databases, like Oracle or MySQL, but not SQL Server):

CASE A 
    WHEN 'ok' THEN C = 'ok'
    WHEN 'bad' THEN C = 'bad'

An expression is something that can be evaluated on the right hand side of an assignment, or in a SELECT statement, for instance.

A statement is a command that can be used in an imperative language, i.e. in a stored procedure. The CASE statement (if supported by a database) works just like an IF statement.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • There is no `CASE` statement in T-SQL so answering as if there is is misleading, I think. – Damien_The_Unbeliever Sep 08 '15 at 07:09
  • Yep. Just realised... There is in PL/SQL, though. Will fix the answer. The OP said *"whenever I read about"*... perhaps they were reading about the `CASE` statement for other RDBMS – Lukas Eder Sep 08 '15 at 07:10
2

It means someone has overcomplicated things. If SQL Server had a boolean data type, they'd probably have just had then true, else false and no comparison at the end. But because that's not possible in SQL Server, they've substituted 1 and 0 and then just compare that to 1 at the end to make it a logical comparison.

It could equally have been written as:

#PROMPT('SEL_TYPE')# = '%' OR
#PROMPT('SEL_TYPE')# = 'ALL' OR
e.evt_job = #PROMPT('SEL_TYPE')#

With no need for a CASE expression at all.

Or even, probably, as just #PROMPT('SEL_TYPE')# IN ('%','ALL',e.evt_job), but some may feel that this obscures the intent a little too much.

So, 

 select code, wo_num, desc from table1
 where org = #PROMPT('SEL_ORG')# and
    CASE
       WHEN #PROMPT('SEL_WO_TYPE')# = '%' then 1
       WHEN #PROMPT('SEL_WO_TYPE')# = 'ALL' then 1
       WHEN e.evt_jobtype = #PROMPT('SEL_WO_TYPE')# then 1 ELSE 0 END = 1 
 and e.evt_type in (''A,'B')

Could have more simply been written as:

 select code, wo_num, desc from table1
 where org = #PROMPT('SEL_ORG')# and
   e.evt_type in (''A,'B') and
   (
       #PROMPT('SEL_TYPE')# = '%' OR
       #PROMPT('SEL_TYPE')# = 'ALL' OR
       e.evt_job = #PROMPT('SEL_TYPE')#
   )

Someone wrote a CASE expression (and then had to introduce the 1s and 0s) when all they needed was basic boolean logic.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • I can understand what you are saying, but still can't understand my code, I'll post an example: select code, wo_num, desc from table1 where org = #PROMPT('SEL_ORG')# and CASE WHEN #PROMPT('SEL_WO_TYPE')# = '%' then 1 WHEN #PROMPT('SEL_WO_TYPE')# = 'ALL' then 1 WHEN e.evt_jobtype = #PROMPT('SEL_WO_TYPE')# then 1 ELSE 0 END = 1 and e.evt_type in (''A,'B') – Nanaki Sep 08 '15 at 08:15
  • @Nanaki - comments isn't the best place to put code samples, as you're experiencing. But as I say in my answer, it's just someone overcomplicating things. You can replace the entire `CASE...END` expression there with either of the code samples in my answer (although you'd want to wrap the first with `()` brackets also because there are other expressions in the `WHERE` clause) and the query would produce exactly the same results. – Damien_The_Unbeliever Sep 08 '15 at 08:25
  • Thank you Damien, but what im trying to understand is the result of the CASE statment. It compares two things on the same line, and returns 1 or 0, but what happens when it's 0 or 1? – Nanaki Sep 08 '15 at 08:39
  • 1
    @Nanaki - after the end of the `CASE` expression, whatever value it has computed is compared to `1`. If the case has produced a `1` then that expression will be true. If the case has produced a `0`, then that expression will be false. They could have written `THEN 'T'` and `ELSE 'F'` and then compared with `'T'` at the end - as I say right at the top, they're working around the fact that SQL Server has no boolean data type so they can't just write `true` and `false`. And also, as I say, it's overcomplicated for what they're trying to do. – Damien_The_Unbeliever Sep 08 '15 at 08:47
  • But in my code, why it's needed to know if the `CASE` is 1 or 0? I mean, when you compare thing to get a true or false value, it's because if it's true you will do something, and if it's false you will do something else. But I can't see what the code will do with this true or false (1 or 0) values. – Nanaki Sep 08 '15 at 08:55
  • @Nanaki - I'm not sure how else to explain it. They're using a `CASE` expression to produce either a `1` or a `0`. They then compare that with a `1` - `CASE...END = 1`. So, it's a convoluted way to say "if any of the `WHEN` expressions is true, I want this whole thing to be true." – Damien_The_Unbeliever Sep 08 '15 at 09:00
  • And what is the 'whole thing'? I mean, if the whole expresión is to produce a 0 or 1, the code would be like: where code = 'A' **and 1 and** org = '3' Where the CASE expresions result is in bold. Maybe I'm just stupid, but I don't get it... – Nanaki Sep 08 '15 at 10:03
  • @Nanaki - look after the `END` keyword - there's a `= 1` there. That's the comparison. If you replace the `CASE` expression with what it computes, you end up with either `code = 'A' and 1 = 1 and org = '3'` or `code = 'A' and 0 = 1 and org = '3'`. – Damien_The_Unbeliever Sep 08 '15 at 10:08
  • Got it. So if the code is `where code = 'A' and 0 = 1 and org = '3'` the evaluated **where** will be false and won't be used on the select, but if its `code = 'A' and 1 = 1 and org = '3'` it will be true and used as a filter on the select. Thank you very much Damien, and sorry, jeje. – Nanaki Sep 08 '15 at 10:25