Pervasive 9.5 Stored Procedure with Date Parameter
Pervasive 9.5 Stored Procedure with Date Parameter
(OP)
Hi,
Trying to create stored procedure with a date parameter.
call sp_aginginvoice ('2008-10-15');
appears to run correctly but when I query the view created I get an expression error.
I have narrrowed error down to datediff :input clause
If I substitute :input for ('2008-10-15') in the code then the procedure works perfect but this defeats purpose of date parameter.
Here is my code:
Create Procedure SP_AgingInvoice (:stDate char (10));
BEGIN
Declare :Input date;
Set :Input = Cast (:stDate as date);
Create View AgingInvoice as
select invoice.invunique, invoice.nvno, invoice.nvdate,
DateDiff(day, invoice.nvdate, :input) as Aging
from Invoice, Account
where account.aunique = invoice.acct;
END;
Any help would be most appreciated!
Thanks
Chris
Trying to create stored procedure with a date parameter.
call sp_aginginvoice ('2008-10-15');
appears to run correctly but when I query the view created I get an expression error.
I have narrrowed error down to datediff :input clause
If I substitute :input for ('2008-10-15') in the code then the procedure works perfect but this defeats purpose of date parameter.
Here is my code:
Create Procedure SP_AgingInvoice (:stDate char (10));
BEGIN
Declare :Input date;
Set :Input = Cast (:stDate as date);
Create View AgingInvoice as
select invoice.invunique, invoice.nvno, invoice.nvdate,
DateDiff(day, invoice.nvdate, :input) as Aging
from Invoice, Account
where account.aunique = invoice.acct;
END;
Any help would be most appreciated!
Thanks
Chris
Talk To Other Members
RE: Pervasive 9.5 Stored Procedure with Date Parameter
First, what version of PSQL are you using? I tried with the latest v10.10 and I get an "invalid argument value" error trying to run the Stored Procedure.
Second, what happens if you change the parameter to a date to avoid the Cast? I still get the argument value error.
As a suggestion, just create the view directly. From a Pervasive stand point, there's no benefit to have a stored procedure create a view.
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com
RE: Pervasive 9.5 Stored Procedure with Date Parameter
I get the Invalid Argument Error when I use Date instead of Char or VarChar as the Stored Procedure Parameter
This is why I changed the Stored Procedure Parameter to text and then convert it to date in the Procedure
This appears to work fine, the stored procedure creates correctly and the firing the stored procedure creates the view but when I query the view (specifically the "aging" field) I get an "expression error" i.e. I cannot even open the view.
I have tried Cast and Convert with same issue.
I am doing this for a Customer Statement where the end user will enter the Statement Cutoff Date which will determine the Invoice Aging. For example, if cutoff date is the 1st of this Month, Invoice Aging Days would be Invoice Date - 1st of this month.
Based on date parameter the view will created and the report read off this view so the correct aging will show when the statement is printed.
If I substitute '2008-10-01' or curdate() in the stored procedure it works fine, just not with the parameter.
Any ideas would be most welcome!
Chris
RE: Pervasive 9.5 Stored Procedure with Date Parameter
It seems like DateDiff has the problem with variables. I hard coded the variable inside the procedure and it still failed. Under V10 no matter what I try with variables or data types, I can't get the view to create.
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com