If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Transform Access SQL to SQL server 2005 or SQLExpress
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. |
#2
|
|||
|
|||
Transform Access SQL to SQL server 2005 or SQLExpress
Juan Robledo wrote:
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" |
#3
|
|||
|
|||
Transform Access SQL to SQL server 2005 or SQLExpress
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 "Bob Barrows [MVP]" escribió en el mensaje de noticias ... Juan Robledo wrote: 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" |
#4
|
|||
|
|||
Transform Access SQL to SQL server 2005 or SQLExpress
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" |
#5
|
|||
|
|||
Transform Access SQL to SQL server 2005 or SQLExpress
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. "Bob Barrows [MVP]" escribió en el mensaje de noticias ... 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" |
#6
|
|||
|
|||
Transform Access SQL to SQL server 2005 or SQLExpress
Juan Robledo wrote:
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. "Bob Barrows [MVP]" escribió en el mensaje de noticias ... 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" |
#7
|
|||
|
|||
Transform Access SQL to SQL server 2005 or SQLExpress
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 "Bob Barrows [MVP]" escribió en el mensaje de noticias ... Juan Robledo wrote: 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. "Bob Barrows [MVP]" escribió en el mensaje de noticias ... 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" |
#8
|
|||
|
|||
Transform Access SQL to SQL server 2005 or SQLExpress
Juan Robledo wrote:
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. -- 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" |
#9
|
|||
|
|||
Transform Access SQL to SQL server 2005 or SQLExpress
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. "Bob Barrows [MVP]" escribió en el mensaje de noticias ... Juan Robledo wrote: 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. -- 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" |
Thread Tools | |
Display Modes | |
|
|