Solved

Problem SQL query with CASE statement in MS Access

Posted on 2010-11-19
13
394 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
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

785 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