I am new to pervasive SQL but have worked with MS SQL 7 and 2000 for about 4 years. I am trying to populate a dynamic field in a view based of of statice results in a query however I am unable to get a CASE..WHEN or IF..THEN statement to work. Does anyone have any input on this?

http://www.pervasive.com/support/tec...ne_manuals.asp

try the manuals first. on that page should be the manuals for the major versions.

Hi,

Did you get a solution to your Pervasive SQL query on CASE when else ?

I have the exact same problem and would be interested to know the answer

regards

Bill

What version of Pervasive are you using?
I found this in the V8 SQL Engine Reference manual:
IF
Remarks
The IF system scalar function provides conditional execution based on the truth value of a condition

Examples
This expression prints the column header as "Prime1" and amount owed as 2000 where the value of the column amount_owed is 2000 or it prints a 0 if the value of the amount_owed column is not equal to 2000.

SELECT Student_ID, Amount_Owed,
IF (Amount_Owed = 2000, Amount_Owed, Convert(0, SQL_DECIMAL)) "Prime1"
FROM Billing

From table Class, the following example prints the value in the Section column if the section is equal to 001, else it prints "xxx" under column header Prime1

Under column header Prime2, it prints the value in the Section column if the value of the section column is equal to 002, or else it prints "yyy."

SELECT ID, Name, Section,
IF (Section = '001', Section, 'xxx') "Prime1",
IF (Section = '002', Section, 'yyy') "Prime2"

FROM Class

You can combine header Prime1 and header Prime2 by using nested IF functions. Under column header Prime, the following query prints the value of the Section column if the value of the Section column is equal to 001 or 002. Otherwise, it print "xxx."

SELECT ID, Name, Section,
IF (Section = '001', Section, IF(Section = '002', Section, 'xxx')) Prime

FROM Class

Hello,

Pervasive.SQL V8, 2000i, and 2000 do not support the CASE expression you mention. The CASE keyword in those versions of the product is used to ignore case when evaluating string columns.

Regards,
Tim Meiers
Pervasive Technical Publications

Tim,

Thanks for the reply. I have found a workaround

I used a 'double if' statement

eg
IF(("Addr Line1" = '' or "Addr Line1" is null), "Addr Mail Line1","Addr Line1") as "Addr Line1",

Bill