Pervasive SQL Syntax

Last post 07-25-2005 8:53 AM by info@mirtheil.com. 4 replies.
Page 1 of 1 (5 items)
Sort Posts: Previous Next
  • 07-22-2005 1:37 PM

    Pervasive SQL Syntax

    I am working on some queries in Pervasive and the language isn't quite what I am used to. I'm used to MS SQL 2000. I'm having problems with the comparison operator side of the query. For example:

    SELECT * FROM "JCJHST01" where shipped = ""

    Some of the items in the shipped field are null. I can take care of that just fine with "is null". My above example is how I would evaluate the shipped field in MS SQL when there is an empty character set. I get errors when I try to do so in Pervasive.
    The error is: ODBC Error: SQLSTATE = 22008, Native error code = 0
    Invalid date, time or timestamp value.
  • 07-22-2005 3:19 PM In reply to

    Re: Pervasive SQL Syntax

    It would appear that some of your date fields have incorrect data.

    See if you can list and these records in the PCC.
  • 07-25-2005 7:38 AM In reply to

    Re: Pervasive SQL Syntax

    1.) you must quote string literals with single quotes, not double quotes. So there is a chance that the statment works if you type

    SELECT * FROM "JCJHST01" where shipped = '' or
    SELECT * FROM "JCJHST01" where shipped = 0
    or
    SELECT * FROM "JCJHST01" where shipped is
    null

    2.) If this does not work: It looks like column "shipped" is of type "timestamp". Consult the docs how to construct a timestamp literal
  • 07-25-2005 7:48 AM In reply to

    Re: Pervasive SQL Syntax

    quote:

    2.) If this does not work: It looks like column "shipped" is of type "timestamp". Consult the docs how to construct a timestamp literal


    What docs are you referring to? I haven't been able to find anything related to the Pervasive form of SQL. Help?

  • 07-25-2005 8:53 AM In reply to

    Re: Pervasive SQL Syntax

    In the Library (http://www.pervasive.com/library), searching for "timestamp" gives a number of results including the following which gives the format:
    http://www.pervasive.com/library/docs/psql/900/prog_net/prog_net-07-2.html
    and a little further down:
    http://www.pervasive.com/library/docs/psql/794/sqlref/sqldtype3.html#63046 whicg defines the Timestamp type.
Page 1 of 1 (5 items)