Pervasive 9.5 Stored Procedure with Date Parameter

[chrismcaniff]

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

 

[mirtheil]

A couple of questions and a suggestion.
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

 

[chrismcaniff]

I am using Version 9.5

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

 

[mirtheil]

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.

Why not just create the view instead of having the stored procedure do it? It would be one less step in your process.

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