problems with query that is setting a flag

Question

problems with query that is setting a flag

jrw39

Mr or Mrs. 500

Points: 565
More actions
August 14, 2009 at 11:20 am

#209424

I'm having problems with this query which should set a flag based on if then then else from access 07. I've converted it to CASE WHEN but I'm still getting the error below.
CASE WHEN((([XPD]=1) Or ([CRTR]=1)),1,0) AS [XPD Flag],
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near ','.
Thanks
in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

jrw39 (8/14/2009)
I'm having problems with this query which should set a flag based on if then then else from access 07. I've converted it to CASE WHEN but I'm still getting the error below.
CASE WHEN((([XPD]=1) Or ([CRTR]=1)),1,0) AS [XPD Flag],
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near ','.
Thanks
in advance

CASE WHEN XPD = 1 OR CRTR = 1 THEN 1 ELSE 0 END as XPDFlag

Questions regarding syntax are easily answered by looking in BOL (Books Online, the SQL Server help System).

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

Dear JRW

Did you look at this?...I posted this today in response to another question you raised in a different thread...this outlines MANY of the differences between Access and SQL syntax

http://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html

alternatively use BOL...you have already recieved this advice from many people.

the syntax is CASE...WHEN...ELSE...END...which is more powerful than nested IIf statements and far easier to read

I appreciate that moving from Access to SQL is not easy...been there, read the book, got the shirt, ate the pie !

But it is worth it....particularly with large datasets amd mutilple users.

What front-end are you intending to use....Access ADP?

Kind regards Graham

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

jrw39 Mr or Mrs. 500 Points: 565 More actions · Answer 3

Thank you so much this is a major problem I'm running into while trying to convert queries from access 07 to sql server 05

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

Dear jrw

MS give the impression that upgrading from Access to SQL is a walk in the park...well it can be if all you wish to do is migrate some data and tables.

If you have anything much more complicated than that ...where there are forms/reports driven by VBA/macros then it can be a nightmare....:crazy:

As you now appreciate the query syntax is quite different in several areas, and this can be confusing when trying to research problems.

Other gotcha's...the method of passing parameters to queries that populate forms...run your queries as stored procedures on the server to gain most benefit; only return the rows required rather than letting Jet do the filtering on the client...yadda yadda yadda.

Are you just moving data and queries to SQL or have you got an Access front end application?

Kind regards gah

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

jrw39 Mr or Mrs. 500 Points: 565 More actions · Answer 5

The goal for the end state after the conversion is complete is to have an access front end and a SQL Server backend. It has been extremely frustrating with the syntax especially converting the IIf from access to the CASE WHEN for SQL. The encouraging thing is that we have already made huge processing gains with the couple of databases that have been converted.