Syntax error in FROM clause

[gmore24]

Hello,

I get a Syntax error in FROM clause message when running that query
below... I am sure there is a square braquet or a parenthese missing,
but I can't figure it out.... any help will be appreciated.

Thanks
****************************************

SELECT W.*

FROM tbl_ORA_ITEMS_ORDERS_ALL W INNER JOIN

[
SELECT L1.ITEM_ID, L1.SHIP_TO_ORG_ID, L1.PO_HEADER_ID, L1.RELEASE_NUM,
L1.LINE_REAL, Max(L1.SHIPMENT_NUM) AS MAXSHPN
FROM
[
SELECT A.*
FROM
[
SELECT ITEM_ID
, PO_HEADER_ID
, RELEASE_NUM
, SHIP_TO_ORG_ID
, Max(LINE_REAL) AS MAX_LINE_REAL
FROM tbl_ORA_ITEMS_ORDERS_ALL
GROUP BY ITEM_ID, PO_HEADER_ID
, RELEASE_NUM, SHIP_TO_ORG_ID
]. AS Q_MAXLINE INNER JOIN

(
[
SELECT ITEM_ID
, SHIP_TO_ORG_ID
, Max(S_CREATE_DATE) AS MAX_SHP_DATE
FROM tbl_ORA_ITEMS_ORDERS_ALL
GROUP BY ITEM_ID, SHIP_TO_ORG_ID
]. AS Q_MAXSHPD INNER JOIN tbl_ORA_ITEMS_ORDERS_ALL AS A

ON
(Q_MAXSHPD.ITEM_ID = A.ITEM_ID)
AND (Q_MAXSHPD.SHIP_TO_ORG_ID = A.SHIP_TO_ORG_ID)
AND (Q_MAXSHPD.MAX_SHP_DATE = A.S_CREATE_DATE)

)
ON (Q_MAXLINE.ITEM_ID = A.ITEM_ID)
AND (Q_MAXLINE.PO_HEADER_ID = A.PO_HEADER_ID)
AND (Q_MAXLINE.RELEASE_NUM = A.RELEASE_NUM)
AND (Q_MAXLINE.SHIP_TO_ORG_ID = A.SHIP_TO_ORG_ID)
AND (Q_MAXLINE.MAX_LINE_REAL = A.LINE_REAL)
WHERE (((A.ITEM_ID)=191422))
]. AS L1
GROUP BY L1.ITEM_ID, L1.SHIP_TO_ORG_ID, L1.PO_HEADER_ID,
L1.RELEASE_NUM, L1.LINE_REAL

] V

ON (W.SHIPMENT_NUM = V.MAXSHPN)
AND (W.LINE_REAL = V.LINE_REAL)
AND (W.RELEASE_NUM = V.RELEASE_NUM)
AND (W.ITEM_ID = V.ITEM_ID)
AND (W.SHIP_TO_ORG_ID = V.SHIP_TO_ORG_ID)
AND (W.PO_HEADER_ID = V.PO_HEADER_ID);

 

[John Spencer (MVP)]

DId not throughly check but did note that you have are probably missing a dot
As when assigning the name to a subquery.

]. AS V

SELECT W.*

FROM tbl_ORA_ITEMS_ORDERS_ALL W INNER JOIN

[
SELECT L1.ITEM_ID, L1.SHIP_TO_ORG_ID, L1.PO_HEADER_ID, L1.RELEASE_NUM,
L1.LINE_REAL, Max(L1.SHIPMENT_NUM) AS MAXSHPN
FROM
[
SELECT A.*
FROM
[
SELECT ITEM_ID
, PO_HEADER_ID
, RELEASE_NUM
, SHIP_TO_ORG_ID
, Max(LINE_REAL) AS MAX_LINE_REAL
FROM tbl_ORA_ITEMS_ORDERS_ALL
GROUP BY ITEM_ID, PO_HEADER_ID
, RELEASE_NUM, SHIP_TO_ORG_ID
]. AS Q_MAXLINE INNER JOIN

(
[
SELECT ITEM_ID
, SHIP_TO_ORG_ID
, Max(S_CREATE_DATE) AS MAX_SHP_DATE
FROM tbl_ORA_ITEMS_ORDERS_ALL
GROUP BY ITEM_ID, SHIP_TO_ORG_ID
]. AS Q_MAXSHPD INNER JOIN tbl_ORA_ITEMS_ORDERS_ALL AS A

ON
(Q_MAXSHPD.ITEM_ID = A.ITEM_ID)
AND (Q_MAXSHPD.SHIP_TO_ORG_ID = A.SHIP_TO_ORG_ID)
AND (Q_MAXSHPD.MAX_SHP_DATE = A.S_CREATE_DATE)

)
ON (Q_MAXLINE.ITEM_ID = A.ITEM_ID)
AND (Q_MAXLINE.PO_HEADER_ID = A.PO_HEADER_ID)
AND (Q_MAXLINE.RELEASE_NUM = A.RELEASE_NUM)
AND (Q_MAXLINE.SHIP_TO_ORG_ID = A.SHIP_TO_ORG_ID)
AND (Q_MAXLINE.MAX_LINE_REAL = A.LINE_REAL)
WHERE (((A.ITEM_ID)=191422))
]. AS L1
GROUP BY L1.ITEM_ID, L1.SHIP_TO_ORG_ID, L1.PO_HEADER_ID,
L1.RELEASE_NUM, L1.LINE_REAL

]. AS V <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

