Translate an access query to sql express or sql 2005

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Juan Robledo - 29 Jun 2008 14:54 GMT

i have this sql strin that works fine in access and asp.net 2.0
i 'd like to pass to sql express but i get errors with the query.

the original Access query:

SELECT cAuxRk.CatB, cAuxRk.NombreCompleto, Sum(cAuxRk.PBh) AS Parc,
IIf(Count([PBh])<[NTJugados],0,Min(IIf([CT] Between 24 And 30,[PBh],Null)))
AS Peor, [Parc]-[Peor] AS Neto, Sum(IIf([CT]=24,[PBh],Null)) AS SRoq,
Sum(IIf([CT]=25,[PBh],Null)) AS DJul, Sum(IIf([CT]=26,[PBh],Null)) AS ViNu,
Sum(IIf([CT]=27,[PBh],Null)) AS Bena, Sum(IIf([CT]=28,[PBh],Null)) AS Arco,
Sum(IIf([CT]=29,[PBh],Null)) AS Sher, Sum(IIf([CT]=30,[PBh],Null)) AS Cala,
Sum(IIf([CT]=31,[PBh],Null)) AS Final
FROM cAuxRk
GROUP BY cAuxRk.CatB, cAuxRk.NombreCompleto, cAuxRk.NTJugados
ORDER BY cAuxRk.CatB, Sum(cAuxRk.PBh) DESC;

the sql express query:

SELECT TOP (100) PERCENT CatB, nCompleto, SUM(PBh) AS Parc,
COUNT(CASE PBh < NTJugados THEN 0 ELSE MIN(CASE CT
BETWEEN 24 AND 30 THEN PBh ELSE NULL END) END) As Peor,
SUM(CASE CT WHEN 24 THEN PBh ELSE NULL END) AS
SRoq,
SUM(CASE CT WHEN 25 THEN PBh ELSE NULL END) AS
DJul, SUM(CASE CT WHEN 26 THEN PBh ELSE NULL END) AS Vinu,
SUM(CASE CT WHEN 27 THEN PBh ELSE NULL END) AS
Bena, SUM(CASE CT WHEN 28 THEN PBh ELSE NULL END) AS Arco,
SUM(CASE CT WHEN 29 THEN PBh ELSE NULL END) AS
Sher, SUM(CASE CT WHEN 30 THEN PBh ELSE NULL END) AS Cala,
SUM(CASE CT WHEN 31 THEN PBh ELSE NULL END) AS
Final
FROM dbo.cAuxRK
GROUP BY CatB, nCompleto, NTJugados

the errors i get are:

Error en la lista de argumentos de función: no se reconoce '<'.
Error en la lista de argumentos de función: no se reconoce ')'.
Error en la lista de argumentos de función: no se reconoce ','.
No se puede analizar el texto de la consulta.

the problem is this part of the string:
COUNT(CASE PBh < NTJugados THEN 0 ELSE MIN(CASE CT BETWEEN 24 AND 30 THEN
PBh ELSE NULL END) END) As Peor,

if i supress this part, the query works fine, but i don´t know how to do
transform this part of the string

Any idea?
Thanks.

Juan.

Reply to this Message

Dan Guzman - 29 Jun 2008 16:49 GMT

> the problem is this part of the string:
> COUNT(CASE PBh < NTJugados THEN 0 ELSE MIN(CASE CT BETWEEN 24 AND 30 THEN
> PBh ELSE NULL END) END) As Peor,

There are 2 different forms of a CASE expression - simple and searched. You
need to specify the searched form in the COUNT expression because you are
evaluating boolean expressions (PBh < NTJugados and CT BETWEEN 24 AND 30)
rather than a simple input expression (CT). See CASE in the Books Online
for details.

Also, don't specify TOP (100) PERCENT in your query. That clause is
generated by the query designer only to facilitate data sampling and is
unneeded in your final query. Untested query below.

SELECT
CatB,
nCompleto,
SUM(PBh) AS Parc,
COUNT(CASE WHEN PBh < NTJugados THEN 0
ELSE MIN(CASE WHEN CT BETWEEN 24 AND 30 THEN PBh
ELSE NULL END) END) As Peor,
SUM(CASE CT WHEN 24 THEN PBh ELSE NULL END) AS SRoq,
SUM(CASE CT WHEN 25 THEN PBh ELSE NULL END) AS DJul,
SUM(CASE CT WHEN 26 THEN PBh ELSE NULL END) AS Vinu,
SUM(CASE CT WHEN 27 THEN PBh ELSE NULL END) AS Bena,
SUM(CASE CT WHEN 28 THEN PBh ELSE NULL END) AS Arco,
SUM(CASE CT WHEN 29 THEN PBh ELSE NULL END) AS Sher,
SUM(CASE CT WHEN 30 THEN PBh ELSE NULL END) AS Cala,
SUM(CASE CT WHEN 31 THEN PBh ELSE NULL END) AS Final
FROM dbo.cAuxRK
GROUP BY
CatB,
nCompleto,
NTJugados

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

>i have this sql strin that works fine in access and asp.net 2.0
> i 'd like to pass to sql express but i get errors with the query.
[quoted text clipped - 49 lines]
>
> Juan.

Reply to this Message

Uri Dimant - 30 Jun 2008 05:43 GMT

Dan
Hmm, I have not used queries that are built like that.
I tried to test something simple (interpret your example) on Northwind
database and getting error

SELECT

> CatB,
> nCompleto,
> SUM(PBh) AS Parc,
> COUNT(CASE WHEN PBh < NTJugados THEN 0
> ELSE MIN(CASE WHEN CT BETWEEN 24 AND 30 THEN PBh
> ELSE NULL END) END) As Peor,

----sql server 2000/2005

SELECT

SUM(orderid) AS orderid,
COUNT(CASE WHEN ProductID < Quantity THEN 0
ELSE MIN(CASE WHEN ProductID BETWEEN 24 AND 30 THEN ProductID
ELSE NULL END) END) As Peor
FROM [order details]

Server: Msg 130, Level 15, State 1, Line 6
Cannot perform an aggregate function on an expression containing an
aggregate or a subquery.

