Home » SQL & PL/SQL » SQL & PL/SQL » tuning or refining query
tuning or refining query [message #401213] Sat, 02 May 2009 13:01 Go to next message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
hi
I have a table

col1  col2  col3  col4  col5  col6  col7
----- ----- ----- ----- ----- ----- -----
1     2      3     4      5    6    7
2     2      3     4      5    6    7
3     2      3     4      5    6    7
4     2      3     4      5    6
5     2      3     4      0    6
6     2      3     4      0    6    


using the previous data as an example my query should return
col1  col2  col3  col4  col5  col6  col7
----- ----- ----- ----- ----- ----- -----
4     2      3     4      5    6
5     2      3     4      0    6


as col7 is null
and any other column =5
by using the following query
SELECT *
FROM   mtest
WHERE  col7 IS NULL
       AND (col1 = 5
             OR col2 = 5
             OR col3 = 5
             OR col4 = 5
             OR col5 = 5
             OR col6 = 5) 


my table consists of 50 column
the "where condition" consist of 1 fixed column i.e: col7
and any of other column should have specific one value .

is there a method to tune or refine the "where condition "
to be written once not 49 times.

regards
thanks
Re: tuning or refining query [message #401214 is a reply to message #401213] Sat, 02 May 2009 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>is there a method to tune or refine the "where condition " to be written once not 49 times.
NO
icon7.gif  Re: tuning or refining query [message #401218 is a reply to message #401213] Sat, 02 May 2009 14:06 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SELECT *
FROM mtest
WHERE col7 IS NULL
and MOD(col1*col2*col3*col4*col5*col6*col7,5)=0

only if all other columns are not multiples of 5
Re: tuning or refining query [message #401220 is a reply to message #401213] Sat, 02 May 2009 14:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is there a method to tune or refine the "where condition "
to be written once not 49 times.

Is there a way to know if a room in a house contains 5 persons without looking in each of them?
Please answer my question.

Regards
Michel
Re: tuning or refining query [message #401222 is a reply to message #401220] Sat, 02 May 2009 15:05 Go to previous messageGo to next message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
thanks all

Michel Cadot wrote on Sat, 02 May 2009 14:27
Is there a way to know if a room in a house contains 5 persons without looking in each of them?
Please answer my question.


of course no way

perhaps I mis-use words

my aim is " instead of reassign the target value 49 times for 49 columns , is to be assign once for the 49 columns "
something like
...
where col50= target_value1
and 
(coll,col2,col3,..........,col49) in (target_value2)-- for example
...

instead of
SELECT *
FROM   mtest
WHERE  col50 target_value1
       AND     (col1 = target_value2
             OR col2 = target_value2
             OR col3 = target_value2
             OR col4 = target_value2
             ....
             ....
             OR col48 = target_value2
             OR col49 = target_value2)

ayush_anand wrote
SELECT *
FROM mtest
WHERE col7 IS NULL
and MOD(col1*col2*col3*col4*col5*col6*col7,5)=0


thanks for reply
unfortunately, values here are examples.
Re: tuning or refining query [message #401223 is a reply to message #401222] Sat, 02 May 2009 15:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select ename, sal, comm from emp where 1400 in (sal,comm);
ENAME             SAL       COMM
---------- ---------- ----------
MARTIN           1250       1400
TURNER           1400          0

2 rows selected.


Please always post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: tuning or refining query [message #401248 is a reply to message #401222] Sun, 03 May 2009 04:57 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Another way is

SELECT *
FROM mtest
WHERE col7 IS NULL
and decode(col1,target_value,0,1)*decode(col2,target_value,0,1)*decode(col3,target_value,0,1) = 0
Re: tuning or refining query [message #401259 is a reply to message #401213] Sun, 03 May 2009 07:50 Go to previous message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
thanks Michel Cadot
SQL> select ename, sal, comm from emp where 1400 in (sal,comm);

that is what I looked for.

I got your note about Test Case

thanks all
rgards
Previous Topic: SQL Operators - Datatypes allowed
Next Topic: Question
Goto Forum:
  


Current Time: Thu Dec 08 16:05:03 CST 2016

Total time taken to generate the page: 0.21752 seconds