Home » SQL & PL/SQL » SQL & PL/SQL » Logical equivalence (10g r2)
Logical equivalence [message #439210] Thu, 14 January 2010 23:05 Go to next message
hungman
Messages: 16
Registered: September 2006
Junior Member
Hi,

wondering if you guys could give me a hand with this bit, been struggling to find a logical equivalence or a valid query for this. If someone could rewrite this example query in a valid form:

select  t1.col1, 
        t2.col2
from    t1,
        t2
where   t1.col3 = t2.col4(+)
and     t1.col5 = case
                    when :param is null then t2.col6(+)
                    else t2.col6
                  end


Ok, so what I'm trying to achieve is this: if a user doesn't enter a value, then retrieve all records from t2 (essentially keep the outer join), however if the user does enter a value, then only retrieve records that match up t1 with t2.

Hope that makes sense.

thanks in advance.
Re: Logical equivalence [message #439216 is a reply to message #439210] Thu, 14 January 2010 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> if a user doesn't enter a value
Most users don't directly interact with SQL & the DB.

This would appear to be a great candidate for SQL injection.

> if a user doesn't enter a value
I suggest a simple IF... THEN ... ELSE with 2 valid SELECT via the user interface
Re: Logical equivalence [message #439373 is a reply to message #439210] Fri, 15 January 2010 23:55 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
In the old days we used what is called manually partitioned views for this. You don't really need a view, just a query that uses union all and takes advantage of a well understood optimization strategy. Consider this:

select *
from t1,t2
where t1.key = t2.key(+)
and :param is null
union all
select *
from t1,t2
where t1.key = t2.key
and :param is not null

As you can see, because of the paramer being tested in each query, only one or the other select can return data. This makes them mutually exclusive.

The trick is that the oracle optimizer understands this. It understands that only one of these select will ever return data and it understands that it can figure this out before it actually executes the query (eg. during parse time) and thus will only need to execute one or the other. Thus you don't suffer the cost of executing both queries. It is a form of short circuiting.

Good luck, Kevin
Re: Logical equivalence [message #439382 is a reply to message #439216] Sat, 16 January 2010 02:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
BlackSwan wrote on Fri, 15 January 2010 06:31
This would appear to be a great candidate for SQL injection.

:param being a bind variable will make it safe from SQL injection.
Re: Logical equivalence [message #439538 is a reply to message #439210] Mon, 18 January 2010 03:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As far as I can tell, you can't do a conditional outer join in the CASE statement.
You can fake up an outer join though:
create table test_135(col_1 number,col_2 number);

create table test_136(col_1 number, col_2 number,col_3 number);

insert into test_135 select level,level from dual connect by level <=5;

insert into test_136 values (2,2,99);
insert into test_136 values (4,4,99);

commit;

select  t1.col_1, 
        t1.col_2,
        t2.col_3
from    test_135 t1,
        test_136 t2
where   t1.col_1 = t2.col_1(+)
and     t1.col_2 = case when :param is null then nvl(t2.col_2,t1.col_2)
                        else t2.col_2
                   end;
Re: Logical equivalence [message #439638 is a reply to message #439210] Mon, 18 January 2010 14:33 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
select  t1.col1, 
        t2.col2
from    t1,
        t2
where   t1.col3 = t2.col4(+)
and ((:param is null and t1.col5 = t2.col6(+))
     or (:param is not null and t1.col5 = t2.col6))
Previous Topic: sql and pl/sql
Next Topic: query to get first and last values (merged)
Goto Forum:
  


Current Time: Sat Dec 10 17:01:16 CST 2016

Total time taken to generate the page: 0.11840 seconds