Re: Sql Query

From: Sanjay Mishra <smishra_97_at_yahoo.com>
Date: Tue, 21 Mar 2006 07:54:18 -0800 (PST)
Message-ID: <20060321155418.39380.qmail@web51303.mail.yahoo.com>

Stephane

You are really great as this is so complex that I need time to understand it. Thanks for your time and efforts

Sanjay

Stephane Faroult <sfaroult_at_roughsea.com> wrote: 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 /* 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 
> 
> makes sharing a breeze.

--
http://www.freelists.org/webpage/oracle-l




		
---------------------------------
Brings words and photos together (easily) with
 PhotoMail  - it's free and works with Yahoo! Mail.
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 21 2006 - 09:54:18 CST