Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Sql Query

Re: Sql Query

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Mon, 20 Mar 2006 21:25:12 +0100
Message-ID: <441F0FA8.3080107@roughsea.com>


Sanjay,

    I was wondering if it is possible to do it in pure SQL and I am afraid I got carried out by the problem. Here is my entry for the 'Sick SQL of the Year' challenge :

SQL> variable param varchar2(1000)
SQL> begin
  2 :param :=
'Col1,111,Col2,AAAA,Col2,AB%,COL2,BBBB,Col3,23454,Col3,1212';   3 end;
  4 /

PL/SQL procedure successfully completed.

SQL> select decode(n,

  2                1, '     ',
  3                   decode(cnt, 1, ' and ',
  4                                  decode(r, 1, ' and (',
  5                                               '      or ')))
  6         || col || val
  7         || decode(cnt, 1, '',
  8                        decode(r, cnt, ')',
  9                                       ''))
 10  from (select rownum n,
 11               col,
 12               decode(lvl, 2, ' in (', op) ||
 13                      val || decode(lvl, 2, ')', '') val,
 14               count(*) over (partition by col) cnt,
 15               row_number() over (partition by col
 16                                  order by op)  r
 17        from (select col, op, val, lvl,
 18                     row_number() over (partition by col, op
 19                                        order by lvl desc) num
 20              from (select col, op, val, lvl
 21                    from (select level lvl,
 22                                 col,
 23                                 op,
 24                                 ltrim(sys_connect_by_path(val, ','), 
',') val
 25                          from (select col,
 26                                       rownum rn2,
 27                                       op,
 28                                       count(*) over (partition by 
col) cnt,
 29                                       rank() over (partition by col 
order by op) rnk,
 30                                       decode(valtype, 'A', '''', '') ||
 31                                           decode(valtype, 'A', 
replace(val, '''', ''''''), val)
 32                                          || decode(valtype, 'A', 
'''', '') val
 33                                from (select col,
 34                                             val,
 35                                             case instr(val, '%')
 36                                               when 0 then ' = '
 37                                               else ' like '
 38                                             end op,
 39                                             case 
nvl(length(rtrim(translate(val, ' +-.0123456789',
 40                                                                            
'**************'), '*')), 0)
 41                                               when 0 then 'N'
 42                                               else 'A'
 43                                             end valtype
 44                                      from (select max(case mod(rn, 2)
 45                                                         when 1 then 
lower(col)
 46                                                         else null
 47                                                       end) col,
 48                                                   max(case mod(rn, 2)
 49                                                         when 0 then col
 50                                                         else null
 51                                                       end) val
 52                                            from (select rn,
 53                                                         trunc((rn 
+1)/2) rn2,
 54                                                         substr(',' 

|| :param || ',',
55 instr(','
|| :param || ',', ',', 1, rn) + 1,
56 instr(','
|| :param || ',', ',', 1, rn + 1)
57 - instr(','
|| :param || ',',
58 ',', 1, rn) - 1) col 59 from (select rownum rn 60 from dual 61 connect by level <= 1 + length(:param) 62 - length(replace(:param, ',', '')))) 63 group by rn2 64 order by rn2))) 65 connect by nocycle col = prior col 66 and rn2 > nvl(prior rn2, 0) 67 and op != 'like') 68 where instr(val, '%') = 0 69 or lvl = 1)) 70 where num = 1)

 71 /

DECODE(N,1,'',DECODE(CNT,1,'AND',DECODE(R,1,'AND(','OR')))||COL||VAL||DECODE(CNT


     col1 = 111
 and (col2 in ('AAAA','BBBB')

      or col2 like 'AB%')
 and col3 in (23454,1212)

I don't mean that it is easy to maintain :-)

Stéphane Faroult

Sanjay Mishra wrote:

> Thomas
>  
> I tried the following and it worked
> CREATE OR REPLACE procedure gen_proc is
> arg varchar2(500);
> col varchar2(100);
> p_col varchar2(100) := 'test';
> val varchar2(100);
> where_con varchar2(500);
> outs varchar2(500) := 'select c1 from some_table where ';
> begin
> -- PARAMETER
> arg:='c1,v1,c2,v2,c3,v3,c3,v3%,c3,v4';
>  
>  
> arg:=arg||',';
> loop
> col := substr ( arg,1, instr(arg,',')-1);
> arg:=substr ( arg, instr(arg,',')+1);
> val:=substr ( arg,1, instr(arg,',')-1);
> arg:=substr ( arg, instr(arg,',')+1);
> if ( p_col != col ) then
> if ( where_con is not null ) then
> where_con := where_con || ') and ( ';
> else
> where_con := where_con || '(';
> end if;
> if ( instr(val,'%')=0 ) then
> where_con := where_con || ' ' || col || ' = ''' || val || '''' ;
> else
> where_con := where_con || ' ' || col || ' like ''' || val || '''' ;
> end if;
> else -- if col is repeated
> if ( instr(val,'%')=0 ) then
> where_con := where_con || ' or ' || col || ' = ''' || val || '''';
> else
> where_con := where_con || ' or ' || col || ' like ''' || val || '''' ;
> end if;
> end if;
> if ( arg is null ) then
> exit;
> end if;
> p_col:=col;
> end loop;
>  
> outs := outs || where_con || ')';
> dbms_output.put_line(outs );
> end;
> /
>  
> Sanjay
>
>
> */T homas Day <tomday2_at_gmail.com>/* wrote:
>
>     I'm no expert but this sounds like a job for dynamic SQL - or else
>     some programmatic solution.  Good luck and let us know what you
>     find in the manual. 
>
>
> ------------------------------------------------------------------------
> Yahoo! Mail
> Bring photos to life! New PhotoMail 
> <http://pa.yahoo.com/*http://us.rd.yahoo.com/evt=39174/*http://photomail.mail.yahoo.com> 
> makes sharing a breeze. 



--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 20 2006 - 14:25:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US