• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

Help with Pervasive query

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?
0
classnet
Asked:
classnet
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<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

0
 
classnetAuthor Commented:
Thanks!  Actually had to use 0 (long story) but this put me on the right track.
0
 
Bill BachPresidentCommented:
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)
RETURNS (
   Field1  Datatype1,
   Field2  Datatype2)
AS BEGIN
  DECLARE :stmt LONGVARCHAR;
  SET :stmt = 'SELECT * FROM a_particular_table';
  if(:lastdate is not null) then
    SET :stmt = :stmt + ' WHERE date_field <= ' + :lastdate
  end if;
  exec(:stmt);
END;

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.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now