CODEWITHSUNDEEP
stored-proceduresif-statementfirebirdwhere-clauseclause
Rolf L
Rolf L

Reputation: 21

Firebird SQL using <if> in <where>

I try to construct a SP in FB 2.5 that looks like that:

where 
   (
      (kunde between :kdvon and :kdbis)
      and 
          (AdrGrp between :AdrGrpvon and :AdrGrpbis)
      and
          (auftragstyp between :AuftrTypVon and :AuftrTypBis)
      and
          (Status between :statusvon and :statusbis)
      and
      IF LFDNR <> 0 THEN (LFDNUMMER  = :LFDNR)
      IF BESTELLTAG <> 0 then (bestelldatum  = :BESTELLTAG)
   )

It worked well until the If Statements were inserted. After that I get the message SQL error code = -104. Token unknown - line 156, column 14. LFDNR.

The IF conditions are defined as Input Parameters. Using the colon : to mark them as parameters did not work.

Question Is it possible to use "If" inside "Where" in this way? How do I have to use the condidional params?

TiA Rolf

Upvotes: 1

Views: 2470

Answers (1)

lc.
lc.

Reputation: 116458

Try this instead:

...
AND (LFNDR = 0 OR LFDNUMMER = :LFDNR)
AND (BESTELLTAG = 0 OR bestelldatum = :BESTELLTAG)

(I don't know if LFNDR and BESTELLTAG are parameters/variables or columns in the = 0 clause - if they are parameters (the same as the right hand part), you should probably mark them with a ':')

Upvotes: 3

Related Questions