>> the problem is this part of the string:
>> COUNT(CASE PBh < NTJugados THEN 0 ELSE MIN(CASE CT BETWEEN 24 AND 30
[quoted text clipped - 84 lines]
>>
>> Juan.

Reply to this Message

Dan Guzman - 30 Jun 2008 13:36 GMT

> I tried to test something simple (interpret your example) on Northwind
> database and getting error

Uri, I addressed the problem with the CASE expression but not the aggregates
and mentioned that the query was untested. John and Plamen chimed in about
the other query errors and the original poster reported that the problem was
resolved.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> Dan
> Hmm, I have not used queries that are built like that.
[quoted text clipped - 111 lines]
>>>
>>> Juan.

Reply to this Message

Jeffrey Williams - 29 Jun 2008 16:52 GMT

> i have this sql strin that works fine in access and asp.net 2.0
> i 'd like to pass to sql express but i get errors with the query.
[quoted text clipped - 49 lines]
>
> Juan.

Juan,

There are two forms of case expressions. The simple case expression is:

CASE <expression>
WHEN <value> THEN ...
WHEN <value> THEN ...
ELSE ...
END

The second form is the searched case, which looks like:

CASE
WHEN <expression> THEN ...
WHEN <expression> THEN ...
ELSE ...
END

In your query, you are trying to use the simple case, when the searched
case is what you really need. Lookup CASE in BOL for more information.

Also, remove that 'silly' TOP (100) PERCENT - it is not needed. Here is
a rewrite that may be what you are looking for:

SELECT CatB
,nCompleto
,SUM(PBh) AS Parc
,COUNT(CASE WHEN PBh < NTJugados THEN 0
ELSE MIN(CASE WHEN CT BETWEEN 24 AND 30 THEN PBh
ELSE NULL
END)
END) As Peor
,SUM(CASE CT WHEN 24 THEN PBh ELSE NULL END) AS SRoq
,SUM(CASE CT WHEN 25 THEN PBh ELSE NULL END) AS DJul
,SUM(CASE CT WHEN 26 THEN PBh ELSE NULL END) AS Vinu
,SUM(CASE CT WHEN 27 THEN PBh ELSE NULL END) AS Bena
,SUM(CASE CT WHEN 28 THEN PBh ELSE NULL END) AS Arco
,SUM(CASE CT WHEN 29 THEN PBh ELSE NULL END) AS Sher
,SUM(CASE CT WHEN 30 THEN PBh ELSE NULL END) AS Cala
,SUM(CASE CT WHEN 31 THEN PBh ELSE NULL END) AS Final
FROM dbo.cAuxRK
GROUP BY
CatB
,nCompleto
,NTJugados

HTH,

Jeff

Reply to this Message

Tom Cooper - 29 Jun 2008 16:58 GMT

You are missing two WHEN's. To make the CASE statements syntactically
correct,
COUNT(CASE PBh < NTJugados THEN 0 ELSE MIN(CASE CT BETWEEN 24 AND 30 THEN
PBh ELSE NULL END) END) As Peor
should be
COUNT(CASE WHEN PBh < NTJugados THEN 0 ELSE MIN(CASE WHEN CT BETWEEN 24 AND
30 THEN
PBh ELSE NULL END) END) As Peor

However, if you do that, you will get a new error number 130
"Cannot perform an aggregate function on an expression containing an
aggregate or a subquery."

You get that because you are doing a COUNT of an expression that includes a
MIN function. So you will also have to change that. Without data and
expected results, it is difficult to tell you what the correction for that
should be. Perhaps what you want is
COUNT(CASE WHEN PBh < NTJugados THEN 0 ELSE CASE WHEN CT BETWEEN 24 AND 30
THEN
PBh ELSE NULL END END) As Peor,
which is syntacally correct, but may not return the result you want. If you
need help correcting this, it would be best if you gave us DDL and sample
data, and the expected results with that sample data.

Tom

>i have this sql strin that works fine in access and asp.net 2.0
> i 'd like to pass to sql express but i get errors with the query.
[quoted text clipped - 49 lines]
>
> Juan.

Reply to this Message

John Bell - 29 Jun 2008 17:46 GMT

>i have this sql strin that works fine in access and asp.net 2.0
> i 'd like to pass to sql express but i get errors with the query.
[quoted text clipped - 49 lines]
>
> Juan.

Hi

No one has commented on your use of MIN!

I am not sure why you have MIN(CASE WHEN CT BETWEEN 24 AND 30 THEN PBh
ELSE NULL END) if you are only counting them?

(CASE WHEN CT BETWEEN 24 AND 30 THEN 1 ELSE NULL END)

Also

SELECT COUNT(0)

will return 1

I suspect that you should be summing them which means your original access
query may be wrong!

SUM (CASE WHEN PBh < NTJugados AND CT BETWEEN 24 AND 30 THEN PBh END) As
Peor,

John

Reply to this Message

Plamen Ratchev - 29 Jun 2008 19:11 GMT

You already have a few replies explaining the problems with the CASE
function. However, seems to me you incorrectly translated the Access
formula. Here is what you have in Access:

IIf(Count([PBh])<[NTJugados],0,Min(IIf([CT] Between 24 And 30,[PBh],Null)))
AS Peor

That in SQL Server code will be:

CASE WHEN COUNT(PBh) < NTJugados
THEN 0
ELSE MIN(CASE WHEN CT BETWEEN 24 AND 30
THEN PBh
END)
END AS Peor

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Juan Robledo - 29 Jun 2008 19:22 GMT

I already resolve the problem.

Thank you anyway.

>i have this sql strin that works fine in access and asp.net 2.0
> i 'd like to pass to sql express but i get errors with the query.
[quoted text clipped - 49 lines]
>
> Juan.

Reply to this Message