Re: New query binds won't execute

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/01
Message-ID: <8ek6bd$7u4$1@nnrp1.deja.com>#1/1

In article <8ed16p$s1e$1_at_nnrp1.deja.com>,   jwainz_at_my-deja.com wrote:
> Using Microsoft ADO I created a prepared command. This creates a bind
> in Oracle. It works fine and is listed below:
>
> SELECT * FROM daily_total_display WHERE lgon_id = :V001
>
> Due to requirements I needed to add an ORDER BY to the command, so I
> changed the commandtext and run the app. The new query appears in
> v$sqltext and v$sqlarea as listed below:
>
> SELECT * FROM daily_total_display WHERE lgon_id = :V001 ORDER BY :V002
>
> When I run the app it still uses the 1st query even though I've made
> all the proper changes. It even threw errors initially when I forgot
 to
> fill the extra parm inside the ADO command. Any ideas how to delete
 the
> first command, or why it won't use the second command?
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

What is the difference between these 2 queries? (it is using the second query -- its just that the second query isn't any different then the first).... Lets say V001 has a value of 'X' and V002 has a value of 'Y'. The queries:

SELECT * FROM daily_total_display WHERE lgon_id = 'X'

and

SELECT * FROM daily_total_display WHERE lgon_id = 'X' order by 'Y'

aren't really any different? you are ordering by a CONSTANT (whatever value is in V002 when the query is executed). You aren't ordering by anything.....

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).

For example:

scott_at_8i> variable bv varchar2(25)
scott_at_8i>
scott_at_8i> exec :bv := 'DEPTNO'

PL/SQL procedure successfully completed.

scott_at_8i>
scott_at_8i> select * from dept

  2   order by decode( :bv, 'DEPTNO', to_char(deptno,'fm0000000009'),
  3                         'DNAME', dname,
  4                         'LOC', loc )

  5 /

    DEPTNO DNAME LOC
---------- -------------- -------------

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

scott_at_8i> exec :bv := 'DNAME'

PL/SQL procedure successfully completed.

scott_at_8i> select * from dept

  2   order by decode( :bv, 'DEPTNO', to_char(deptno,'fm0000000009'),
  3                         'DNAME', dname,
  4                         'LOC', loc )

  5 /

    DEPTNO DNAME LOC
---------- -------------- -------------

        10 ACCOUNTING     NEW YORK
        40 OPERATIONS     BOSTON
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO






--
Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html

--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Mon May 01 2000 - 00:00:00 CDT