Using User Input parameters in an order by clause

Kimberly_Brennan_(lockmaruser2_-_(deleted))

It takes it fine, but does not sort properly

Find more posts tagged with

Comments

Bob_De_Garmo_(degarmob_(Delete)_2518944)

User Input Parameters are most commonly used as limiting factors in the SQL's "Where Clause" and are literal values; not database table columns. An "Order By" clause expects a database column name to sort by. When you insert a User Input Parameter place holder (%1), the retrieved data will, in essence be sorted by a singular interpretted value. i.e. "Order By 'ABC123'"Hope that makes sense...

Bob_De_Garmo_(degarmob_(Delete)_2518944)

Does this instance use an Oracle DB? Other DBs probably have a similar construct, but in Oracle what you potentially could do is use the "Decode" statement in the "Order By" clause. Just for fun, I tried the following, and it works fine:~~~~~~~~~~~~~~~~~~~~~~~~~~~select kuaf1.lastname || ', ' || kuaf1.firstname "User Name", kuaf2.name "Department" from kuaf kuaf1, kuaf kuaf2 where kuaf1.deleted = 0 and kuaf1.groupid = kuaf2.id order by decode(%1, 'Name', kuaf1.lastname, 'Dept', kuaf2.name, kuaf1.lastname)~~~~~~~~~~~~~~~~~~~~~~~~~~~In this case << perhaps not the best example >> the user would enter the parameter 'Name' to sort by Last Name, or 'Dept' to sort by Department. If anything else is entered as the parameter, it defaults to Last Name.Does that give you ideas/direction?

Marita_Ventura_(DCAdmin_(Delete)_2237520)

Try this.select * from DTREE where subtype=202 order by %1select userinput1 as param1 and string as type & give some prompt. This worked for me.