Hello!

I've beat my head crazy trying to figure out what the heck is wrong with this query. It is not my query, but one from a packaged software I need to customize. I've created a link to the sql db's in an access db (linked to all sql tables) and trying and trying to run this and no matter what i do, it seems to give the same error.

SELECT     dbo.CLIENT_TRANS.CLIENT_ID, dbo.CLIENT_TRANS.TRANS_CD_ID, dbo.TRANS_CD.TRANS_CD_DESC, dbo.CLIENT_TRANS.ITEM_ID, 
                      dbo.CLIENT_TRANS.ACT_TRANS_TS, dbo.CLIENT_TRANS.ACT_TRANS_DESC, dbo.CLIENT_TRANS.ACT_TRANS_AMT, dbo.CLIENT_TRANS.USER_ID, 
                      dbo.CLIENT.FIRST_NAME, dbo.CLIENT.LAST_NAME, dbo.CLIENT.ACCOUNT, dbo.CLIENT.FILTER_CODE_ID, dbo.CLIENT_TRANS.PAYOUT_ID, 
                      dbo.ITEM.ITEM_NUM, dbo.ITEM.ITEM_NAME, dbo.TRANS_CD.NEGATIVE_IND, dbo.TRANS_CD.PAYABLE_IND, dbo.PHONE.PRIMARY_IND, 
                      dbo.PHONE.PHONE_NUMBER, dbo.PHONE.PHONE_DESC, CASE WHEN dbo.CLIENT_TRANS.TRANS_CD_ID IN (1, 2, 5, 11) 
                      THEN dbo.CLIENT_TRANS.ACT_TRANS_TS ELSE getdate() - 365 * 5 END AS ITEMSOLDDATE, dbo.CLIENT_TRANS.TRANS_CD_ID AS Expr1, 
                      dbo.CLIENT_TRANS.HOLD_IND, dbo.TRANS_CD.PAYABLE_IND AS Payable
FROM         dbo.PHONE RIGHT OUTER JOIN
                      dbo.CLIENT ON dbo.PHONE.CLIENT_ID = dbo.CLIENT.CLIENT_ID RIGHT OUTER JOIN
                      dbo.CLIENT_TRANS ON dbo.CLIENT.CLIENT_ID = dbo.CLIENT_TRANS.CLIENT_ID LEFT OUTER JOIN
                      dbo.TRANS_CD ON dbo.CLIENT_TRANS.TRANS_CD_ID = dbo.TRANS_CD.TRANS_CD_ID LEFT OUTER JOIN
                      dbo.ITEM ON dbo.CLIENT_TRANS.ITEM_ID = dbo.ITEM.ITEM_ID
WHERE     (dbo.CLIENT_TRANS.PAYOUT_ID IS NULL) AND (dbo.PHONE.PRIMARY_IND = 1) AND (dbo.CLIENT_TRANS.TRANS_CD_ID <> 13) AND 
                      (dbo.CLIENT_TRANS.TRANS_CD_ID <> 8) AND (dbo.CLIENT_TRANS.TRANS_CD_ID <> 15) AND (dbo.CLIENT_TRANS.TRANS_CD_ID <> 9)

The error message says "syntax error (missing operator) in query expression 'CASE WHEN dbo.CLIENT_TRANS.TRANS_CD_ID IN (1, 2, 5, 11) THEN dbo.CLIENT_TRANS.ACT_TRANS_TS ELSE getdate() - 365 * 5 END'.

I am running this query via access query where I paste the exact sql in the "sql" window.

I have researched and spent countless hours trying to figure out what is wrong with the statement... the entire query is shown above. If anyone can tell me what went wrong, I'd really be grateful. Thank you so very much, Laura

Recommended Answers

All 8 Replies

Your query is very complicated but where do you run this query?

the data is in sql, i'm trying to run the query that will run inside the program it was written for, but doesn't do exactly what we need. So I found the source code for the query and copied that exactly, pasted it in the access database i created that links to the sql data. Pasted the exact code that runs into the access sql query window and get that error when i try to run it.

