JJcerner

Hello,

Hoping for some help as I am getting a run-time error...I use a On Error Resume Next and a MsgBox Err and I get the error code = 3704. When I take this out I get an error code reading Incorrect Syntax near the keyword 'JOIN'. I have narrowed this down to the first Left Outer Join in the below sql. This will run via Sql Server Studio but when I put it into VBE and execute I get the error. Thinking the left outer has to be written in the .Net framework language? Just FYI this select statement takes ~5 minutes to run so any suggestions on improved performance are appreciated.

SQL=
sSQL(1) = "SELECT COALESCE(QTITM.ADJ_UNIT_PRI, 0.00) as 'Start Price', COALESCE(QTITM.NET_PRI, 0.00) as 'Net Price',COALESCE(COALESCE(QTITM.ADJ_UNIT_PRI, QTITM.NET_PRI) * QTITM.EXTD_QTY, 0.00) as 'Extended Start Price',COALESCE(CASE WHEN PRD.PRICE_TYPE_CD = 'Usage' THEN QTITM.NET_PRI ELSE QTITM.NET_PRI * QTITM.EXTD_QTY END, 0.00) as 'Extended Price', QTITM.EXTD_QTY as 'Extended Quantity',PRD.X_PROD_FAMILY as 'Product Family',PRD.X_PRODUCT_DESCR61 as 'Product Description',PRD.PART_NUM as 'Part #',PRD.X_MFG_PART_NUM as 'Mfg Part #',PRD.X_SUB_BUS_MODEL as 'Sub Business Model',CONLN.X_QUOTE_ITEM_ID as 'CONTRACT QUOTE ITEM ID',CVRD_PRNT_SUP_QTITM.ROW_ID as 'Parent Support', " & _
"CVRD_PRNT_SUP_PRD.X_SUB_BUS_MODEL as 'Sub Business Model',CVRD_PRNT_PP.X_PRICE_STRUCTURE as 'Pricing Structure',CVRD_PRNT_SUP_QTITM.NET_PRI as 'Net Price',CVRD_PRNT_SUP_QTITM.EXTD_QTY as 'Quantity',COALESCE(SVC.X_PROJ_NAME, '') as 'Svcs Project Name',COALESCE(SVC.X_BILL_TYPE, '') as 'Bill Type',COALESCE(SVC.X_DELIVERY_METHOD, '') as 'Delivery Method',COALESCE(ALOC.X_ADJ_ALLOC_CP, 0.00) as 'Adj Allocated CP',COALESCE(QTITMPRI.COST_PRI, 0.00) as 'Cost',QTITM.STAT_CD as 'Status' " & _
"FROM R3_CX_CONTRACT_LN as CONLN INNER JOIN R3_S_QUOTE_ITEM as QTITM on QTITM.ROW_ID = CONLN.X_QUOTE_ITEM_ID INNER JOIN R3_S_PROD_INT as PRD on PRD.ROW_ID = QTITM.PROD_ID INNER JOIN R3_CX_PP_CON_HD as PP on PP.ROW_ID = CONLN.X_PP_ID " & _
"LEFT OUTER JOIN R3_CX_ALLOCATIONS as ALOC on ALOC.X_QUOTE_ITEM_ID = QTITM.ROW_ID LEFT OUTER JOIN R3_CX_QUOTE_SVC as SVC on SVC.ROW_ID = QTITM.X_SVC_PROJ_ID LEFT OUTER JOIN R3_S_QUOTE_ITM_PRI as QTITMPRI on QTITMPRI.ROW_ID = QTITM.ROW_ID" & _
"LEFT OUTER JOIN R3_S_QUOTE_ITEM_XA as ATTR on ATTR.QUOTE_ITEM_ID = QTITM.ROW_ID and ATTR.ATTR_NAME = 'Term (in months)' LEFT OUTER JOIN R3_S_QUOTE_ITEM as PRNT_QTITM on PRNT_QTITM.ROW_ID = QTITM.PAR_SQ_ITEM_ID LEFT OUTER JOIN R3_CX_CONTRACT_LN as PRNT_CONLN on PRNT_QTITM.ROW_ID = PRNT_CONLN.X_QUOTE_ITEM_ID and PRNT_CONLN.X_CONTRACT_ID = '1-2MYA4DG'" & _
"LEFT OUTER JOIN R3_S_QUOTE_ITEM_XA as PRNT_ATTR on PRNT_ATTR.QUOTE_ITEM_ID = PRNT_QTITM.ROW_ID and PRNT_ATTR.ATTR_NAME = 'Term (in months)' LEFT OUTER JOIN R3_S_PROD_INT as PRNT_PRD on PRNT_PRD.ROW_ID = PRNT_QTITM.PROD_ID LEFT OUTER JOIN R3_CX_PP_CON_HD as PRNT_PP on PRNT_PP.ROW_ID = PRNT_CONLN.X_PP_ID LEFT OUTER JOIN R3_S_QUOTE_ITEM as CVRD_QTITM on CVRD_QTITM.ROW_ID = QTITM.SQ_ITEM_ID" & _
"LEFT OUTER JOIN R3_S_QUOTE_ITEM as CVRD_PRNT_QTITM on CVRD_PRNT_QTITM.ROW_ID = CVRD_QTITM.PAR_SQ_ITEM_ID LEFT OUTER JOIN R3_S_QUOTE_ITEM as CVRD_PRNT_SUP_QTITM on CVRD_PRNT_SUP_QTITM.SQ_ITEM_ID = CVRD_PRNT_QTITM.ROW_ID LEFT OUTER JOIN R3_CX_CONTRACT_LN as CVRD_PRNT_CONLN on CVRD_PRNT_SUP_QTITM.ROW_ID = CVRD_PRNT_CONLN.X_QUOTE_ITEM_ID and CVRD_PRNT_CONLN.X_CONTRACT_ID = '1-2MYA4DG' LEFT OUTER JOIN R3_S_QUOTE_ITEM_XA as CVRD_PRNT_ATTR on CVRD_PRNT_ATTR.QUOTE_ITEM_ID = CVRD_PRNT_SUP_QTITM.ROW_ID and CVRD_PRNT_ATTR.ATTR_NAME = 'Term (in months)' LEFT OUTER JOIN R3_S_PROD_INT as CVRD_PRNT_SUP_PRD on CVRD_PRNT_SUP_PRD.ROW_ID = CVRD_PRNT_SUP_QTITM.PROD_ID LEFT OUTER JOIN R3_CX_PP_CON_HD as CVRD_PRNT_PP on CVRD_PRNT_PP.ROW_ID = CVRD_PRNT_CONLN.X_PP_ID" & _
"WHERE QTITM.STAT_CD = 'Complete' AND CONLN.X_CONTRACT_ID = '1-1YSQNYF'"

