SQL / pervasive 2

[johnny45]

Trying to query Pervasive 9 (accpac5.5) with the following query I built in access (which is linked to accpac pervasive tables):

SELECT ICITMV.VENDNUM, ICITMV.VENDNAME, ICILOC.ITEMNO, ICILOC.QTYONHAND, ICILOC.QTYONORDER, ICILOC.QTYSALORDR, ICILOC.QTYONHAND-ICILOC.QTYSALORDR AS NET_AVAIL, NET_AVAIL/(ICSTATI.SALESQTY/365*30) AS ['MONTHS LEFT'], ICSTATI.SALESQTY, ICSTATI.SALESQTY/365 AS DDD
FROM ICSTATI INNER JOIN (ICILOC INNER JOIN ICITMV ON ICILOC.ITEMNO = ICITMV.ITEMNO) ON ICSTATI.ITEMNO = ICITMV.ITEMNO
WHERE (((ICITMV.VENDNUM)='22280') AND ((ICSTATI.YEAR)='2008') AND ((ICSTATI.PERIOD)=10));

The SQL works great in access but crashes when I try to use in VBA excel.
I know that VBA/excel does NOT like the "[]"...can anyone help me get this SQl to excecute through Excel ?

 

[mirtheil]

The following may work:

SELECT ICITMV.VENDNUM, ICITMV.VENDNAME, ICILOC.ITEMNO, ICILOC.QTYONHAND, ICILOC.QTYONORDER, ICILOC.QTYSALORDR, ICILOC.QTYONHAND-ICILOC.QTYSALORDR AS NET_AVAIL, NET_AVAIL/(ICSTATI.SALESQTY/365*30) AS "MONTHS LEFT", ICSTATI.SALESQTY, ICSTATI.SALESQTY/365 AS DDD
FROM ICSTATI INNER JOIN (ICILOC INNER JOIN ICITMV ON ICILOC.ITEMNO = ICITMV.ITEMNO) ON ICSTATI.ITEMNO = ICITMV.ITEMNO
WHERE (((ICITMV.VENDNUM)='22280') AND ((ICSTATI.YEAR)='2008') AND ((ICSTATI.PERIOD)=10))

Pervasive uses double quotes for aliases rather than the "['...']"


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[johnny45]

Thanks Mirtheil
No luck with the above

 

[johnny45]

I get this error:
LNA][Pervasive][ODBC Engine Interface]Error in expression: NET_AVAIL / ( ICSTATI . SALESQTY / 365 * 30 )

 

[Jay Converse]

NET_AVAIL isn't a field, it's an alias. Use (ICILOC.QTYONHAND-ICILOC.QTYSALORDR) instead.

 

[mirtheil]

Try:

SELECT ICITMV.VENDNUM, ICITMV.VENDNAME, ICILOC.ITEMNO, ICILOC.QTYONHAND, ICILOC.QTYONORDER, ICILOC.QTYSALORDR, ICILOC.QTYONHAND-ICILOC.QTYSALORDR AS NET_AVAIL, ICILOC.QTYSALORDR, (ICILOC.QTYONHAND-ICILOC.QTYSALORDR)/(ICSTATI.SALESQTY/365*30) AS "MONTHS LEFT", ICSTATI.SALESQTY, ICSTATI.SALESQTY/365 AS DDD
FROM ICSTATI INNER JOIN (ICILOC INNER JOIN ICITMV ON ICILOC.ITEMNO = ICITMV.ITEMNO) ON ICSTATI.ITEMNO = ICITMV.ITEMNO
WHERE (((ICITMV.VENDNUM)='22280') AND ((ICSTATI.YEAR)='2008') AND ((ICSTATI.PERIOD)=10))

I guess Pervasive doesn't like the "NET_AVAIL" alias

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[johnny45]

Thank You !


I tried changing that several times to no avail.....

The above works....:
60 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 72977/ms]

1 minute and 18s...this seems very long to me....is the tim eto excecute the query acceptable ?
can I speed it up ?

 

[mirtheil]

I had posted this in your other thread (

http://www.tek-tips.com/viewthread.cfm?qid=1526475&page=1):

Not knowing the database, I can't say if it's "bad" SQL or not. It's possible that the indexes are not being used.
Specifically, you'll want to check on:
ICITMV.VENDNUM
ICSTATI.YEAR
ICILOC.ITEMNO
ICITMV.ITEMNO

Make sure those are indexed.
You could also use the Query Plan and Query Plan Viewer to see how the engine is processing the query.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Jay Converse]

ISTATI.YEAR and ICITMV.VENDNUM are not indexed, so you might have luck throwing index on them in the Pervasive control center. Note that you'll lose the indexes on a Dump/Load, and it may interfere with upgrades. The best permanent solution is changing over to MSSQL, that's many of our clients are doing.

 

[johnny45]

Thank you tuba2007 & mirtheil for the help and clarification.....

Cheers,
Johnny

 

[Tianjin]

Try use "WHERE" instead of "INNER JOIN", this should speed up your query.