Re: Sql Query
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: smishra_97@xxxxxxxxx
- Date: Mon, 20 Mar 2006 21:25:12 +0100
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@xxxxxxxxx>/* 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
