Construction CASE Firebird

Asked

Viewed 189 times

1

I need to build a structure CASE in the SQL Firebird only you’re not accepting the way I’m doing.

SELECT 
  CONTAS_RECEBER.ID,
  CONTAS_RECEBER.DT_VENC,
  CONTAS_RECEBER.TIPO_OPERACAO,
  CONTAS_RECEBER.ID_OPERACAO,
  CASE
    WHEN CONTAS_RECEBER.TIPO_OPERACAO = 'DAV'  THEN
       (SELECT VENDEDOR.DESCRICAO FROM DAV LEFT JOIN VENDEDOR on(VENDEDOR.ID=DAV.ID_VENDEDOR) WHERE DAV.ID CONTAS_RECEBER.ID_OPERACAO) 

    WHEN CONTAS_RECEBER.TIPO_OPERACAO = 'NFCe' THEN
       (SELECT VENDEDOR.DESCRICAO FROM NFCE LEFT JOIN VENDEDOR on (VENDEDOR.ID = NFCE.ID_VENDEDOR) WHERE NFCE.ID CONTAS_RECEBER.ID_OPERACAO)

    WHEN CONTAS_RECEBER.TIPO_OPERACAO = 'NFe' THEN
       (SELECT VENDEDOR.DESCRICAO FROM NFE LEFT JOIN VENDEDOR on (VENDEDOR.ID = NFE.ID_VENDEDOR) WHERE NFE.ID CONTAS_RECEBER.ID_OPERACAO)
  ELSE 'VENDEDOR NÃO INFORMADO'  END AS NOME_VENDEDOR,  
  CONTAS_RECEBER.VLR_QUITACAO AS VLR_PAGO,
  CONTAS_RECEBER.VLR_CONTA
FROM
  CONTAS_RECEBER
  LEFT OUTER JOIN CLIENTES ON (CLIENTES.ID_CLIENTE = CONTAS_RECEBER.ID_CLIENTE)

The error that appears is this:

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 12, column 105.
CONTAS_RECEBER.

I also have to fetch the name of the seller that is in the seller table, and the Accounts Receivable table links to the tables that contain the Seller ID, these references are in CONTAS_RECEBER.TIPO_OPERACAO, which gives the name of the table where I go find the Seller ID and CONTAS_RECEBER.ID_OPERACAO which is the ID identifies the sale.

If you need more information just ask.

firebird firebird-2.5
  • 1

    Refer to the manual because I believe that this type of construction is not allowed in the SQL standard. Anyway it lacked the operand in its clauses WHERE. An alternative is to make all the Join in SELECT and use the result fields in the CASE expression.

1 answer

1


For what it says in firebirdsql.org, in the CASE will only accept as a result values literal or variables, compound expressions or NULL literals.

only your code is missing a fine the Operator "=" in the clause WHERE do Select inside the case so the error occurs. I’ll put your corrected code here.

SELECT 
  CONTAS_RECEBER.ID,
  CONTAS_RECEBER.DT_VENC,
  CONTAS_RECEBER.TIPO_OPERACAO,
  CONTAS_RECEBER.ID_OPERACAO,
  CASE CONTAS_RECEBER.TIPO_OPERACAO
    WHEN 'DAV'  THEN (SELECT VENDEDOR.DESCRICAO FROM DAV  LEFT JOIN VENDEDOR on (VENDEDOR.ID = DAV.ID_VENDEDOR)  WHERE DAV.ID  = CONTAS_RECEBER.ID_OPERACAO) 
    WHEN 'NFCe' THEN (SELECT VENDEDOR.DESCRICAO FROM NFCE LEFT JOIN VENDEDOR on (VENDEDOR.ID = NFCE.ID_VENDEDOR) WHERE NFCE.ID = CONTAS_RECEBER.ID_OPERACAO)
    WHEN 'NFe'  THEN (SELECT VENDEDOR.DESCRICAO FROM NFE  LEFT JOIN VENDEDOR on (VENDEDOR.ID = NFE.ID_VENDEDOR)  WHERE NFE.ID  = CONTAS_RECEBER.ID_OPERACAO)
    ELSE 'SEM VENDEDOR'
  END AS NOME_VENDEDOR,
  CONTAS_RECEBER.VLR_QUITACAO AS VLR_PAGO,
  CONTAS_RECEBER.VLR_CONTA
FROM
  CONTAS_RECEBER
    LEFT OUTER JOIN CLIENTES ON (CLIENTES.ID_CLIENTE = CONTAS_RECEBER.ID_CLIENTE)

Browser other questions tagged firebird firebird-2.5

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