Re: Sql Query
- From: Sanjay Mishra <smishra_97@xxxxxxxxx>
- To: sfaroult@xxxxxxxxxxxx
- Date: Tue, 21 Mar 2006 07:54:18 -0800 (PST)
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@xxxxxxxxxxxx> 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.
--
//www.freelists.org/webpage/oracle-l
---------------------------------
Brings words and photos together (easily) with
PhotoMail - it's free and works with Yahoo! Mail.
Other related posts:
