Equivalent statement in Sqlbase

Discussion forum about all things SqlBase.
FRBhote
India
Posts: 2172
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Equivalent statement in Sqlbase

Post by FRBhote » 25 Jun 2020, 06:42

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

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 274
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: Equivalent statement in Sqlbase

Post by Steve Leighton » 26 Jun 2020, 02:29

.
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)   );
.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

FRBhote
India
Posts: 2172
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Equivalent statement in Sqlbase

Post by FRBhote » 26 Jun 2020, 05:05

Lovely! Thank you.

How come it's not documented? If you have some documentation, I would be mighty grateful.
Untitled.png
You do not have the required permissions to view the files attached to this post.

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 274
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: Equivalent statement in Sqlbase

Post by Steve Leighton » 26 Jun 2020, 06:20

.
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.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

FRBhote
India
Posts: 2172
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Equivalent statement in Sqlbase

Post by FRBhote » 29 Jun 2020, 06:11

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?

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 274
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: Equivalent statement in Sqlbase

Post by Steve Leighton » 29 Jun 2020, 12:15

.
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)   )
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

FRBhote
India
Posts: 2172
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Equivalent statement in Sqlbase

Post by FRBhote » 29 Jun 2020, 12:50

I must be going old...

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 274
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: Equivalent statement in Sqlbase

Post by Steve Leighton » 30 Jun 2020, 01:06

:roll:
Maybe you should have sold them on SB12.2 by now :!:
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests