Hello,

I have been trying to locate a good resource for examples of SQL for pervasive and am not having much luck. Is anyone aware of a good source for this? In general I need to write select queries for Crystal Reports. I am running into problems trying to use the syntax I learned to love so much from Oracle (DECODE, CASE, etc.).

Also, is there a query engine like Toad that everyone recommends? I have heard that some like to use WinSQL for Pervasive.

Thank you all! Cheers!

ian

The best resource for SQL syntax for Pervasive is the Pervasive website (http://www.pervasive.com/library). You'll find subtle differences between the versions so make sure you pick the one for your version.
I've never used Toad or WinSQL. I prefer ODBC Test or the Pervasive COntrol Center (for executing SQL statements outside my applications).


As to your current problem, a few questions:
- What error are you seeing?
- What version of PSQL are you using?
- What's the actual SQL statement returning the error?

Thank you for the quick post and the resource. What I am trying to do is a build a parameter in Crystal that gives either the option to leave blank or to enter 0 for all records. This may not be exact, but I have done this in the past with Oracle using something like:

DECODE({?Param},0,0,{@Field}) = {?Param}

or

CASE WHEN {?Param} = '' THEN {@Field} = {@Field}
ELSE {@Field} = {?Param}

or

IF {?Param} = '' THEN {@Field} = {@Field}
ELSE {@Field} = {?Param}

Again, this syntax may not be exact - it's been a little while. Thank you much for your expertise!

Cheers,
ian

What version of PSQL are you using? the CASE WHEN syntax is not supported prior to v9.
DECODE isn't supported at all.
Here's an example of the CASE (without parameters):
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

Unfortunately, I'm not familiar with Crystal.

I believe that we are on v8, but am not positive. The database is actually the backend to a construction accounting system (Timberline). I thank you again for your assistant with this.

Much appreciated,
ian

If you are on V8 (I think Timberline only supported v8), then the CASE WHEN won't work. You can use the IF statement though:
and an example:

This is great information - thank you very much. While we're on the Timberline, v8 subject, can you give any warnings on the upgrade that is soon to occur to v9? Should we expect any compatability issues with this upgrade? I am excited to have my CASE statement back - :-)

I can't speak for Timberline as I've only seen it a couple of times. As far as PSQL, the upgrade is fairly painless although the PCC does change quite a bit. In V8, it's Win32. With V9 (and later), it's Java based.

Thanks for all of your expertise - it is much appreciated.