PDA

View Full Version : Dynamic ORDER BY


rico1931
06-02-2009, 06:01 PM
Hello folks,

I'm trying to do a dynamic ORDER BY using DW and I keep coming up with this error
"
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. "

So I did some google searching and I found that I would have to use a CASE statement to achieve what I really want. Now I'm no expert in SQL but if I have to go write it I will I was just wondering if anyone else out there found out an easier way to do it using DW recordset option?

edbr
06-03-2009, 01:35 AM
what do you want to order by? 1 doesnt make much sense, order by column name ascending or descending, if you use a value the closest would be (in DW) order by entered value

rico1931
06-03-2009, 01:38 AM
right but I want it to be dynamic and take a col name but DW doesn't allow you to do ORDER BY 'parm from URL' in the recordset option

edbr
06-03-2009, 01:48 AM
no , what does parm from URL' mean? im not clear what you want. sorry its early here :)

edbr
06-03-2009, 02:00 AM
if you meant pasing a value from a url ( im waking up a bit ) you need to pass the value from a link such as actionpage.php?id=1
then in your record set use order by id (url)

rico1931
06-03-2009, 02:02 AM
ok so you have a SQL statement
[code]
SELECT USER_ID, USER_Fname
FROM table A
ORDER BY sort
[code]

Now 'sort' is a URL parameter so when a user goes to mypage.com
and click on a header like Fname it will pass the parm mypage.com?sort=Fname and by default its always ASC

haha and Good Morning!

rico1931
06-03-2009, 02:03 AM
Exactly! that's what I mean... Wow got a cup of coffee that fast huh? haha but I get this SQL error
The SELECT item identified by the ORDER BY
number 1 contains a variable as part of the expression
identifying a column position. Variables are only allowed
when ordering by an expression referencing a column name. "

rico1931
06-03-2009, 02:05 AM
wow 9am over there.. Must be beautiful there too!

edbr
06-03-2009, 02:10 AM
ok in your statement you select USER_ID, USER_Fname but you are asking it to order by sort.
the value is read by $_GET['sort']; which will return the value that is passed by mypage.com?sort=Fname in this case Fname

rico1931
06-03-2009, 01:55 PM
right thats what I have in my SQL but since i'm using ASP...don't ask why... its Request.QueryString("sort") but is this right?


SELECT *
FROM dbo.USERS_CURRENT
ORDER BY sort
Something above is wrong because I keep getting this error


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][SQL Server Native Client 10.0][SQL Server]The SELECT item identified by the ORDER BY

number 1 contains a variable as part of the expression identifying a column position.

Variables are only allowed when ordering by an expression referencing a column name.



Also it tells me my error is on this line


Set rsCONTROL = rsCONTROL_cmd.Execute




So it def doesn't like that 'sort' parameter in there for some reason

rico1931
06-03-2009, 02:22 PM
Screw it I just cheated and made a form then did a drop down list to handle the sort function. Time is money and I need to start producing results! thanks!