query problem case syntax error can't figure it out

[wildpetals]

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

[Ramy Mahrous]

Your query is very complicated but where do you run this query? and what I can help you is to give you this link may the error in Case....When syntax http://msdn.microsoft.com/en-us/library/ms181765.aspx

[wildpetals]

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. :)

[Ramy Mahrous]

Look there's no Case...Where syntax in Access, and that's way I asked your "Where you run this query" but I found an article shows how to convert T-SQL query to Access query; here you're http://ewbi.blogs.com/develops/2006/02/adding_case_to_.html please keep me up-to-date with your problem

[wildpetals]

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

[Ramy Mahrous]

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

[wildpetals]

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 :)

[Ramy Mahrous]

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

[atinobrian]

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