SQL syntax error

[alvintse]

Hi,
I am using Access 2002. For a query, I wrote:

Select m.ID, m.Name,
Check1 = case when m.ID in (SELECT m1.ID FROM merge m1 Where m1.check_code = '1') Then 1 Else 0 End
From merge m;

The error message is:

Syntax error (missing operator) in query expression 'Check1 = case when m.ID in (SELECT m1.ID FROM merge m1 Where m1.check_code = '1') Then 1 Else 0 End

Check1 is a new field I added to the query. Can someone tell me what's wrong?

Thanks

 

[Crowley16]

eh, your sql doesn't make sense...

the line:
Check1 = case when m.ID in (SELECT m1.ID FROM merge m1 Where m1.check_code = '1') Then 1 Else 0 End

is not a valid field specification...

I think you are using syntax from another sql engine...

maybe if you post what you actually want, we can help you more...

 

[GHolden]

Hi,

It does look like it may be an Access thing.

The following...

SELECT Code,
   Val = 
     CASE 
        WHEN ADate IN (SELECT C.ADATE FROM A C) 
        THEN 1 
        ELSE 0 
     END
FROM A

Works fine in SQL Server but I receive the same error as you when tested in Access.



There are two ways to write error-free programs; only the third one works.

 

[Crowley16]

Just for info:

Access uses something called JET SQL, which is a SQL implementation in it's own right...

so you can't expect to use SQL Server syntax or any other syntax in Access...

 

[GHolden]

Crowley16:

However, there is very little difference between JET SQL (Access) and T-SQL (SQL Server).

I do a lot of Access SQL development in SQL Server Query Analyser because it's a more user friendly environment.



There are two ways to write error-free programs; only the third one works.

 

[PHV]

Something like this ?
Select m.ID, m.Name, First(NZ(C.chk, 0)) As Check1
From merge m LEFT JOIN
(SELECT ID, 1 As chk FROM merge Where check_code = '1') C
ON m.ID=C.ID
GROUP BY m.ID, m.Name
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GHolden]

Hi,

Possibly something like this...

SELECT m.ID,
       m.Name,
       IIf(m.ID IN (SELECT m1.ID FROM merge m1 WHERE m1.check_code = '1'),1,0) AS Check1
FROM merge m;

There are two ways to write error-free programs; only the third one works.

 

[alvintse]

Thanks for all the replied. I used GHolden's method and created a query exactly what I expected. When I use the query for the report. I need to group by ID, then name. It working fine if I set those header and footer not showed. But I need to put the fields at the footer section. When I do this, I got an error message:

Muliti-level group by clause is not allowed in a subquery.

Any idea?

Thanks.

 

[PHV]

Have you tried my suggestion, ie no subquery ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[alvintse]

Sorry PHV,
I am not quite understand your query for the part:

First(NZ(C.chk, 0)) As Check1

Can you explain how this work?

Thanks

 

[PHV]

First(NZ(C.chk, 0)) As Check1
As we are in an aggregate query, use an aggregate function (First)
As we deal with an outer join, use the Nz function, to replace null value (ie not found in the joined query) with zero.
BTW, it is working ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244