Stored Procedures

[Mighty]

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

 

[mirtheil]

You cannot use a stored procedure as part of a select.
What version of PSQL are you using? You might be able to use a function (

http://www.pervasive.com/library/docs/psql/950/sqlref/sqlref-04-19.html).

What are you trying to accomplish?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Mighty]

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

 

[mirtheil]

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

 

[Mighty]

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

 

[Mighty]

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