i cant get join to work with visual foxpro driver...
this is the query im running...
SELECT item.itemnumber, item.mfgr, item.description,
item.extendeddescription, Oinvcount.conditioncode, Oinvcount.qty,
totalsub.totalsubst, Oinvcount.qty + totalsub.totalsubst AS sellable
FROM item,
{ oj (SELECT itemid, conditioncode, COUNT(itemid) AS
qty
FROM inventory
WHERE (conditioncode <> 'REP')
AND (conditioncode <> 'TBE')
AND (conditioncode <> 'TEST')
AND (conditioncode <> 'RTV')
AND (location <> 'REPAIR')
AND (location <> 'RMA-REPAIR')
GROUP BY itemid, conditioncode) Oinvcount
LEFT OUTER JOIN
(SELECT itemid, conditioncode, SUM(qty) AS
totalsubst
FROM (SELECTitemsubst.itemid,
itemsubst.itemidsubst,
invcount.conditioncode,
invcount.qty
FROM itemsubst,
(SELECT itemid,
conditioncode,
COUNT(itemid) AS qty
FROM inventory inventory_1
WHERE
(conditioncode <> 'REP')
AND
(conditioncode <> 'TBE')
AND
(conditioncode <> 'TEST')
AND
(conditioncode <> 'RTV')
AND
(location <> 'REPAIR')
AND
(location <> 'RMA-REPAIR')
GROUP BY itemid,
conditioncode) invcount
WHERE
itemsubst.itemidsubst = invcount.itemid
GROUP BY
itemsubst.itemid,
itemsubst.itemidsubst,
invcount.conditioncode,
invcount.qty) subtbl_condqty
GROUP BY itemid, conditioncode) totalsub
ON
Oinvcount.itemid = totalsub.itemid }
WHERE item.itemid = Oinvcount.itemid
ORDER BY item.itemnumber
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
the query gives this ERROR messege...
TITLE: Microsoft Report Designer
------------------------------
Could not generate a list of fields for the query.
Check the query syntax, or click Refresh Fields on the query toolbar.
------------------------------
ADDITIONAL INFORMATION:
Command contains unrecognized phrase/keyword. (Microsoft OLE DB Provider for
Visual FoxPro)
<<<<<<<<<<<<<<<<<<<<<<<
SOOOOOOO......... I know im running one big nasty query, but it works well
if i wanted to return the 150 results from "totalsub"... however i want to
join "totalsub" to "Oinvcount" and return all of its 1500 results.
i get the same error running this query
SELECT inventory.conditioncode, item.itemid, inventory.location
FROM { oj inventory RIGHT OUTER JOIN
item ON inventory.itemid = item.itemid }
ORDER BY item.itemid
<<<<<<<<<<<<<<<<<<<<<<<<<<<<
if sombody knows how or if the JOIN cammand works with foxpro provider can
you please inform me? this is the last step i need to finish this query...
|