Access: Syntax Error

[63falcondude]

I could have sworn that this code worked a couple of months ago but now I am getting the following error when attempting to run it:

Syntax error (missing operator) in query expression 'CASE WHEN d.SALESDAT IS NULL    THEN ' '
    ELSE TO_CHAR(d.SALESDAT,'MM/dd/yyyy')
END'.

After selecting OK, "WHEN" is highlighted.

Here is the beginning of my code to help put things into perspective:

SELECT 
d.PRODID AS Subsidiary_Identifier, 
d.PRODID2 AS Machine_Type, 
d.SERIALNO AS Mach_NO,
CASE WHEN d.SALESDAT IS NULL
    THEN ' '
    ELSE TO_CHAR(d.SALESDAT,'MM/dd/yyyy')
END AS Commissioning_Date,
CASE WHEN e.CDSTART IS NULL
    THEN ' '
    ELSE TO_CHAR(e.CDSTART,'MM/dd/yyyy')
END AS Date_of_Service_Work,
e.NO_INT AS Service_Worksheet_No, 
CAST(i.DOMVALMNEMO AS VARCHAR(10)) || ' - ' || ISNULL(h.DESCRLONG,' ') AS Error_Code, 
d.ERRTXT AS Error_Reported_by_Customer, 
f.ORDTXT AS Error_Reported_work_performed, 
ISNULL(j.NO_INT,' ') AS Mat_no_of_offending_component,
a.NO_INT AS Parts_Used,
a.nam AS Description,
e.empliddispo AS TechnicianID

Does anyone know what is causing this error? I believe this is using ORACLE or PSQL but really don't know. I am a beginner with Access and running queries.

 

[63falcondude]

Is this definitely a query you are trying to run in an Access database?

I am trying to run this query in Access the same way that I have ran it a few months ago. I connect to the database and then copy and paste the SQL (or PL SQL ... no idea really) into a new query from notepad. I believe that it is through Oracle but I don't even know what that means besides it being an extension of SQL...

 

[63falcondude]

yeah,try that

try changing every line that has an AS
NVL2(d.SALESDAT,TO_CHAR(d.SALESDAT,'MM/dd/yyyy'),' ') AS Commissioning_Date,

try these different forms for each line
NVL2(d.SALESDAT,TO_CHAR(d.SALESDAT,'MM/dd/yyyy'),' ') Commissioning_Date,

NVL2(d.SALESDAT,TO_CHAR(d.SALESDAT,'MM/dd/yyyy'),' ') [Commissioning_Date],

NVL2(d.SALESDAT,TO_CHAR(d.SALESDAT,'MM/dd/yyyy'),' ') 'Commissioning_Date',

With your help, I got those 2 CASE statements working (or at least not giving me an error). The errors are now coming further down in the code. Even if I delete the line of code with the CASE statement, I am getting errors further down in my joins (which I did not include in prior posts). The whole thing is off and I don't know why.

 

[Norie]

I'm pretty sure the Access version of SQL, which is kind of limited, doesn't support some of the syntax, eg CASE WHEN, that is used in that query.

Where you previously running the query using code via a connection to an another (Oracle?) database?

 

[63falcondude]

I'm pretty sure the Access version of SQL, which is kind of limited, doesn't support some of the syntax, eg CASE WHEN, that is used in that query.

Where you previously running the query using code via a connection to an another (Oracle?) database?

I'll try my best to answer your question because I'm not really sure. I just found where the working one was. It is in "All Access Objects" and then under "Queries". There is a list of queries with little Earths to the left of them. These were all created by someone else (not on my computer).

I must have ran it from there because that works fine and has the same code that I was trying to run in a new query. When I tried to copy that working code and paste it into a new query, I get those errors.

I guess that means that I was running the query using code via a connection to another database. Anyway, all is good now that I found where the working one is. Thank you so much for your help!

 

[xenou]

The queries with little earths in them are called "pass through" queries. It just sends the query text as is to another database (oracle) and the other database actually runs the queries.

These queries will only work in pass through queries - they are specifically using syntax that works only in oracle.

 

[63falcondude]

The queries with little earths in them are called "pass through" queries. It just sends the query text as is to another database (oracle) and the other database actually runs the queries.

These queries will only work in pass through queries - they are specifically using syntax that works only in oracle.

Thanks, that makes sense. I'll have to look into pass-through queries more. Why would people use pass-through queries over normal SQL queries in Access?

 

[xenou]

A pass through query would run on a server somewhere else. Typically, if your main data is on that server, it will be able to handle the request for you both faster and safer (especially queries that change data - updates, inserts, deletes).


----------------------------------------------------------------------------------------------
Note: that said, you can sometimes do something like this too:
1) select data from the remote server and put it into a local ("regular") access table.
2) now run you queries locally on that data.

But this works only if you have reporting going on, not updates, deletes, etc. And if the data is not extremely a lot, since in big servers you sometimes have a lot more data than even fits into Access.

 

[63falcondude]

A pass through query would run on a server somewhere else. Typically, if your main data is on that server, it will be able to handle the request for you both faster and safer (especially queries that change data - updates, inserts, deletes).


----------------------------------------------------------------------------------------------
Note: that said, you can sometimes do something like this too:
1) select data from the remote server and put it into a local ("regular") access table.
2) now run you queries locally on that data.

But this works only if you have reporting going on, not updates, deletes, etc. And if the data is not extremely a lot, since in big servers you sometimes have a lot more data than even fits into Access.

Interesting, thanks again for the help. Have a Merry Christmas guys.