Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2008
    Posts
    8

    Unanswered: SQL for Writing Reports

    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

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Apr 2008
    Posts
    8
    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

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Apr 2008
    Posts
    8
    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

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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:
    Code:
    IF ( search-condition , expression , expression )
    and an example:
    Code:
    IF
    
    The IF system scalar function provides conditional execution based on the truth value of a condition
    
    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
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  7. #7
    Join Date
    Apr 2008
    Posts
    8
    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 - :-)

  8. #8
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  9. #9
    Join Date
    Apr 2008
    Posts
    8
    Thanks for all of your expertise - it is much appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •