Solved

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

Posted on 2014-12-17
8
77 Views
Last Modified: 2015-01-08
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.
0
Comment
Question by:classnet
  • 4
  • 4
8 Comments
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40505230
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)))
0
 

Author Comment

by:classnet
ID: 40505236
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...
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40505263
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....
0
 

Author Comment

by:classnet
ID: 40505270
I get a "Invalid scalar funtion: ISNULL" or IFNULL.

Using version 11.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 28

Expert Comment

by:Bill Bach
ID: 40505318
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.
0
 

Author Comment

by:classnet
ID: 40505379
No idea what the issue is... everything is return the same error.
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 500 total points
ID: 40505405
I checked the PSQLv10 documentation from 2007, and both IFNULL and ISNULL are supported there.  So, ALL versions of v11 should work, too.   Are you accessing data on your LOCAL machine, or on a REMOTE server?  If a remote server, right-click the Server Name in the PCC and select Properties.  When prompted at the login box, click cancel, an dyou should see the version of the database engine on the server.

If you are using a local engine, do you have a full PSQL installation, or do you have a custom installation from an application vendor, such as Sage (Timberline).  This vendor provides a custom ODBC driver and may not support the IFNULL function.
0
 

Author Closing Comment

by:classnet
ID: 40538237
I reinstalled Pervasive and now all is well...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now