Using the Firebird SQL case statement

Asked

Viewed 2,406 times

3

I’m trying to make 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 solve?

sql firebird
  • 1

    He is not recognizing this "date". What do you want to use there? Is it the current date? "Token Unknown - line 3, column 83."

  • 1

    Completing what the friend above said, if Voce wants it to return to the current date put the current_date command

1 answer

3

The syntax of the construction CASE does not agree with Firebird documentation, at least not with that link.

To illustrate, let’s assume that we have the one bank 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 build an expression CASE to extract ProductName, Preço and categories, but these are named according to the 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 build the expression according to this syntax (Simple CASE):

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

Then 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 expression above and paste into the space to declare the expression SQL of this link.

Browser other questions tagged sql firebird

You are not signed in. Login or sign up in order to post.