Equivalent statement in Sqlbase

[FRBhote]

In Sqlserver I have :

CASE WHEN DT <= '2018-01-31' AND DRATE > RATE THEN DRATE ELSE RATE END

Looking for an equivalent in Sqlbase.

This works ok but if the predicate is not satisfied I get 02213 EVA ISN Invalid @CHOOSE selector number:

@CHOOSE(DT - 2018-01-31, @CHOOSE( @ROUND(DRATE*100, 0) - @ROUND(RATE*100, 0), RATE, DRATE)

TIA

[Steve Leighton]

.
use coalesce wherever DT, RATE or DRATE could be NULL and replace it with 0 ( or whatever value you want ).

Code: Select all

@CHOOSE(  @COALESCE( DT - 2018-01-31,0)     , @CHOOSE( @ROUND( @COALESCE( DRATE,0 ) *100, 0) - @ROUND( @COALESCE( RATE,0 ) *100, 0), RATE, DRATE)   );

.

[FRBhote]

Lovely! Thank you.

How come it's not documented? If you have some documentation, I would be mighty grateful.

Untitled.png

[Steve Leighton]

.
All version of SQLBase manuals here: https://samples.tdcommunity.net/index.p ... ase_Books/

Check 'Language Reference - SQL Function Reference' for a full list of built-ins.
The online help is useless.

[FRBhote]

Problem.

Have a few hundred customers on SB7.6 and another few ditto on 8.5.

Works fine on 8.5 but not on 7.6 - no @COALESCE function.

Any other solution possible?

[Steve Leighton]

.
Replace @COALESCE with @NULLVALUE. Works the same. Replaces NULL with 0 .

Code: Select all

@CHOOSE(  @NULLVALUE( DT - 2018-01-31,0)     , @CHOOSE( @ROUND( @NULLVALUE( DRATE,0 ) *100, 0) - @ROUND( @NULLVALUE( RATE,0 ) *100, 0), RATE, DRATE)   )

[FRBhote]

I must be going old...

[Steve Leighton]

Maybe you should have sold them on SB12.2 by now