Link to home
Create AccountLog in
Avatar of Sheldon Livingston
Sheldon LivingstonFlag for United States of America

asked on

Pervasive CASE or IF statement

I am using Pervasive 10...

Can the IF or CASE statement return a field as opposed to just text?
Avatar of Bill Bach
Bill Bach
Flag of United States of America image

Yes.  In-line IF constructs are allowed, as in this example:
   SELECT IF( Myfield < 0, 0 , Myfield)
The standard syntax is:
    IF ( search-condition , expression , expression )

You can also use in-line CASE statements, using this syntax:
    CASE case_value_expression WHEN when_expression THEN then_expression

To see this in the docs, start the Pervasive Control Center, select Help/Documentation, open up the Advanced Reference section, then the SQL Engine Reference, find the SQL Syntax Reference in there, and select the "SELECT" keyword.  Scroll down to find the syntax diagram that shows how this works.
Avatar of Sheldon Livingston

ASKER

The code segment works:

  if(tsource = 'Interface trans','tsource','False') "My Field"

tsource is the name of the field.  This overall statement returns either "tsource" or "False" depending if tsource equals Interface trans or not.

This segment does not work:
  if(tsource = 'Interface trans',tsource,'False') "My Field"
I would have expected it to return either False or the contents of tsource.

Any thoughts?
I did this query against the DEMODATA database:

SELECT IF(LEFT(Name,3) = 'MAT',Name, 'Not a Math Class') AS "ClassName" FROM "Course"

This returns the results, as expected. For any class starting with "MAT", it displays the class name, or else it displays "Not a Math Class".  

Your query should work the same way:
   SELECT  IF(tsource = 'Interface trans', tsource, 'False') AS "My Field"
However, this query makes no sense -- why would you use tsource in the THEN part?  You could easily replace this with the constant string 'Interface trans' and it would work as well.
FYI -- I tested this on v10.31 and v11.21.
ASKER CERTIFIED SOLUTION
Avatar of Bill Bach
Bill Bach
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
My "segment" was just an example... for clarity...

When I remove the ' from tsource I get a Application-defined or object-defined error
Hmmm... this "works".

   SELECT  IF(tsource = 'Interface trans', tsource, tsource) AS "My Field"
Thus it is the "False" part of the equation that is messing up when I want to return a field.
I can return a field in either position on the DEMODATA database:
    SELECT IF(LEFT(Name,3) = 'MAT',Name, Description) AS "ClassName" FROM "Course"
Is there something unusual about your database table definition?  Get me the exact error message, or try running it with JUST this one field and the FROM clause...
The issue that I was having was that the True and False types had to be equal.

Thus, the line below works:

  if(tsource = 'Interface trans',convert(tsource,SQL_CHAR),'False') "My Field"
That was one of my questions (https://www.experts-exchange.com/questions/27934942/Pervasive-CASE-or-IF-statement.html?anchorAnswerId=38596691#a38596691), but I was concentrating on more of the string length as a possible issue.  Glad it is working now.
Had to "convert", or otherwise, utilize like types.