Been reading a bit on here and see where people have different ways to return the number of rows from a select statement. Maybe this will help someone who is struggling with SQL syntax
Here is another technique: We use this as the temp view created is based upon login name, gives each person a view of the data, and is updated as main tables are updated.
Keep in mind you can not insert, delete, or Update rows in views.
Two Subs slCreateView and slDropView, you would first check to see if view already exists
Getting the Rowcount for a select statement, once view is created you just use Select * or you can further limit your returns.
Here is another technique: We use this as the temp view created is based upon login name, gives each person a view of the data, and is updated as main tables are updated.
Keep in mind you can not insert, delete, or Update rows in views.
Two Subs slCreateView and slDropView, you would first check to see if view already exists
Code Select
SUB slCreateView(TempView AS STRING, slSelect AS STRING)
LOCAL slCmd AS STRING
slcmd = "Create View [" & TempView & "] as " & slSelect
slEXE(slcmd)
END SUB
SUB slDropView(TempView AS STRING)
LOCAL slCMD AS STRING
slCmd = "Drop View [" & TempView & "]"
slexe(slcmd)
END SUB Getting the Rowcount for a select statement, once view is created you just use Select * or you can further limit your returns.
Code Select
slCMD = "Select distinct sfolder from tdrive"
slCreateView("RRR.TEMP", slCMD)
slCMD = "Select count(*) as RowCount from [RRR.Temp]"
slsel(slcmd)
slgetrow
Rec = slFn("RowCount") <- you can return as val
'whatever else you need to do with this data
slDropView("RRR.TEMP")