>The stored procedure seems to work fine, but the dynamic ORDER BY withing
>does not (eg.,):
[snip]
>What am I missing?
I had a similar problem myself, on Oracle 8i. A quick post to the
c.d.o.misc newsgroup, and Tom Kyte (the God of Oracle, IMHO) provided the
following:
[...begin excerpt...]
> If you are trying to dynamically order based on a bind variable (you
> cannot parameterize an order by with a bind variable) you can try using
> decode(), eg:
>
> select * from T where x = :bv
> order by decode( :another_bv, 'C1', c1, 'C2', c2, 'C3', c3, ... )
>
> this works if all of C1, ... CN are the same type (so the decode
> returns all CHAR or DATE or NUMBER). If it does not, you'll have to
> convert each type into something type comparable so they can sort
> correctly (the decode will return only 1 type of return type -- it
> won't return a number on one call and a string on the next call --
> it'll always return a string if it can return both a number and a
> string).
[...end excerpt...]
I'm not sure whether DECODE() is peculiar to Oracle, or whether that's a
general SQL thing (the SP code you included in your question wasn't
Oracle-ish...); but for the record, the cursor ends up looking something
like this:
CURSOR testCur(sortBy IN VARCHAR2) IS
SELECT fooVal1, fooVal2, fooVal3
FROM fooTable
ORDER BY DECODE(
sortBy,
'col1',fooVal1,
'col2',fooVal2,
'col3',fooVal3);
Thus, passing the cursor 'col1' as a variable orders it by fooVal1, passing
'col2' orders by fooVal2, etc.
Being trapped in PL/SQL hell for the last few months, I haven't had the
opportunity to play around with the CFStoredProc tag, but one question comes
to mind; if you're not returning the columns in the same way every time,
could that affect the operation of the tag?
Answers & questions...
Brandon Whitaker
[EMAIL PROTECTED]
-------
"It'll get used by the same people using Opera. People dressed in black
wearing berets."
- Dave Watts, on Mozilla
"The net interprets censorship as damage and routes around it."
- John Gilmore
"I'm sorry, I can't hear you. God just sat on my Whoopee Cushion."
- Kibo
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
