Solved

Problem SQL query with CASE statement in MS Access

Posted on 2010-11-19
13
395 Views
Last Modified: 2012-05-10
Hello experts.

I wrote a query to create a view in MS Access. When I tried to re-write it in t-sql in MS Access, in order to use it with Intergration Services, I can't save it because there is an error.
The error message I get is: "Syntax error (missing operator) in query expression 'CASE WHEN ...'."

Thanks.

SELECT DISTINCT SUBSTRING(T_DROMOS.TXCODE,1,2) AS ZONI_ID, T_DROMOS.TXCODE, T_DROMOS.TXDESC, T_DROMOS.TXJAC1, T_DROMOS.TXCHAM, 
CASE 
WHEN CONTROL.COADR1 Like '%VLACHOS%' OR CONTROL.COADR2 Like '%VLACHOS%' OR CONTROL.COADR3 Like '%VLACHOS%'
THEN 1
WHEN CONTROL.COADR1 Like '%FANOURIOU%' OR CONTROL.COADR2 Like '%FANOURIOU%' OR CONTROL.COADR3 Like '%FANOURIOU%'
THEN 2
WHEN CONTROL.COADR1 Like '%LOUCA%' OR CONTROL.COADR2 Like '%LOUCA%' OR CONTROL.COADR3 Like '%LOUCA%' 
THEN 3
ELSE -1
END AS ENORIAID
FROM T_DROMOS INNER JOIN TXCONN ON T_DROMOS.TXCODE=SUBSTRING(TXCONN.TXTAXC,1,5) INNER JOIN CONTROL ON TXCONN.TXTAXP=CONTROL.COCODE;

Open in new window

0
Comment
Question by:spiral2007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 12

Expert Comment

by:ErezMor
ID: 34172199
access uses a different dialect of sql than sql erver (t-sql)
if your query is intended to run on sql server's data from access, you need to turn your query type to a "pass-through" query, which does as it's name implies, passes the whole syntax "as is" through to sql server without interpetting (hence no syntax errors...)
0
 

Author Comment

by:spiral2007
ID: 34172215
And how can i do that?
0
 
LVL 12

Expert Comment

by:ErezMor
ID: 34172235
in access 2007-2010 it's on the design ribbon
older versions, right click the query window and select sql specific...passthrough
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:spiral2007
ID: 34172265
If I turn it to pass through I can't find it in my Integration Services Solution.
0
 
LVL 12

Expert Comment

by:ErezMor
ID: 34172336
regardless of integration services,
if it's sql server's data, then create it directly in sql server as a view or storeprocedure, if it's access data, then change the t-sql sentences to jet-sql so it can be saved as normal access query
tell me which way is going to be and we'll proceed from there
0
 
LVL 9

Accepted Solution

by:
Stephan_Schrandt earned 250 total points
ID: 34172350
msaccess does not provide the inbuilt functions like t-sql. But access can use vba functions do simulate this. Add a vba module to your database and paste the code snippet. Then in the query use the function like this:

SELECT DISTINCT SUBSTRING(T_DROMOS.TXCODE,1,2) AS ZONI_ID, T_DROMOS.TXCODE, T_DROMOS.TXDESC, T_DROMOS.TXJAC1, T_DROMOS.TXCHAM,
calfield(CONTROL.COADR1,CONTROL.COADR2,CONTROL.COADR3) AS ENORIAID
FROM T_DROMOS.....


Option Explicit

Public Function calfield(value1, value2, value3) As String
    If InStr(1, value1, "VLACHOS", vbTextCompare) > -1 Or InStr(1, value2, "VLACHOS", vbTextCompare) > -1 Or InStr(1, value3, "VLACHOS", vbTextCompare) > -1 Then
        calfield = 1
    ElseIf InStr(1, value1, "FANOURIOU", vbTextCompare) > -1 Or InStr(1, value2, "FANOURIOU", vbTextCompare) > -1 Or InStr(1, value3, "FANOURIOU", vbTextCompare) > -1 Then
        calfield = 2
    ElseIf InStr(1, value1, "LOUCA", vbTextCompare) > -1 Or InStr(1, value2, "LOUCA", vbTextCompare) > -1 Or InStr(1, value3, "LOUCA", vbTextCompare) > -1 Then
        calfield = 3
    Else
        calfield = 4
    End If
End Function

Open in new window

0
 

Author Comment

by:spiral2007
ID: 34172356
It's access data so it's the second way.
Thanks.
0
 
LVL 12

Expert Comment

by:ErezMor
ID: 34172401
a vba function is  one way, but you can still do with only sql if you change case statements to IIF (immediate if), so your t-sql should be something like:


SELECT DISTINCT SUBSTRING(T_DROMOS.TXCODE,1,2) AS ZONI_ID, T_DROMOS.TXCODE, T_DROMOS.TXDESC, T_DROMOS.TXJAC1, T_DROMOS.TXCHAM,
IIF(CONTROL.COADR1 Like '*VLACHOS*' OR CONTROL.COADR2 Like '*VLACHOS*' OR CONTROL.COADR3 Like '*VLACHOS*',1,
IIF(CONTROL.COADR1 Like '*FANOURIOU*' OR CONTROL.COADR2 Like '*FANOURIOU*' OR CONTROL.COADR3 Like '*FANOURIOU*',2,
IIF(CONTROL.COADR1 Like '*LOUCA*' OR CONTROL.COADR2 Like '*LOUCA*' OR CONTROL.COADR3 Like '*LOUCA*',3,-1))) AS ENORIAID
FROM T_DROMOS INNER JOIN TXCONN ON T_DROMOS.TXCODE=SUBSTRING(TXCONN.TXTAXC,1,5) INNER JOIN CONTROL ON TXCONN.TXTAXP=CONTROL.COCODE;
0
 
LVL 12

Assisted Solution

by:ErezMor
ErezMor earned 250 total points
ID: 34172418
oops, missed the substring function, which is a t-sql too, change it to Left(T_DROMOS.TXCODE,2)
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 34387646
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question