SQL Syntax

[ScottInTexas]

I have to display a row of data that is developed from the result of division of the data in two columns in my DB. I put this in a 2 dim array and then pass it to the charting rotuine. I want the array in order. But I can't stand the thought of writing a sort routine! I haven't had to do that since GW-Basic!

I need an SQL that can do it but don't know enough of that to write it. My result should be a name in ary(0,0) and column2/column1 in ary(0,1) If Column1 <> 0 or Column2 <> 0. As it is now I just read the data, check the values and if they are not 0 then divide and store the results in an array that doesn't guarantee the order.

Something Like "Select Name, (hrs/dollars) as result Oder By Result"

Thanks for looking.

[Roy Sinclair]

Since you're posting in the ASP forum I'll assume you're using ADO to get the results. You can resort the recordset like this:

recordSetName.sort = "fieldname1"

If you want to sort on multiple fields just separate the fieldnames by spaces.

[ScottInTexas]

Thanks for your response.

The division of field1/field2 does not return a new recordset that can be sorted. I can open the recordset with ORDER BY Field1 but this only makes the highest value of field1 the first (or last depending on sort order), it wont do anything for the resulting value. Once I have the recordset and I have to use the fields all I have left is the result of the division, not a new field.

SQL="Select field1, field2 From Table Order by field1"

MyValue=Field2/field1

Use MyValue in an array.

[Roy Sinclair]

You didn't understand. You can already get the recordset containing your new value as a field in that recordset. Once you have the recordset you can re-sort the records in the recordset using the sort method of the ADO Recordset object and sort on any field including the one you created using the "field/field as newfield" calculation. Using that option you can re-sort a recordset several times if you need to, a very useful thing to know since you don't have to run the query back to the database again using different sql.

[ScottInTexas]

OK Roy,

I didn't understand. I kept getting erros in my SQL when I tried to do the division during the actual opening of the recordset. Since I couldn't find anything with division in the references under aggregate data I thought it couldn't be done that way.

Long story short - I got the SQL working, now I have a recordset I can sort. Thanks for your help.

[ScottInTexas]

Alright, SQL Works great (in Access) now why does this cause an error?


'Separate function
Set connxion = Server.CreateObject("ADODB.Connection")
connxionStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("/database/LHC_Perf_Metrics.mdb") & ";"
connxion.Open(connxionStr)
GetConn=connxion
.
'Get a connection
DBConn=GetConn
.
.
.
SQL="Select PlantID, (ACE/RAB) as ResultField FROM Production WHERE (YearID=" & theYear & " AND (ACE > 0) AND (RAB > 0) AND Quarter=5);"
.
RS.CursorType=1
RS.Open SQL, DBConn
.
RS.Sort="ResultField " & DataOrder Line 145



The Error I get is

Object or provider is not capable of performing requested operation.

/GetChart.asp, line 145

[raf]

why don't you simply include an 'order by resultfield' clause in the sql statement ?

Anyway, shouldn't the code be

RS.Sort="ResultField DESC"

[ScottInTexas]

I get an error "No value given for one or more parameters when I try the ORDER BY clause in my SQL. When I put the SQL in Access and test it a dialog box pops up asking for a value for ResultField.

shouldn't the code be RS.Sort="ResultField DESC"

That's what I have! RS.Sort="ResultField" the '& DataOrder' is a variabel which says either "ASC" or "DESC" depending on which data the user chooses.

This is extremely frustrating because I have spent all morning looking this up and nothing I have read works. There is something missing in one of my statements, but I'll be darned if I can find it

[ScottInTexas]

It's fixed! I have finally got everything in it's proper place and syntax.

My problem was in the fact that I was using vbScript constants that were not defined (adUseClient, etc). When I used the numeric values and assigned the right values to the right parameters the sort worked perfectly. My thanks to those of you who posted responses. Below is the finished work in case someone else needs the answer.



Function GetConn()
Dim conn

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.MapPath("/MyDatabase"))
GetConn=conn

End Function
.
.
.
DBConn=GetConn
Set RS=Server.CreateObject("ADODB.recordset")
RS.CursorLocation=3
RS.CursorType=3
RS.LockType=4

SQL="Select PlantID, (ACE/RAB) as ResultField FROM Production WHERE (YearID=" & theYear & " AND (ACE > 0) AND (RAB > 0) AND Quarter=5);"

RS.Open SQL, DBConn
.
.
.
If ValueField="Calculate" Then
RS.Sort="ResultField " & DataOrder
End If