Thanks for any tips, as this has been causing me to pull my hair out!

Orca44

Hi there, welcome to the forum.

That seems to be quite a complex query. I don't know why Excel would have a problem with the SQL since it runs in Management Studio, but I would suggest finding the exact problem. It might not be where the IDE indicates it to be.

The easiest way for me to do this would be to simplify the query by changing it to and then testing it in VBE again.

If that works, start removing the bottom half of the JOINs and keep removing it, until the query works, then you know the problem lies with the last part that you removed. If you could then post that simplified version again, it could make it easier to determine what the problem is.

As far a the performance is concerned, it is understandable that it takes a while since there is an lot of joins and the data size will also play a role. The query can be simplified somewhat, for instance the JOIN isn't doing anything thing so it can be removed. You could also look into changing some of the JOINs into additional WHERE clauses, since that seems to be all that they are doing.

JJcerner

Thanks a lot for the ideas. I am in the process of simplifying it as you note. Thinking to add some items in Access and see how they build out in the sql view. More to follow on this...thanks again.

JJcerner

After continuing to cycle through different attempts I came to find out in VBA on a Left Outer Join it likes (parentheses) around your ON clauses. So if I write my code like the following it worked:

LEFT OUTER JOIN R3_CX_ALLOCATIONS as ALOC on (ALOC.X_QUOTE_ITEM_ID = QTITM.ROW_ID)

Thanks for the help.

Orca44

That's good to know. Thanks for posting the solution.

Gruff

That is not the only difference between VBA queries and other environment queries. There are more. Also Access has unique differences as well.

One would think that MS would have made sure all of their data products could use the exact same query language. Unfortunateley this was not the case. Some of this can be laid at the feet of progress which is difficult to avoid.

andrern2000

Try replacing LEFT OUTER JOIN with LEFT JOIN. I read it from somewhere that it functions the same. Maybe ADODB doesn't recognise OUTER part.