Sorting on the difference between two fields 1

[liark]

I have two fields in a table..... in my select statement, I'd like to select a variable as THE DIFFERENCE between these two fields. Is this possible?

i.e.

SELECT (abc - def) AS ghi FROM table
WHERE....

this is the code:

<cfquery name="gettopmovers" datasource="abc" maxrows=10>
	SELECT surname, (lws - tws) as topmove from punters
	WHERE openfield > 0
	ORDER by topmove DESC
</cfquery>

as a result, I get

Error Diagnostic Information
ODBC Error Code = 07001 (Wrong number of parameters)


[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.


The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (2:4) to (2:64).

The fields do exist in the table.

Thanks for your help.


Liark

 

[r937]

yes you can use a column alias in the SELECT, the problem is, in microsoft access you cannot use the alias in the ORDER BY

SELECT surname
     , (lws - tws) as topmove 
  from punters
 WHERE openfield > 0
ORDER 
    by (lws - tws) DESC

r937.com | rudy.ca

 

[liark]

Brilliant. Thanks r937.

Liark