Transform Access SQL to SQL server 2005 or SQLExpress

[Juan Robledo]

I need help about this crosstab query that works fine in an AccessDataSorce
(Asp.net 2.0)
Now i'm trying to pass all the web to sqlDataSource and i have troubles in
this part of the string:

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

How can i translate that part ?
I have tried with case(count(Catb)....) but always get an error.


The complete query is the following:

SELECT CatB, NombreCompleto, Sum(PBh) AS Parc,
IIf(Count([CatB])<[NTJugados],0,Min(IIf([CT] Between 24 And 30,[PBh],Null)))
AS Worst, [Parc]-[Worst] AS Neto, Sum(IIf([CT]=24,[PBh],Null)) AS Col01,
Sum(IIf([CT]=25,[PBh],Null)) AS Col02, Sum(IIf([CT]=26,[PBh],Null)) AS
Col03, Sum(IIf([CT]=27,[PBh],Null)) AS Col04, Sum(IIf([CT]=28,[PBh],Null))
AS Col05, Sum(IIf([CT]=29,[PBh],Null)) AS Col06

FROM cAuxRk
GROUP BY cAuxRk.CatB, cAuxRk.NombreCompleto, cAuxRk.NTJugados
ORDER BY cAuxRk.CatB, Sum(cAuxRk.PBh) DESC;


Thank you.
Juan.

 

[Bob Barrows [MVP]]

I need help about this crosstab query that works fine in an
AccessDataSorce (Asp.net 2.0)
Now i'm trying to pass all the web to sqlDataSource and i have
troubles in this part of the string:

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

How can i translate that part ?
I have tried with case(count(Catb)....) but always get an error.

What error?

The complete query is the following:

SELECT CatB, NombreCompleto, Sum(PBh) AS Parc,
IIf(Count([CatB])<[NTJugados],0,Min(IIf([CT] Between 24 And
30,[PBh],Null))) AS Worst, [Parc]-[Worst] AS Neto,
Sum(IIf([CT]=24,[PBh],Null)) AS Col01, Sum(IIf([CT]=25,[PBh],Null))
AS Col02, Sum(IIf([CT]=26,[PBh],Null)) AS Col03,
Sum(IIf([CT]=27,[PBh],Null)) AS Col04, Sum(IIf([CT]=28,[PBh],Null))
AS Col05, Sum(IIf([CT]=29,[PBh],Null)) AS Col06
FROM cAuxRk
GROUP BY cAuxRk.CatB, cAuxRk.NombreCompleto, cAuxRk.NTJugados
ORDER BY cAuxRk.CatB, Sum(cAuxRk.PBh) DESC;

You would have had better response asking this in an asp.net or sql server
group.
Does this query really work in Access? You can't group by CatB and count it
at the same time ...

 

[Juan Robledo]

