query problem case syntax error can't figure it out

Question

wildpetals 0 Newbie Poster

15 Years Ago

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

mssql

3 Contributors
8 Replies
148 Views
1 Week Discussion Span
Latest Post 15 Years Ago Latest Post by atinobrian

Ramy Mahrous 401 Postaholic

15 Years Ago

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

Ramy Mahrous 401 Postaholic

15 Years Ago

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

Reply to this topic

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.

wildpetals 0 Newbie Poster · Answer 1 · 2009-01-19T01:48:47+00:00

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

wildpetals 0 Newbie Poster · Answer 2 · 2009-01-19T02:07:25+00:00

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 401 Postaholic Featured Poster · Answer 3 · 2009-01-19T02:15:42+00:00

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 0 Newbie Poster · Answer 4 · 2009-01-19T04:33:04+00:00

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 401 Postaholic Featured Poster · Answer 5 · 2009-01-19T12:28:09+00:00

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

atinobrian 7 Newbie Poster · Answer 6 · 2009-01-29T12:24:10+00:00

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