Thank you so much for reading my problem. :)

ahhhhh, maybe if I find a query analyzer and run it, it would work then. I didn't realize access sql had limitations.. I did read the link.... and am trying to figure out how to modify it. i will keep you posted! :) thank you. Laura

If you didn't find what you need from this post, search on "convert case when to access" you find a lot, wish for you the best of luck

If you didn't find what you need from this post, search on "convert case when to access" you find a lot, wish for you the best of luck

Ok, so now I got past that error and now, there's an error with the joins? this is definitely not something I'm that good with yet. I am receiving the following error:

syntax error (mising operator) in query expression '

dbo.CLIENT ON dbo.PHONE.CLIENT_ID = dbo.CLIENT.CLIENT_ID RIGHT OUTER JOIN
dbo.CLIENT_TRANS ON dbo.CLIENT.CLIENT_ID = dbo.CLIENT_TRANS.CLIENT_ID LEFT OUTER JOIN
dbo.TRANS_CD ON

SELECT     dbo.CLIENT_TRANS.CLIENT_ID, 
                 dbo.CLIENT_TRANS.TRANS_CD_ID, 
                 dbo.TRANS_CD.TRANS_CD_DESC, 
                 dbo.CLIENT_TRANS.ITEM_ID, 
                 dbo.CLIENT_TRANS.ACT_TRANS_TS, 
                 dbo.CLIENT_TRANS.ACT_TRANS_DESC, 
                 dbo.CLIENT_TRANS.ACT_TRANS_AMT, 
                 dbo.CLIENT_TRANS.USER_ID, 
                 dbo.CLIENT.FIRST_NAME, 
                 dbo.CLIENT.LAST_NAME, 
                 dbo.CLIENT.ACCOUNT, 
                 dbo.CLIENT.FILTER_CODE_ID,
                 dbo.CLIENT_TRANS.PAYOUT_ID, 
                 dbo.ITEM.ITEM_NUM, 
                 dbo.ITEM.ITEM_NAME, 
                 dbo.TRANS_CD.NEGATIVE_IND, 
                 dbo.TRANS_CD.PAYABLE_IND, 
                 dbo.PHONE.PRIMARY_IND, 
                 dbo.PHONE.PHONE_NUMBER, 
                 dbo.PHONE.PHONE_DESC, 
                 dbo.CLIENT_TRANS.TRANS_CD_ID AS Expr1, 
                 dbo.CLIENT_TRANS.HOLD_IND, dbo.TRANS_CD.PAYABLE_IND AS Payable,
            IIf(dbo.CLIENT_TRANS.TRANS_CD_ID IN (1, 2, 5, 11) ,dbo.CLIENT_TRANS.ACT_TRANS_TS,
                   getdate() - 365 * 5) AS ITEMSOLDDATE
FROM         dbo.PHONE                                                                                                                                         RIGHT   OUTER JOIN
                   dbo.CLIENT              ON dbo.PHONE.CLIENT_ID                    = dbo.CLIENT.CLIENT_ID                  RIGHT   OUTER JOIN
                   dbo.CLIENT_TRANS ON dbo.CLIENT.CLIENT_ID                    = dbo.CLIENT_TRANS.CLIENT_ID     LEFT     OUTER JOIN
                   dbo.TRANS_CD        ON dbo.CLIENT_TRANS.TRANS_CD_ID = dbo.TRANS_CD.TRANS_CD_ID      LEFT     OUTER JOIN
                   dbo.ITEM                  ON dbo.CLIENT_TRANS.ITEM_ID          = dbo.ITEM.ITEM_ID
WHERE     (dbo.CLIENT_TRANS.PAYOUT_ID IS NULL) 
                    AND (dbo.PHONE.PRIMARY_IND = 1) 
                    AND (dbo.CLIENT_TRANS.TRANS_CD_ID <> 13) 
                    AND (dbo.CLIENT_TRANS.TRANS_CD_ID <> 8) 
                    AND (dbo.CLIENT_TRANS.TRANS_CD_ID <> 15) 
                    AND (dbo.CLIENT_TRANS.TRANS_CD_ID <> 9)

Is this another incompatibility with access sql and sql?

thanks :)

No, because Joins are standards. But did you use query visualizer to make sure you're doing correct relationship?

Ok, so now I got past that error and now, there's an error with the joins? this is definitely not something I'm that good with yet. I am receiving the following error:

syntax error (mising operator) in query expression '

dbo.CLIENT ON dbo.PHONE.CLIENT_ID = dbo.CLIENT.CLIENT_ID RIGHT OUTER JOIN
dbo.CLIENT_TRANS ON dbo.CLIENT.CLIENT_ID = dbo.CLIENT_TRANS.CLIENT_ID LEFT OUTER JOIN
dbo.TRANS_CD ON

SELECT     dbo.CLIENT_TRANS.CLIENT_ID, 
                 dbo.CLIENT_TRANS.TRANS_CD_ID, 
                 dbo.TRANS_CD.TRANS_CD_DESC, 
                 dbo.CLIENT_TRANS.ITEM_ID, 
                 dbo.CLIENT_TRANS.ACT_TRANS_TS, 
                 dbo.CLIENT_TRANS.ACT_TRANS_DESC, 
                 dbo.CLIENT_TRANS.ACT_TRANS_AMT, 
                 dbo.CLIENT_TRANS.USER_ID, 
                 dbo.CLIENT.FIRST_NAME, 
                 dbo.CLIENT.LAST_NAME, 
                 dbo.CLIENT.ACCOUNT, 
                 dbo.CLIENT.FILTER_CODE_ID,
                 dbo.CLIENT_TRANS.PAYOUT_ID, 
                 dbo.ITEM.ITEM_NUM, 
                 dbo.ITEM.ITEM_NAME, 
                 dbo.TRANS_CD.NEGATIVE_IND, 
                 dbo.TRANS_CD.PAYABLE_IND, 
                 dbo.PHONE.PRIMARY_IND, 
                 dbo.PHONE.PHONE_NUMBER, 
                 dbo.PHONE.PHONE_DESC, 
                 dbo.CLIENT_TRANS.TRANS_CD_ID AS Expr1, 
                 dbo.CLIENT_TRANS.HOLD_IND, dbo.TRANS_CD.PAYABLE_IND AS Payable,
            IIf(dbo.CLIENT_TRANS.TRANS_CD_ID IN (1, 2, 5, 11) ,dbo.CLIENT_TRANS.ACT_TRANS_TS,
                   getdate() - 365 * 5) AS ITEMSOLDDATE
FROM         dbo.PHONE                                                                                                                                         RIGHT   OUTER JOIN
                   dbo.CLIENT              ON dbo.PHONE.CLIENT_ID                    = dbo.CLIENT.CLIENT_ID                  RIGHT   OUTER JOIN
                   dbo.CLIENT_TRANS ON dbo.CLIENT.CLIENT_ID                    = dbo.CLIENT_TRANS.CLIENT_ID     LEFT     OUTER JOIN
                   dbo.TRANS_CD        ON dbo.CLIENT_TRANS.TRANS_CD_ID = dbo.TRANS_CD.TRANS_CD_ID      LEFT     OUTER JOIN
                   dbo.ITEM                  ON dbo.CLIENT_TRANS.ITEM_ID          = dbo.ITEM.ITEM_ID
WHERE     (dbo.CLIENT_TRANS.PAYOUT_ID IS NULL) 
                    AND (dbo.PHONE.PRIMARY_IND = 1) 
                    AND (dbo.CLIENT_TRANS.TRANS_CD_ID <> 13) 
                    AND (dbo.CLIENT_TRANS.TRANS_CD_ID <> 8) 
                    AND (dbo.CLIENT_TRANS.TRANS_CD_ID <> 15) 
                    AND (dbo.CLIENT_TRANS.TRANS_CD_ID <> 9)

Is this another incompatibility with access sql and sql?

thanks :)

sql does not support IIF
you have to use CASE

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.