asked on 12/17/2014

Pervasive SQL addition of fields where one or more may be null

Hello...

I'd like to write a query that gives me ((Field A + Field B) - (Field C + Field D))

I get a blank, perhaps NULL, if any of the fields are null. Thus Field A may be 22 but if fields C, D or E are null then the answer appears to be null.

Any Advice.

SQL

Last Comment

Sheldon Livingston

1/8/2015

Bill Bach

12/17/2014

You don't mention the exact version of PSQL, but the later versions support the IFNULL() function. Basically, if the field is non-null, the field is returned. If it is null, the alternate value is returned. In arithmetic like this, it would simply be:
((IFNULL(FieldA,0) + IFNULL(FieldB,0)) - (IFNULL(FieldC,0) + IFNULL(FieldD,0)))

Sheldon Livingston

12/17/2014

ASKER

Sorry... should have clarified. I'm want to sum up... thus:

SUM ((IFNULL(FieldA,0) + IFNULL(FieldB,0)) - (IFNULL(FieldC,0) + IFNULL(FieldD,0))) = Something

IFNULL isn't a valid scalar function...

Bill Bach

12/17/2014

IFNULL() is certainly valid in the current release:
http://docs.pervasive.com/products/database/psqlv11/wwhelp/wwhimpl/js/html/wwhelp.htm#href=ODBC/scalarfunc.05.6.html#1138644

Perhaps you can try ISNULL() instead. Better yet, include the exact version number that you ARE using....

Sheldon Livingston

12/17/2014

ASKER

I get a "Invalid scalar funtion: ISNULL" or IFNULL.

Using version 11.

Bill Bach

12/17/2014

Very odd. I just ran this test query on PSQLv11.30 using the DEMODATA database:
SELECT ISNULL(Last_Name,'') FROM Person
and it works fine. Can you test the same query? I also ran it with IFNULL(), too.

Sheldon Livingston

12/17/2014

ASKER

No idea what the issue is... everything is return the same error.

ASKER CERTIFIED SOLUTION

Bill Bach

12/17/2014