Using the SQL Firebird case statement

Question

Using the SQL Firebird case statement

Navigation

#1 by (3 votes)

3

I'm trying to do the following sql in firebird.

select distinct tb_proostarefas.*,
(case
    WHEN (tb_proostarefas.dt_prev is null and tb_proostarefas.dt_vencimento < date) then 1
    When (tb_proostarefas.dt_prev is not null and tb_proostarefas.dt_prev < date) then 2
    When (tb_proostarefas.dt_prev is not null and tb_proostarefas.dt_prev >= date) then 3
  else 3 end) AS 'STATUS'
from tb_proostarefas
order by tb_proostarefas.gut desc, tb_proostarefas.dt_vencimento

I have the following error:

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 3, column 83.
).

How can I resolve?

sql firebird

asked by anonymous 16.08.2017 / 13:12

1 answer

Show child results in parent array in Codeigniter Is it correct to use PHP code in the view layer along with HTML?

score 3 · Answer 1

The syntax of CASE does not conform to the firebird documentation, at least not this link.

For example, let's suppose that we have a database with a table called Products , with the following fields:

ProductID   ProductName      SupplierID   CategoryID    Unit                  Price
1           Chais            1            1             10 boxes x 20 bags    18
2           Chang            1            1             24 - 12 oz bottles    19
3           Aniseed Syrup    1            2             12 - 550 ml bottles   10

....

Now let's construct a CASE expression to extract ProductName , Preço and categories, but these are named according to ID, as follows:

Caso id=1, 'Categoria 1'
Caso id=2, 'Cateogira 2'
Caso id=7, 'Categproa 7'
Se nenhuma das anteriores, 'Outras'

We will construct the expression according to this syntax ( Simple CASE ):

CASE <expression>
   WHEN <exp1> THEN result1
   WHEN <exp2> THEN result2
   ...
   [ELSE defaultresult]
END

So our expression would be:

SELECT 
  ProductName, Price,
  case Categoryid
    when 1 then 'Categoria 1'
    when 2 then 'Categoria 2'
    when 7 then 'Cateroria 7'
    else 'Outras'
  end as category
from Products

To see the result, copy the above expression and paste it in the space for the SQL declaration of this link