0

I have a MS access query which I am running in my c sharp application, I am able to run the query fine using SSMS (I know this isn't an access sql but its all I can use) and when I import it into my c sharp application I get an incorrect syntax error. (My c sharp application reads from access dbf files) Here is the full sql below:

SELECT ([T2_BRA].[REF] + [F7]) AS NewStyle,
            Sum(T2_BRA.Q11) AS QTY1, Sum(T2_BRA.Q12) AS QTY2, 
                Sum(T2_BRA.Q13) AS QTY3, Sum(T2_BRA.Q14) AS QTY4, Sum(T2_BRA.Q15) AS QTY5, Sum(T2_BRA.Q16) AS QTY6, Sum(T2_BRA.Q17) AS QTY7, Sum(T2_BRA.Q18) AS QTY8, 
                    Sum(T2_BRA.Q19) AS QTY9, Sum(T2_BRA.Q20) AS QTY10, Sum(T2_BRA.Q21) AS QTY11, Sum(T2_BRA.Q22) AS QTY12, Sum(T2_BRA.Q23) AS QTY13, T2_HEAD.REF,
                        Sum(T2_BRA.LY11) AS LY1, Sum(T2_BRA.LY12) AS LY2, Sum(T2_BRA.LY13) AS LY3, Sum(T2_BRA.LY14) AS LY4, Sum(T2_BRA.LY15) AS LY5, 
                            Sum(T2_BRA.LY16) AS LY6, Sum(T2_BRA.LY17) AS LY7, Sum(T2_BRA.LY18) AS LY8, Sum(T2_BRA.LY19) AS LY9, Sum(T2_BRA.LY20) AS LY10,
                                Sum(T2_BRA.LY21) AS LY11, Sum(T2_BRA.LY22) AS LY12, Sum(T2_BRA.LY23) AS LY13, T2_BRA.BRANCH, T2_HEAD.LASTDELV, T2_EAN.EAN_CODE, T2_SIZES.S01 AS S1, 
                                T2_SIZES.S02 AS S2, 
                                T2_SIZES.S03 AS S3, 
                                T2_SIZES.S04 AS S4, 
                                T2_SIZES.S05 AS S5, 
                                T2_SIZES.S06 AS S6, 
                                T2_SIZES.S07 AS S7, 
                                T2_SIZES.S08 AS S8, 
                                T2_SIZES.S09 AS S9, 
                                T2_SIZES.S10 AS S10, 
                                T2_SIZES.S11 AS S11, 
                                T2_SIZES.S12 AS S12, 
                                T2_SIZES.S13 AS S13 
                                    FROM ((((((T2_BRA INNER JOIN T2_HEAD ON T2_BRA.REF = T2_HEAD.REF)) INNER JOIN T2_SIZES ON T2_HEAD.SIZERANGE = T2_SIZES.SIZERANGE) INNER JOIN 
                                    (SELECT Right(T2_LOOK.[KEY],3) AS NewCol, T2_LOOK.F1 AS MasterColour, Left(T2_LOOK.[KEY],3) AS Col, T2_LOOK.F7
                                FROM T2_LOOK
                                WHERE (Left(T2_LOOK.[KEY],3))='COL') as Colour ON T2_BRA.COLOUR = Colour.NewCol) LEFT JOIN T2_EAN ON T2_EAN.T2T_CODE LIKE (SELECT ('#' + ([T2_BRA].[REF] + [F7]) + '#'))))
                                    WHERE [T2_BRA].[REF] = '010403' AND T2_BRA.BRANCH in ('A','G')
                                    GROUP BY ([T2_BRA].[REF] + [F7]),T2_HEAD.REF, T2_BRA.BRANCH, T2_HEAD.LASTDELV, T2_EAN.EAN_CODE, T2_SIZES.S01, 
T2_SIZES.S02, T2_SIZES.S03, T2_SIZES.S04, T2_SIZES.S05, T2_SIZES.S06, T2_SIZES.S07, T2_SIZES.S08, T2_SIZES.S09, T2_SIZES.S10, T2_SIZES.S11, T2_SIZES.S12, T2_SIZES.S13

The line I am getting the syntax error is:

LEFT JOIN T2_EAN ON T2_EAN.T2T_CODE LIKE (SELECT ('#' + ([T2_BRA].[REF] + [F7]) + '#')

Any help would be great! :)

Improve this question
9
  • Your brackets seems out of line and the SELECT clause you've pointed out doesn't have a FROM clause to go with it. I can't put my finger exactly what's wrong. I'd consider changing the + to an & as that is how Access prefers it (1+1=2 while 1 & 1 = 11). Not sure though as you're using SSMS & C#. Commented Apr 30, 2018 at 15:58
  • Is [T2_BRA].[REF] a date? SQL uses "%" for the like query. For example, if I wanted to look for the word "data" anywhere in the string, I would use: WHERE [Column] LIKE '%data%' Commented Apr 30, 2018 at 16:00
  • As an example the first join in your FROM clause has these brackets ((T2_BRA INNER JOIN T2_HEAD ON T2_BRA.REF = T2_HEAD.REF)) - there's no need to place two sets of brackets around the join, just one will do in this instance. Commented Apr 30, 2018 at 16:02
  • 2
    @DarrenBartrup-Cook ... that depends. In ODBC mode, Access uses ANSI-92's % but in GUI .exe mode uses ANSI-89's * wildcard operator. See more info Commented Apr 30, 2018 at 16:13
  • 1
    # is a valid wildcard in MS Access and is used, combined with the LIKE operator, to prompt the user for a pattern to search (Doc here). However I don't think that doing a LIKE <nested query result> is allowed and this is probably the root cause here. All in one, it is certainly completely false to prompt the user for a pattern to search on a nested query result. Commented Apr 30, 2018 at 16:19

2 Answers 2

Reset to default
1

Your problem JOIN clause has some issues for the MS Access dialect:

  1. SELECT that uses a column and table reference must have FROM source;
  2. String concatenation does not use + but & operator;
  3. LIKE expressions can be used in ON clauses but the comparison will be row by row (not searching values across all rows of joining table as possibly intended).

Correcting above still imposes a challenge since you are attempting to join a table by the LIKE expression in a LEFT JOIN relationship.

Consider first comma-separating your table, T2_EAN, which equates to a cross join then add a WHERE clause running an EXISTS subquery. Doing so, WHERE becomes the implicit join and T2_EAN column will point to field in main query. Do be aware other tables in query must use INNER JOIN for this comma-separated table. And adjust parentheses with removal of LEFT JOIN. 

FROM T2_EAN, (((((
...
WHERE [T2_BRA].[REF] = '010403' AND T2_BRA.BRANCH in ('A','G') 
  AND EXISTS 
  (SELECT 1 FROM [T2_BRA] t 
   WHERE T2_EAN.T2T_CODE LIKE ('%' & (t.[REF] & t.[F7]) & '%')

Now, the challenge here is the WHERE will correspond to an INNER JOIN and not LEFT JOIN. To overcome this, consider adding a UNION (not UNION ALL) query exactly the same as above but without the EXISTS subquery. This will then return records that did not meet LIKE criteria and UNION will leave out duplicates. See LEFT JOIN Equivalent here. Be sure to add a NULL to SELECT wherever T2_EAN column was referenced:

SELECT ... T2_HEAD.LASTDELV, T2_EAN.EAN_CODE, T2_SIZES.S01 AS S1 ...

UNION

SELECT ... T2_HEAD.LASTDELV, NULL AS EAN_CODE, T2_SIZES.S01 AS S1 ...

Do note: performance is not guaranteed with this adjustment. Further considerations include:

  1. Once query compiles and runs, be sure to save this large query or view as a stored object in the MS Access database and not as a scripted C# string query. Even if you do not have MS Access GUI .exe, you can save queries via code using MS Access' querydefs object with VBA (i.e., Excel VBA) or COM-interface with C# or any other language that supports COM like open-source Python, PHP, R.

    Then have C# app simply retrieve the view for its purposes: SELECT * FROM mySavedQuery. Stored queries tend to be more efficient especially for many joins and complex queries than coded queries since the Access engine saves best execution plan and caches stats.

  2. Remove the need of LIKE by saving matching values without extraneous other characters so = can be used as I believe MS Access's LIKE will not use indexes in query plans.

  3. Upsize your Access database to SQL Server for more sophisticated handling with the T-SQL dialect. SQL Server has easy facilities in SSMS to import Access .mdb/.accdb files.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You LEFT OUTER JOIN's ON clause makes no sense:

LEFT JOIN T2_EAN ON T2_EAN.T2T_CODE LIKE (SELECT ('#' + ([T2_BRA].[REF] + [F7]) + '#')

You need to join T2_EAN to your values in ALREADY PRESENT T2_BRA table in your FROM clause. By sticking T2_BRA into a subquery here you are bringing the table in twice, which is nonsense. It's also not allowed to use a subquery inside a LIKE condition.

If it were allowed and did make sense, you would end up with a cartesian product between all the intermediate result set from those inner joins and your left outer join'd table, which is almost definitely not what you are after.

Instead (probably something like):

LEFT JOIN T2_EAN ON T2_EAN.T2T_CODE LIKE '#' + [T2_BRA].[REF] + [F7] + '#'

This is now saying "Left outer join t2_ean to T2_Bra where the T2T_Code matches the concatenation of <any one digit> + T2_Bra.Ref + F7 + <any one digit>" Without knowing your data, I cant' vouch for that being the thing you want, but it feels like the closest interpretation when reverse engineering your incorrect query.

You mention in a comment "I have tried using all the wildcard symbols *, # and ?" Don't just try wildcard symbols hoping something will work. They each do something VERY different. Use the one that you need for you situation. Decent explanation of the three wildcards that work with the LIKE operator in access here. You may want to switch to the asterisk while debugging (since it's the most wide open of the wild cards) and then once you are getting reasonable results, use the much tighter # (match only one digit) operator.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.