Link to home
Start Free TrialLog in
Avatar of Sheldon Livingston
Sheldon LivingstonFlag for United States of America

asked on

Pervasive SQL

I wish to return one of four fields depending on a parameter... how would I do this in Pervasive 10?  Nested if's don't seem to work.
Avatar of Bill Bach
Bill Bach
Flag of United States of America image

Nested IF statements should work, but you may have some data conversion to do to get it working properly.  ;-)

The CASE statement is a much better solution.  Here is an example straight from the manuals:
SELECT name 'Course ID', description 'Course Title',  
CASE name  
WHEN 'Art 101' THEN 'None'  
WHEN 'Art 102' THEN 'Art 101 or instructor approval' 
WHEN 'Art 203' THEN 'Art 102' 
WHEN 'Art 204' THEN 'Art 203' 
WHEN 'Art 305' THEN 'Art 101' 
WHEN 'Art 406' THEN 'None' 
WHEN 'Art 407' THEN 'Art 305' 
END 
AS 'Prerequisites' FROM Course WHERE Dept_Name = 'Art' 
ORDER BY name  

Open in new window

If that doesn't work for you, post your exact query & we'll see what is wrong with it...
What error do you get?  Using v11, I was able to use a nested IF statement:
select if(id<10,if(id<5,id,id*10),if(id<20,id*100,id*1000)) from class where id <20

Open in new window

returns:
"EXPR_1"
1
2
3
4
50
60
70
80
90
1000
1100
1200
1300
1400
1500
1600
1700
1800
1900

Open in new window

Avatar of Sheldon Livingston

ASKER

Here's the code... I think...

SELECT 
  "TJOB" AS "Job",
  "TEXTRA" AS "Extra",
  "TRANDAT" AS "Transaction Date",
  "TACTDAT" AS "Accounting Date",
  "TRANTYP" AS "Transaction Type",
  "TDESC" AS "Description",
  "TUNITS" AS "Units",
  "TUNTCST" AS "Unit Cost",
  "TAMOUNT" AS "Amount",
  "TSOURCE" AS "Source",
  
  CASE "TRANTYP"
      when 'Work billed' then 'TJOB'
      when 'Cash receipt' then 'TDESC'
      when 'Scheduled value' then 'TEXTRA'
  END AS "My Field"

FROM 
  "CURRENT_JCT_RECORD_1"

WHERE 
   "TJOB"='300-11-3283'  

Open in new window

Yes... that's the code.  Doesn't work for me.
The syntax is correct.  What about data types on this one, too?  What exact error do you get when you run this from the PCC?  Is the field TRANTYP and not TRANTYPE?

In other words, there must be SOME reason why this doesn't work, but you need to either provide more information or post access to your exact database.
Does this work against DEMODATA?

select case "name" 
	when 'ACC 101' then 'TJOB'
	when 'PHY 204' then 'TDESC'
	when 'PSC 101' then 'TEXTRA'
end as "My Field" from class

Open in new window


What error are you getting?
I'm querying a Timberline accounting database that uses the Pervasive engine.

TRANTYP is correct.

I get that same "Application-defined or object-defined error" error.
Are you using a pure Pervasive ODBC driver, or are you using the special Timberline driver?  This COULD be a limitation on the ODBC driver itself that Timberline provides, and testing with a "true" Pervasive ODBC may be worth a try.
I am stuck using the Timberline driver... sounds like I might have an issue.
ASKER CERTIFIED SOLUTION
Avatar of Bill Bach
Bill Bach
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you!  Nested if worked...
Jut FYI, my understanding is that the Timberline driver isn't a "customized" version of the PSQL driver but is another driver that uses the Simba engine.  It was written by Timberline for Timberline.