ON (W.SHIPMENT_NUM = V.MAXSHPN)
AND (W.LINE_REAL = V.LINE_REAL)
AND (W.RELEASE_NUM = V.RELEASE_NUM)
AND (W.ITEM_ID = V.ITEM_ID)
AND (W.SHIP_TO_ORG_ID = V.SHIP_TO_ORG_ID)
AND (W.PO_HEADER_ID = V.PO_HEADER_ID);

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello,

I get a Syntax error in FROM clause message when running that query
below... I am sure there is a square braquet or a parenthese missing,
but I can't figure it out.... any help will be appreciated.

Thanks
****************************************

SELECT W.*

FROM tbl_ORA_ITEMS_ORDERS_ALL W INNER JOIN

[
SELECT L1.ITEM_ID, L1.SHIP_TO_ORG_ID, L1.PO_HEADER_ID, L1.RELEASE_NUM,
L1.LINE_REAL, Max(L1.SHIPMENT_NUM) AS MAXSHPN
FROM
[
SELECT A.*
FROM
[
SELECT ITEM_ID
, PO_HEADER_ID
, RELEASE_NUM
, SHIP_TO_ORG_ID
, Max(LINE_REAL) AS MAX_LINE_REAL
FROM tbl_ORA_ITEMS_ORDERS_ALL
GROUP BY ITEM_ID, PO_HEADER_ID
, RELEASE_NUM, SHIP_TO_ORG_ID
]. AS Q_MAXLINE INNER JOIN

(
[
SELECT ITEM_ID
, SHIP_TO_ORG_ID
, Max(S_CREATE_DATE) AS MAX_SHP_DATE
FROM tbl_ORA_ITEMS_ORDERS_ALL
GROUP BY ITEM_ID, SHIP_TO_ORG_ID
]. AS Q_MAXSHPD INNER JOIN tbl_ORA_ITEMS_ORDERS_ALL AS A

ON
(Q_MAXSHPD.ITEM_ID = A.ITEM_ID)
AND (Q_MAXSHPD.SHIP_TO_ORG_ID = A.SHIP_TO_ORG_ID)
AND (Q_MAXSHPD.MAX_SHP_DATE = A.S_CREATE_DATE)

)
ON (Q_MAXLINE.ITEM_ID = A.ITEM_ID)
AND (Q_MAXLINE.PO_HEADER_ID = A.PO_HEADER_ID)
AND (Q_MAXLINE.RELEASE_NUM = A.RELEASE_NUM)
AND (Q_MAXLINE.SHIP_TO_ORG_ID = A.SHIP_TO_ORG_ID)
AND (Q_MAXLINE.MAX_LINE_REAL = A.LINE_REAL)
WHERE (((A.ITEM_ID)=191422))
]. AS L1
GROUP BY L1.ITEM_ID, L1.SHIP_TO_ORG_ID, L1.PO_HEADER_ID,
L1.RELEASE_NUM, L1.LINE_REAL

] V

ON (W.SHIPMENT_NUM = V.MAXSHPN)
AND (W.LINE_REAL = V.LINE_REAL)
AND (W.RELEASE_NUM = V.RELEASE_NUM)
AND (W.ITEM_ID = V.ITEM_ID)
AND (W.SHIP_TO_ORG_ID = V.SHIP_TO_ORG_ID)
AND (W.PO_HEADER_ID = V.PO_HEADER_ID);

 

[gmore24]

DId not throughly check but did note that you have are probably missing adot
As when assigning the name to a subquery.

    ]. AS V

Hello, you are right, but I had tried with and without it and I had
the same message... if you notice something else... let me know

Thanks,
gmore

 

[gmore24]

Hey! I found out my problem. It's parentheses vs square braquets...
Access changes my code to braquets and when you wanna make a change
once your sql query is made, I get that error, so I had to keep a copy
of my original query and paste it over each time I need to modify it.

gmore