I don´t know why works, but the query results ok in Acesss.
Anyway, i changed that column for iff(Count([PBh] < [NTJugados] .... but the
errors i get are the following:

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

the sql (sql express) string is:

SELECT TOP (100) PERCENT CatB, nCompleto, SUM(PBh) AS Parc,
IIf(Count([PBh])<[NTJugados],0,Min(IIf([CT] Between
24 And 28,[PBh],Null))) AS Peor,

SUM(CASE CT WHEN 24 THEN PBh ELSE NULL END) AS
Col01,
SUM(CASE CT WHEN 25 THEN PBh ELSE NULL END) AS
Col02, SUM(CASE CT WHEN 26 THEN PBh ELSE NULL END) AS Col03,
SUM(CASE CT WHEN 27 THEN PBh ELSE NULL END) AS
Col04, SUM(CASE CT WHEN 28 THEN PBh ELSE NULL END) AS Col05,
SUM(CASE CT WHEN 29 THEN PBh ELSE NULL END) AS
Col06
FROM dbo.cAuxRK
GROUP BY CatB, nCompleto, NTJugados
ORDER BY CatB, Parc DESC

I need help about this crosstab query that works fine in an
AccessDataSorce (Asp.net 2.0)
Now i'm trying to pass all the web to sqlDataSource and i have
troubles in this part of the string:

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

How can i translate that part ?
I have tried with case(count(Catb)....) but always get an error.

What error?

The complete query is the following:

SELECT CatB, NombreCompleto, Sum(PBh) AS Parc,
IIf(Count([CatB])<[NTJugados],0,Min(IIf([CT] Between 24 And
30,[PBh],Null))) AS Worst, [Parc]-[Worst] AS Neto,
Sum(IIf([CT]=24,[PBh],Null)) AS Col01, Sum(IIf([CT]=25,[PBh],Null))
AS Col02, Sum(IIf([CT]=26,[PBh],Null)) AS Col03,
Sum(IIf([CT]=27,[PBh],Null)) AS Col04, Sum(IIf([CT]=28,[PBh],Null))
AS Col05, Sum(IIf([CT]=29,[PBh],Null)) AS Col06
FROM cAuxRk
GROUP BY cAuxRk.CatB, cAuxRk.NombreCompleto, cAuxRk.NTJugados
ORDER BY cAuxRk.CatB, Sum(cAuxRk.PBh) DESC;

You would have had better response asking this in an asp.net or sql server
group.
Does this query really work in Access? You can't group by CatB and count
it at the same time ...

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Bob Barrows [MVP]]

I don´t know why works, but the query results ok in Acesss.
Anyway, i changed that column for iff(Count([PBh] < [NTJugados] ....
but the errors i get are the following:

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

the sql (sql express) string is:

SELECT TOP (100) PERCENT CatB, nCompleto, SUM(PBh) AS Parc,
IIf(Count([PBh])<[NTJugados],0,Min(IIf([CT]
Between 24 And 28,[PBh],Null))) AS Peor,

If you want this thing to work in sql server, you have to get rid of these
iif statements. Use CASE.

 

[Juan Robledo]

i know, but i tried with this:
COUNT(CASE PBh < NTJugados THEN 0 ELSE MIN(CASE CT BETWEEN 24 AND 28 THEN
PBh ELSE NULL END) END) As Peor,

and i get these errors:
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.

I don´t know why works, but the query results ok in Acesss.
Anyway, i changed that column for iff(Count([PBh] < [NTJugados] ....
but the errors i get are the following:

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

the sql (sql express) string is:

SELECT TOP (100) PERCENT CatB, nCompleto, SUM(PBh) AS Parc,
IIf(Count([PBh])<[NTJugados],0,Min(IIf([CT]
Between 24 And 28,[PBh],Null))) AS Peor,

If you want this thing to work in sql server, you have to get rid of these
iif statements. Use CASE.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Bob Barrows [MVP]]

i know, but i tried with this:
COUNT(CASE PBh < NTJugados THEN 0 ELSE MIN(CASE CT BETWEEN 24 AND 28
THEN PBh ELSE NULL END) END) As Peor,

You have to use the CASE WHEN syntax:

CASE when PBh < NTJugados THEN 0 ELSE MIN(CASE WHEN CT BETWEEN 24 AND 28 ...

and i get these errors:
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.

I don´t know why works, but the query results ok in Acesss.
Anyway, i changed that column for iff(Count([PBh] < [NTJugados] ....
but the errors i get are the following:

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

the sql (sql express) string is:

SELECT TOP (100) PERCENT CatB, nCompleto, SUM(PBh) AS Parc,
IIf(Count([PBh])<[NTJugados],0,Min(IIf([CT]
Between 24 And 28,[PBh],Null))) AS Peor,

If you want this thing to work in sql server, you have to get rid of
these iif statements. Use CASE.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

 

[Juan Robledo]

Ok!, i 've tried and now i don't get any syntax error, but when i execute
the query i get this one:

Error Message: Its not possible to use a agregate function with an
expression that contains an agregate or a subquery.

now the string is:

SELECT TOP (100) PERCENT 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

i know, but i tried with this:
COUNT(CASE PBh < NTJugados THEN 0 ELSE MIN(CASE CT BETWEEN 24 AND 28
THEN PBh ELSE NULL END) END) As Peor,

You have to use the CASE WHEN syntax:

CASE when PBh < NTJugados THEN 0 ELSE MIN(CASE WHEN CT BETWEEN 24 AND 28
...

and i get these errors:
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.

Juan Robledo wrote:
I don´t know why works, but the query results ok in Acesss.
Anyway, i changed that column for iff(Count([PBh] < [NTJugados] ....
but the errors i get are the following:

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

the sql (sql express) string is:

SELECT TOP (100) PERCENT CatB, nCompleto, SUM(PBh) AS Parc,
IIf(Count([PBh])<[NTJugados],0,Min(IIf([CT]
Between 24 And 28,[PBh],Null))) AS Peor,

If you want this thing to work in sql server, you have to get rid of
these iif statements. Use CASE.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Bob Barrows [MVP]]

Ok!, i 've tried and now i don't get any syntax error, but when i
execute the query i get this one:

Error Message: Its not possible to use a agregate function with an
expression that contains an agregate or a subquery.

I am going to assume that you have verified that the first expression is the
problem one.

Actually, the error message is pretty much explaining what you are doing
wrong. There are certain things you simply cannot do.

now the string is:

SELECT TOP (100) PERCENT CatB, nCompleto, SUM(PBh) AS Parc,

Nothing to do with your problem, but you should get rid of that TOP 100
PERCENT. In older versions of SQL Server, using that would help you get
around the restriction against using ORDER BY in subqueries. They are
closing that loophole so there is no longer any point in including "top 100
percent" in your queries.

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

Hmmm ... this does not make any sense. A count() is going to return the same
result regardless of the result of these case expressions - No matter what
comes out of these CASE expressions, you are going to get a count of 1 for
each record in the group. Now if you were doing a SUM, then using an
expression here would make sense. I don't know your data so I cannot really
make any suggestions here. Perhaps if you showed a few rows of sample data
(relevant fields only) followed by the result you wish to achieve with this
query, I could be of more help.

 

[Juan Robledo]

Ok.
i have already resolve the problem creating a new view in sql with the worst
(MIN) score and the number of records (COUNT) taht point to each record of
the original query.

The access queries are much more flexible with this kind of complex strings.
Thank you,

Juan.