What wrong with this code [message #36218] |
Mon, 12 November 2001 01:12 |
Kashi Ram
Messages: 2 Registered: November 2001
|
Junior Member |
|
|
Sir
I am unable to understand the logic behind what I am getting which is as
follows:
Part1
sql> select typ from dummy where typ like 'P1G'
2 and rownum<2;
Typ
-------
P1G
Part 2
sql>select ''''||replace(typ,',',''',''')||'''' typ from
dummy1 where instr(typ,'P1G')<>0 and rownum<2
TYP
----------------------------------------------------------------------------
----
'DRG','P1G','PIG','SI1','SINL','TI1'
Part 3
sql> select typ from dummy where typ like 'P1G'
2 and rownum<2 and typ in
3 ( select ''''||replace(typ,',',''',''')||'''' typ from dummy1
4 where instr(tpy,'P1G')<>0 and rownum<2)
5 /
no rows selected
Part 4
sql>select typ from dummy where typ like 'P1G'
2 and rownum<2 and typ in
3 ('DRG','P1G','PIG','SI1','SINL','TI1' )
4 /
TRXN_TY
-------
P1G
Why is there no row selected in Part3 when my subquery is going to return
the same result
as the one in part4
If u could throw some light on the flaw,It would be well appreciated
Thanks in advance
Regards
Kashi Ram
----------------------------------------------------------------------
|
|
|
Re: What wrong with this code [message #36240 is a reply to message #36218] |
Mon, 12 November 2001 14:06 |
oraboy
Messages: 97 Registered: October 2001
|
Member |
|
|
Becoz your 3rd query would check for string PIG whereas the subquery would return values enclosed with single quotes like 'PIG'
Thats why you dont get a match
by the way, hope you know you are using LIKE operator for no use( without % or _ ) and there is no need for single quotes generation for string comparison
Oraboy
----------------------------------------------------------------------
|
|
|