How to solve the mentioned issue with a single query [message #334851] |
Fri, 18 July 2008 06:30  |
rbhatia07
Messages: 7 Registered: February 2008 Location: Kolkata
|
Junior Member |
|
|
Hello All,
Please help me to solve the below mentioned issue.
I am having
one master table named "Appl_master" containing one field "Appl_name"
and
One Transuction table named "Appl_trans" having one field "Appl_affected" containing appl_names in comma seperated format.
like
Table1 - Appl_master
Appl_name
APPL1
APPL2
...
...
APPL10
Table2 - Appl_trans
Appl_affected
APPL1,APPL10
APPL2
My Concern is to count total number of applications affected using single query.
I tried for
select count(*) from appl_master where appl_name in (
select appl_affected from appl_trans)
And get 1 as a result for APPL2
But i need count=3 for APPL1,APPL2,APPL10
What exactly Oracle server do
It first execute the inner query and make all the records concated.Hence i get
'APPL1, APPL10', 'APPL2'
And after that the upper query executes like
select count(*) from appl_master where appl_name in ('APPL1,APPL10','APPL2')
check 'APPL1,APPL10' as single string it will give count 1
Even i try to use replace function in that upper query like
select count(*) from appl_master where appl_name in (
select replace(appl_affected,',',''') from appl_trans)
even then i get count=1 (instead of 3)
Please help me out, what exactly i will use to get the desired result
|
|
|
|
|
|
|