Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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 from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Mighty (Programmer) (OP)
1 Mar 06 9:55
Hi Folks,

I had the following sql command which worked fine in MS Access and I tried to modify it to get it to work in Pervasive but I get a syntax error message. How can I modify this query to get it to work:

CODE

SELECT SUM(Query1.DUEQTY) AS TOTALWIP FROM "Part Master" INNER JOIN (SELECT PRTNUM_10, SUM(DUEQTY_10) AS DUEQTY FROM "Order Master" WHERE TYPE_10 = 'MF' AND STATUS_10 = '3' GROUP BY PRTNUM_10) AS Query1 ON "Part Master".PRTNUM_01 = Query1.PRTNUM_10 WHERE "Part Master".COMCDE_01 = 'AB1'

Mighty

mirtheil (Programmer)
1 Mar 06 10:26
What's the exact error message?  It'll give a clue as to where the syntax error is occurring.
What tool are you using to execute this query?  

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

Mighty (Programmer) (OP)
1 Mar 06 10:32
I am running the query in a .NET Web Application. But when I run it in PCC, I get the following error message:

ODBC Error: SQLSTATE = 37000, Native error code = 0
Syntax Error: SELECT SUM(Query1.DUEQTY) AS TOTALWIP FROM "Part Master" INNER JOIN (SELECT<< ??? >> PRTNUM_10, SUM(DUEQTY_10) AS DUEQTY FROM "Order Master" WHERE TYPE_10 = 'MF' AND STATUS_10 = '3' GROUP BY PRTNUM_10) AS Qu

Mighty

Mighty (Programmer) (OP)
1 Mar 06 11:32
I have managed to sort out the previous command but this is the one that is causing me the problems now:

CODE

SELECT "Part Master".PRTNUM_01, SUM(IFNULL(Query1.DUEQTY, 0)) AS TOTALWIP FROM "Part Master" LEFT JOIN (SELECT PRTNUM_10, SUM(DUEQTY_10) AS DUEQTY FROM "Order Master" WHERE TYPE_10 = 'MF' AND STATUS_10 = '3' GROUP BY PRTNUM_10) AS Query1 ON "Part Master".PRTNUM_01 = Query1.PRTNUM_10 WHERE "Part Master".COMCDE_01 = 'AB1' GROUP BY "Part Master".PRTNUM_01

The subquery is basically getting the total WIP quantity for each part number. I am then joining the part master to this table to get all the part numbers in the AB1 range and the total wip for each part. Any ideas how I can do this in Pervasive. As I said, the query works fine in Access.

Mighty

Mighty (Programmer) (OP)
2 Mar 06 3:23
Hi Mirtheil,

I know that you are the Pervasive King (will flattery get me anywhere). I just can't get this query to work. If I can't, I will have to modify my code to work around it somehow.

Thanks,

Nick

Mighty

mirtheil (Programmer)
2 Mar 06 10:29
Where's the error in the new statement?

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

Mighty (Programmer) (OP)
2 Mar 06 10:37
The error message I get when I try to execute the above statement is:

ODBC Error: SQLSTATE = 37000, Native error code = 0
Syntax Error: SELECT "Part Master".PRTNUM_01, SUM(IFNULL(Query1.DUEQTY, 0)) AS TOTALWIP FROM "Part Master" LEFT JOIN (SELECT<< ??? >> PRTNUM_10, SUM(DUEQTY_10) AS DUEQTY FROM "Order Master" WHERE TYPE_10 = 'MF' AND STATUS

Does Pervasive not like nested queries? I am running Pervasive SQL 2000i. It seems to fall over where the command tries to join the Part Master table to the subquery?

Mighty

mirtheil (Programmer)
2 Mar 06 10:45
Looking at the docs for PSQL 2000, it doesn't appear sub queries are supported in joins like you're doing.
From [url]http://www.pervasive.com/library/docs/psql/794/sqlref/engref60.html[/url]:

Quote:


Syntax

query-specification [ [ UNION [ ALL ] query-specification ]...
[ ORDER BY order-by-expression [ , order-by-expression ]... ]
 
order-by-expression ::= expression [ CASE | COLLATE collation-name ] [ ASC | DESC ]
 
query-specification ::=      ( query-specification )

| SELECT [ ALL | DISTINCT ] select-list
  FROM table-reference [ , table-reference ]...
  [ WHERE search-condition ]
  [ GROUP BY expression [ , expression ]...

[ HAVING search-condition ] ]



 
select-list ::= * | select-item [ , select-item ]...
 
select-item ::= expression [ [ AS ] alias-name ] | table-name . *
 
table-reference ::= { OJ outer-join-definition }

| table-name [ [ AS ] alias-name ]
| join-definition
| ( join-definition )


 
join-definition ::= table-reference INNER JOIN table-reference ON search-condition

| table-reference CROSS JOIN table-reference
| outer-join-definition


 
outer-join-definition ::= table-reference outer-join-type JOIN table-reference
ON search-condition
 
outer-join-type ::= LEFT [ OUTER ] | RIGHT [ OUTER ] | FULL [ OUTER ]
 
search-condition ::= search-condition AND search-condition

| search-condition OR search-condition
| NOT search-condition
| ( search-condition )
| predicate

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

Mighty (Programmer) (OP)
2 Mar 06 10:52
I was afraid that was going to be the case. I had a look at that in the Pervasive document but didn't really understand it to be honest.

Mighty

JordanCN (IS/IT--Management)
12 Apr 06 22:50
Hey Mighty,

PRTNUM_01, PRTNUM_10, COMCDE_01.  That's a MAX Users if I ever seen one.

First lets start with:

SUM(Query1.DUEQTY) AS TOTALWIP FROM "Part Master"

This does not make sense, even for MAX.  Your summing what looks like the DueQTY calculation from a query, but saying it is from the "Part Master" and there is no field like that in the Part Master.  I believe you are looking for the Order Master table, Correct?

What is the end goal.  I might have something for you.
Mighty (Programmer) (OP)
13 Apr 06 2:54
Hi JordanCN,

Yes I am most definitely a MAX user - for my troubles. If you are also a MAX user then you will understand what I am trying to do. I am trying to get a list of the totalWIP for all parts in the Part Master table with a certain commodity code. If I just join part master to order master directly I won't get all the parts - only those that currently have WIP.

The way that I have it coded above works fine in Access.

In the end I just had to join order master and part master and use the .NET code to manipulate the data the way I needed it.

Mighty

JordanCN (IS/IT--Management)
24 Apr 06 11:55
Mighty,

I don't see this working the way you want with a pervasive SQL query or procedure so if you have this working in Access I would stick with it.

I thought I was also going to change all my Access DB functions around just so I could use all my functions with Word, Excel, Outlook, etc. and I thought that Pervasive language would just be the best tool.  Unfortunately, since MAX is such a joke this turned out to be a major hassle.  The naming conventions of the tables having spaces in them caused problems, tables like the PO Notes which also contain router notes and other data and the Non-Inventory PO data being mixed with other data and so on.... You know what I mean.

I have opted just to keep an Access MDB file with all my queries in them and just call them in my functions from other apps by using the QueryDef and OpenDatabase methods of DAO from VB.  This way I don't have to learn Pervasive's language.

Learning Pervasive ultimately might be faster, but the versitility just is not there for what we need to do.
Mighty (Programmer) (OP)
24 Apr 06 11:57
Couldn't agree with you more - MAX is a pain in the ass!! I just changed my code around using a different query and then getting my code to analyse the data. Bit longwinded but it gets the job done.

Mighty

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!

Back To Forum

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