SQl Command Syntax

[Mighty]

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:

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]

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]

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]

I have managed to sort out the previous command but this is the one that is causing me the problems now:

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]

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]

Where's the error in the new statement?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Mighty]

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]

Looking at the docs for PSQL 2000, it doesn't appear sub queries are supported in joins like you're doing.
From http://www.pervasive.com/library/docs/psql/794/sqlref/engref60.html:

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]

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]

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]

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]

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]

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