Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Question Unanswered: Pervasive Syntax?

    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?

  2. #2
    Join Date
    Sep 2003
    Posts
    8
    http://www.pervasive.com/support/tec...ne_manuals.asp

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

  3. #3
    Join Date
    Apr 2003
    Location
    Sydney
    Posts
    3

    Question Re: Pervasive Syntax?

    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

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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
    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
    Jul 2003
    Posts
    18

    Re: Pervasive Syntax?

    Originally posted by clutch
    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?
    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

  6. #6
    Join Date
    Apr 2003
    Location
    Sydney
    Posts
    3

    Talking

    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

Posting Permissions

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