Hi, I am trying to use the below SQL statement to retrieve the lowest number in a column of a table.
SELECT MIN(column_name) FROM table_name
Is this SQL statement usable in GeoSCADA or does it require different syntax?
Solved! Go to Solution.
That will work. The column needs to be numeric.
If you want to show more columns you would need a GROUP BY
MAX( ID ), NAME
F1 help contains a syntax guide to the SQL engine.
As Steve said, that's a valid SQL query.
But... given the subject of your question is 'GeoSCADA VB SQL', your question appears to leaving out the VB part (Visual Basic?.. important to note that GeoSCADA allows the use of VBscript in mimics... but NOT Visual Basic)... so I suspect there's more to it than you asked about.
Hi, the code runs untill MsgBox.
I am trying to get the value of "FirstRowTable" to display in a Msg Box.
Set objectTable = Server.FindObject("PoC.Web Service.Work Order.Work Order Table.XXXXX")
SQLStatement = "SELECT MIN(ColumnA) FROM XXXXX"
Set FirstRowTable= Server.Query(SQLStatement)
'Mimic.Layers(0).Item("Lst_1").Sql = Mimic.Layers(0).Item("Lst_1").Sql
When you say 'the value of "FirstRowTable" to display in a Msg Box'.. what do you think the data type of "FirstRowTable" will actually be? and how would that 'look' in a MsgBox?
I'll cut to the chase, it won't work. FirstRowTable is not a datatype that will EVER work as a parameter to a MsgBox. MsgBox is expecting a string. FirstRowTable is NOT a string. It is a ServerQuery object.
So.. what you should have, is something closer to the Server.Query example code in the help (below)
Set DGRS = Server.Query("SELECT MIN(ColumnA) FROM XXXXX")
If Not(DGRS.Error) Then
DGRows = DGRS.Rows
DGColNames = DGRS.ColumnNames
For c = 0 To (DGRS.ColumnCount - 1)
For r = 0 To (DGRS.RowCount - 1)
string = ""
For c = 0 to (DGRS.ColumnCount - 1)
string = string & DGRows(r,c) & " , "
Msgbox Cstr(r) & string
Msgbox DGRS.ErrorMessage, vbCritical + vbOKOnly, "Error in SQL Command"
You'll also want to take care around your use of XXXXX, the way that you've used them makes me suspect that you might think the SQL TableName for the DataTable IS the Name of the object in the ClearSCADA Database.
This is NOT the case.
The SQL TableName is the 'TableName' OPC property field of the DataTable / DataGrid, so you'd need to open up the Properties of the Table object to find it.
Hi, I replaced the string to TestResult to get the code to run. When the code is run a message box appears with "min(WorkOrderNo)". Press OK.
Then another message box with "Variable uses an automation type not supported in VBScript".
Referring to this line of code - TestResult = TestResult & DGRows(r,c) & " , ".
Better still, why not wrap the query into a class to give Record handling functions?
Add the following into a Script Library and reference that library from your script:
'******************************************************************************* Class ResultSet Public SQL Public QueryObj Private Dic Public CurRow Public Rows Sub Class_Initialize() Set Dic = CreateObject("Scripting.Dictionary") End Sub Public Sub Execute(SQL) Set QueryObj = Server.Query(SQL) ColNames = QueryObj.ColumnNames For i = 0 To QueryObj.ColumnCount - 1 Dic.Add LCase(ColNames(i)), i Next Rows = QueryObj.Rows CurRow = 0 End Sub Public Sub ExecuteS( myServer, SQL) Set QueryObj = myServer.Query(SQL) ColNames = QueryObj.ColumnNames For i = 0 To QueryObj.ColumnCount - 1 Dic.Add LCase(ColNames(i)), i Next Rows = QueryObj.Rows CurRow = 0 End Sub Public Property Get ColumnIndex(Name) 'If the column has a space, it is surrounded by quote marks. ColumnIndex = Dic.Item(Replace(LCase(Name),"""","")) End Property Public Property Get Fields(Name) If Not EOF Then ColIndex = ColumnIndex(Name) Fields = Rows(CurRow, ColIndex) Else Err.Raise vbObjectError + 1, "RecordSet", "EOF" End If End Property Public Property Get EOF() If CurRow = QueryObj.RowCount Then EOF = True Else EOF = False End If End Property Public Property Get BOF() If CurRow = 0 Then BOF = True Else BOF = False End If End Property Public Sub MoveNext() If Not EOF Then CurRow = CurRow + 1 Else Err.Raise vbObjectError + 1, "RecordSet", "EOF" End If End Sub Public Sub MoveFirst() CurRow = 0 End Sub Public Sub MoveLast() CurRow = q.RowCount End Sub Public Sub MovePrev() CurRow = CurRow - 1 If CurRow < 0 Then CurRow = 0 End Sub End Class
Now you can write code like this:
Set rs = New ResultSet sql = "SELECT ID, FULLNAME AS FNAME FROM CMimic" rs.Execute sql If rs.QueryObj.RowCount = 0 Then Msgbox "No Mimics found" Exit Sub End If Do While Not rs.EOF m = rs.Fields("FNAME") 'Do something rs.MoveNext Loop
Hi, if the SQL statement is changed to "SELECT * FROM WorkOrderData", then the code steps through the names of the columns.
Until it crashes. Thanks,
Hi, thanks Stephen and Bevan.
I appreciate your answers.
The solution to keep it simple, is to use the syntax as below.
This is suffice for what I need in my particular scenario.
Set DeleteMinRow= Server.Query("DELETE FROM WorkOrderData WHERE WorkOrderNo = (SELECT MIN(WorkOrderNo) FROM WorkOrderData) ")
You clearly need to work on how you ask questions.
Your problem was
"I am trying to use the below SQL statement to retrieve the lowest number in a column of a table."
"I am trying to get the value of "FirstRowTable" to display in a Msg Box."
and now you mark your own answer as 'The Solution' when it doesn't display a thing, doesn't retrieve the lowest number in a column of a table, doesn't display a MsgBox... so it doesn't solve the problem that you asked people for help with.
Hi Bevan, yes I will try to declare the ultimate goal next time in the first post.
I jumped a step thinking it would be give me the first step to resolving the ultimate goal.
A number of syntax combinations had been tested prior to the first post and unfortunately I had not found the correct syntax.
I couldn't get the code to work and give me the result I required.
If I get chance to return to it I will do so. However, at the moment I have to move on to my next task.
I will bear this in mind and give my ultimate goal in the next post, I post.