Help with Pervasive query

Posted on 2014-07-18
Last Modified: 2014-07-18
I wish to query our accounting system that is stored in a Pervasive 11 database.

I wish the query to pull all the records of a particular table UNLESS the user provides a date.  Then I want all records up to that date to be returned.

Any pseudo code?
Question by:classnet
    LVL 65

    Accepted Solution

    <SQL air code.  I do my own stunts too>
    SELECT *
    FROM a_particular_table
    WHERE some_date <= @date_parameter OR @date_parameter IS NULL

    Open in new window


    Author Closing Comment

    Thanks!  Actually had to use 0 (long story) but this put me on the right track.
    LVL 28

    Expert Comment

    by:Bill Bach
    When you post with a SQL Server topic, you definitely get back SQL Server results.  However, Pervasive PSQL does not support the @ for a parameter.  This gives you really two options:
    1) Issue either one query (with the date restriction) or the other (without the restriction).  This would be done in code with a simple IF statement.  Since you don't mention your front end, a more specific answer is not possible.
    2) Use a stored procedure, where you can supply a parameter or not.  Again, the exact syntax cannot be discerned due to the lack of actual info, but here is an example to get you started:
    CREATE PROCEDURE GetDataByDate (in :lastdate VARCHAR(20)=NULL)
       Field1  Datatype1,
       Field2  Datatype2)
      SET :stmt = 'SELECT * FROM a_particular_table';
      if(:lastdate is not null) then
        SET :stmt = :stmt + ' WHERE date_field <= ' + :lastdate
      end if;

    Note, though, that the workings of the WHERE clause depend on your database, too.  Is the date field a true PSQL Date?  Is it a Julian date?  Is it a string date?  What about your input parameter?  You may have to convert the parameter to match the target data type and format.  See the docs (in the PCC under Help/Documentation) on using the CONVERT() function.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now