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

Stored Procedures

Stored Procedures

(OP)
I have never used Stored Procedures before in any database so have no experience with them. My question is a fairly simple one - can I create a stored procedure with one parameter and that use that procedure in a join query with a table?

Apologies if this is really obvious to those Pervasive geniuses out there.

Mighty

RE: Stored Procedures

(OP)
Only on Pervasive Version 8.
Is it possible to use nested queries. Tried doing this using similar syntax to that used by MS Access but Pervasive didn't like it.

Mighty

RE: Stored Procedures

What kind of nested query?  Can you post the Access version, the PSQL version you tried, and the error you got?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Stored Procedures

(OP)
I'm trying to run a query similar to the following access query:

SELECT [Part Master].PRTNUM_01, [Part Master].PMDES1_01, IIF(isNull(Query1.QTYOH), 0, Query1.QTYOH) AS stockQty FROM [Part Master] LEFT JOIN (SELECT PRTNUM_06, SUM(QTYOH_06) AS QTYOH FROM [All Part Stock] WHERE NETFLG_06 = 'Y' GROUP BY [All Part Stock].PRTNUM_06) as Query1 ON [Part Master].PRTNUM_01 = [Query1].PRTNUM_06 WHERE [Part Master].COMCDE_01 = 'AB2' ORDER BY [Part Master].PRTNUM_01

The only change I attempted when trying to run this on Pervasive was to change the brackets around the table names to quotes. it just gives a syntax error after the join statement.

Also, there is an error in my last post. I am only running Pervasive SQL 2000i

Mighty

RE: Stored Procedures

(OP)
As stored procedures don't appear to be an option, I searched a forum for nested queries and from another post it doesn't appear that nested queries are supported in SQL 2000i. From a google search, one suggestion was to create a view for the subqueries and join those to tables.

I have no experience of using views and was wondering what anyones opinion was on that option.

Mighty

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