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?
Sheldon LivingstonConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sheldon LivingstonConsultantAuthor Commented:
Thanks!  Actually had to use 0 (long story) but this put me on the right track.
0
Bill BachPresident and Btrieve GuruCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.