Incorrect syntax near '='

Hi,
I'm using multiple select statements in MS-Access with logical
expressions like (ID=10)*50 instead of an if statement. In this manner
i'm able to get the same results as with if statements (if ID=10 then
result=50) only faster (in MS-Access that is).
when trying to get the same sql statement running in SQL-server I
receive an Incorrect syntax near '=' error message.
Are there any solutions? I will try to avoid updating all queries
because MS-Access will also be used as backend next to a version for
SQL-server backend.
Maybe anyone can help me.You can use CASE statement
SELECT result =
CASE WHEN ID = 10 THEN 50
ELSE something END
"Niels Verkaart" wrote:

> Hi,
> I'm using multiple select statements in MS-Access with logical
> expressions like (ID=10)*50 instead of an if statement. In this manner
> i'm able to get the same results as with if statements (if ID=10 then
> result=50) only faster (in MS-Access that is).
> when trying to get the same sql statement running in SQL-server I
> receive an Incorrect syntax near '=' error message.
> Are there any solutions? I will try to avoid updating all queries
> because MS-Access will also be used as backend next to a version for
> SQL-server backend.
> Maybe anyone can help me.
>|||Thank you Jack,
now I only have to write a clever function to convert dynamic queries
like:
INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 ) SELECT
VerkoopsoortID,Sum(-(Year([Datum])=2006)*[Aantal]),Sum(-(Year([Datum])=2
007)*[Aantal]),Sum(-(Year([Datum])=2008)*[Aantal]),Sum(-(Year([Datum])=2
009)*[Aantal]) FROM CalcAfzetPeriode WHERE CalcID=1528 AND
VerkoopsoortID NOT IN (0) GROUP BY VerkoopsoortID
That's a nice little problem for me. but i'll get there.
> You can use CASE statement
> SELECT result =
> CASE WHEN ID = 10 THEN 50
> ELSE something END
> "Niels Verkaart" wrote:
>|||Hi Niels,
This is a typical crosstab (FAQ). The solution is basically the same as
what Jack has posted:
INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 )
SELECT VerkoopsoortID
, SUM(CASE Year(Datum) WHEN 2006 THEN Aantal END)
, SUM(CASE Year(Datum) WHEN 2007 THEN Aantal END)
, SUM(CASE Year(Datum) WHEN 2008 THEN Aantal END)
, SUM(CASE Year(Datum) WHEN 2009 THEN Aantal END)
FROM CalcAfzetPeriode
WHERE CalcID=1528
AND VerkoopsoortID NOT IN (0)
GROUP BY VerkoopsoortID
HTH,
Gert-Jan
Niels Verkaart wrote:
> Thank you Jack,
> now I only have to write a clever function to convert dynamic queries
> like:
> INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 ) SELECT
> VerkoopsoortID,Sum(-(Year([Datum])=2006)*[Aantal]),Sum(-(Year([Datum])=2
> 007)*[Aantal]),Sum(-(Year([Datum])=2008)*[Aantal]),Sum(-(Year([Datum])=2
> 009)*[Aantal]) FROM CalcAfzetPeriode WHERE CalcID=1528 AND
> VerkoopsoortID NOT IN (0) GROUP BY VerkoopsoortID
> That's a nice little problem for me. but i'll get there.
>|||Great Gert-Jan,
thank you both Gert-Jan and Jack!
Gert-Jan Strik wrote:
> Hi Niels,
> This is a typical crosstab (FAQ). The solution is basically the same
> as what Jack has posted:
> INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 )
> SELECT VerkoopsoortID
> , SUM(CASE Year(Datum) WHEN 2006 THEN Aantal END)
> , SUM(CASE Year(Datum) WHEN 2007 THEN Aantal END)
> , SUM(CASE Year(Datum) WHEN 2008 THEN Aantal END)
> , SUM(CASE Year(Datum) WHEN 2009 THEN Aantal END)
> FROM CalcAfzetPeriode
> WHERE CalcID=1528
> AND VerkoopsoortID NOT IN (0)
> GROUP BY VerkoopsoortID
> HTH,
> Gert-Jan
>
> Niels Verkaart wrote: