Hi.

I am trying to write a simple query that lets a user type in a value
and have only the records that match that value show up.

In MS Access this is easily done.

SELECT [ORDERS].ORDER_NO, [ORDERS].ORDER_DATE
FROM [ORDERS]
WHERE ((([ORDERS].ORDER_DATE)=[Enter a Date]));

Does Pervasive 2000i allow anything like this? If so, what is the
syntax? If not, what are the steps involved to making this happen?

Thank you for any and all your help.
j.t.w

You will need to define the parameter.


"j.t.w" <j.t.w (AT) juno (DOT) com> wrote

You can do this with a stored procedure easily enough:

CREATE PROCEDURE OrdersByDate(IN :OrdDate DATE)
RETURNS(Order_No CHAR(10), Order_Date DATE)
AS BEGIN
SELECT ORDER_NO, ORDER_DATE
FROM ORDERS
WHERE ORDER_DATE = :OrdDate;
END;#

CALL OrdersByDate('2003-12-05')

Hope this helps...
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Chicago: March, 2004: See our web site for details!


"j.t.w" wrote:

Lapchien,

Thank you for responding.

Please excuse me, but I'm a newbie with Pervasive. How do I define the
paramenter? Could you tell me how this is done and/or point me in the
right direction?

Thanks,

j.t.w

Hi Bill.

Thank you for taking the time to reply to my post.

I am a very newbie to Pervasive and need all the help I can get. I
created a new stored procedure and pasted your entire sample code.


CREATE PROCEDURE OrdersByDate(IN :OrdDate DATE)
RETURNS(Order_No CHAR(10), Order_Date DATE)
AS BEGIN
SELECT ORDER_NO, ORDER_DATE
FROM ORDERS
WHERE ORDER_DATE = :OrdDate;
END;#

CALL OrdersByDate('2003-12-05')


Everything looks good so far. I was actually reading up on this via
the Pervasive help (though I couldn't quite grasp it).

Questions...
1) How do I run this stored procedure?
2) Does the "CALL OrdersByDate('2003-12-05')" actually go into the
stored procedure?
3) How does a user get to input a specific date?
4) What is the syntax to select a range of dates (i.e. 20031101 to
20031131)?

Sorry about all the additional questions. Please bear with me.
Anyway, thank you for your help. It is much appreciated.

j.t.w

"j.t.w" wrote:

You issue the CALL statement from whatever environment you are working
in. If you are native ODBC, then a statement of SQLExecDirect will
work. From VB/ADO, you can do a statement.ExecDirect function.

The date value is passed to the parameter in the stored proc. This
functions identically to a procedure in any programming language.

It is up to the application to generate a dialog box to request the
date, then push that value into the CALL statement.

To use a range of dates, you'll use something more like:

CREATE PROCEDURE OrdersByDate(IN :SDate DATE, IN :EDate DATE)
RETURNS(Order_No CHAR(10), Order_Date DATE)
AS BEGIN
SELECT ORDER_NO, ORDER_DATE
FROM ORDERS
WHERE ORDER_DATE BETWEEN :SDate AND :EDate;
END;#

CALL OrdersByDate('2003-12-05', '2003-12-08')
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Chicago: March, 2004: See our web site for details!