error query sql

hudsonalves · November 10th, 2015, 08:29 AM

With this select I do various calculations. I have only used it in MYSQL works perfect.

Code:

select nome, ((select count(*) from JOGOS where timea=times.nome)+(select count(*) from JOGOS where timeb=times.nome)) as qtdejogo,(((select count(*) from JOGOS where timea=times.nome and resultadoa>resultadob)+(select count(*) from JOGOS where timeb=times.nome and resultadoa<resultadob))*3)+(((select count(*) from JOGOS where timea=times.nome and resultadoa=resultadob)+(select count(*) from JOGOS where timeb=times.nome and resultadoa=resultadob))*1) as ponto from times order by ponto desc

BD ACCESS, if I leave with the error of the ORDER BY POINT:

Code:

select nome, ((select count(*) from JOGOS where timea=times.nome)+(select count(*) from JOGOS where timeb=times.nome)) as qtdejogo,(((select count(*) from JOGOS where timea=times.nome and resultadoa>resultadob)+(select count(*) from JOGOS where timeb=times.nome and resultadoa<resultadob))*3)+(((select count(*) from JOGOS where timea=times.nome and resultadoa=resultadob)+(select count(*) from JOGOS where timeb=times.nome and resultadoa=resultadob))*1) as 'ponto' from times

What's wrong with this select?

mat41 · November 10th, 2015, 08:51 PM

shouldn't:

as 'ponto'

be:

as ponto

FYI your post is a bit unclear. In future you should post the actual error for the best help. Also what does BD ACCESS mean? I assume your query is not working in Access? If so, use the analyser, break it down, run your sub selects separate. Then build it up

hudsonalves · November 10th, 2015, 09:37 PM

Hello Matt, I'm using a database in access.

When insert this:

Code:

set rs = con.execute("select nome, ((select count(*) from JOGOS where TIMEA=times.nome)+(select count(*) from JOGOS where TIMEB=times.nome)) as qtdejogo,(((select count(*) from JOGOS where TIMEA=times.nome and resultadoa>resultadob)+(select count(*) from JOGOS where TIMEB=times.nome and resultadoa<resultadob))*3)+(((select count(*) from JOGOS where TIMEA=times.nome and resultadoa=resultadob)+(select count(*) from JOGOS where TIMEB=times.nome and resultadoa=resultadob))*1) as ponto from times order by ponto desc")

Generates error:

Code:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft] [ODBC Driver for Microsoft Access Driver] Too few parameters. They were expected 1.
/bia/tabela.asp, line 23

but when I insert in this way:

Code:

set rs = con.execute("select nome, ((select count(*) from JOGOS where TIMEA=times.nome)+(select count(*) from JOGOS where TIMEB=times.nome)) as qtdejogo,(((select count(*) from JOGOS where TIMEA=times.nome and resultadoa>resultadob)+(select count(*) from JOGOS where TIMEB=times.nome and resultadoa<resultadob))*3)+(((select count(*) from JOGOS where TIMEA=times.nome and resultadoa=resultadob)+(select count(*) from JOGOS where TIMEB=times.nome and resultadoa=resultadob))*1) as ponto from times order by 'ponto' desc")

Works but does not sort in decreasing order the column. Gets all scrambled.

mat41 · November 10th, 2015, 09:59 PM

I dont use Access and have not for many years. I do remember this:

Too few parameters. They were expected 1.

was thrown as a result of a field name issue. field doesn't exist from memory.

The best way to resolve this type of issue is to do what I told you to in my last post. Strip your query back, run it, add another piece, run it, etc..... then you will find the bit that is problematic.

Another question I asked in my first post , shouldnt:

order by 'ponto' desc

be

order by ponto desc

hudsonalves · November 12th, 2015, 06:56 AM

Hello Matt, how are you?

I tested the select the MS Access as follows:

http://www.hudsonalves.com/mercadolivreimg/print1.png

When running SQL him behind the table but does not order the column "ponto":

http://www.hudsonalves.com/mercadolivreimg/print2.png

mat41 · November 12th, 2015, 07:24 PM

HI there. I am well thank you.

I keep asking why do you surround ponto in single quotes, IMO this is not necessary and not recommended. This said its not your solution. It must be your datatype, what is the data type. Based on your output surely it cant be a number?

hudsonalves · November 12th, 2015, 07:52 PM

Hello Matt,

I noticed the following in MYSQL I can turn the end of the passage this way:

Code:

as ponto from times order by ponto desc"

The system not of error and can arrange in descending order.

In MS Access it does not work well, so no date error if I use this:

Code:

as ponto from times order by 'ponto' desc"

In this system I have to use MS Access and I'm not really finding solution to this problem.

You mentioned the output be applied with number, no problem since the organization in descending order not to work. The code I posted you think of to improve something?

mat41 · November 12th, 2015, 08:07 PM

regarding your ordering issue, my question was what is the SQLServer column data type for the ponto field?

hudsonalves · November 12th, 2015, 09:04 PM

The column point collects numbers. really add the result of games. is it possible for me to include the access base in here to see you?

hudsonalves · November 13th, 2015, 07:30 AM

Hello Matt,

If it is not asking too much, you could take a look at MS Access? Follow the link to download.

I tested it in the SQL editor

http://www.hudsonalves.com/mercadolivreimg/jogos.zip