Dynamic SQL statement in Datawindow 1

[golyg]

Hi all,
I am attempting to display a datawindow that will have a SQL statement behind it and this datawindow will be passed a string. This string is what the user wanted to order the results by.
for example,
SELECT *
FROM table_a, table_b
WHERE table_a.speed = table_b.speed
ORDER BY :sg_SortChoice

the problem is that it seems the variable sg_SortChoice is being treated as a string(I obviously want the variable a string), rather than what the string represents. so it ignores it.
If I use this query,
Select *
FROM table_a, table_b
WHERE table_a.speed = table_b.speed
ORDER BY 'speed'

it total ignores my order by statement

any suggestions?

 

[rt63]

Consider sorting the result set on the front end

setredraw(false)
dw.retrieve()
dw.setsort("colname A&quot
dw.sort()
setredraw(true)

Another option could be to use a procedure to fetch records instead of the select statement and pass the orderby field as a paremeter (retrieval argument). So you will need to define the datawindow as data source type procedure etc. The procedure could be on the following lines(assuming SQL)

create procedure sp_out @order varchar(100)
as
declare @sql varchar(1000)
set @sql = 'Select * FROM table_a, table_b WHERE table_a.speed = table_b.speed order by '+@order
execute(@sql)





RT

 

[mbalent]

I would recommend the sorting option as described in the other reply but if you insist on changing the sql sent to the database, do so in the sqlpreview event of the datawindow.

 

[golyg]

Thanks to both for the replies,
I am going with the sort option,
I am actually passing 2 values in setsort() hoping to treat the first as a trailer and then sort with the second.
it works with a few and then for some reason it is "getting confused" with some others.

Thanks again,