INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

RE: Pervasive 9.5 Stored Procedure with Date Parameter

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

RE: Pervasive 9.5 Stored Procedure with Date Parameter

(OP)
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

RE: Pervasive 9.5 Stored Procedure with Date Parameter

Quote:

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close