Home » SQL & PL/SQL » SQL & PL/SQL » How to solve the mentioned issue with a single query
How to solve the mentioned issue with a single query [message #334851] Fri, 18 July 2008 06:30 Go to next message
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


Re: How to solve the mentioned issue with a single query [message #334853 is a reply to message #334851] Fri, 18 July 2008 06:34 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Drop your tables and start redesigning.
Don't store multiple values in a single column.
Re: How to solve the mentioned issue with a single query [message #334854 is a reply to message #334851] Fri, 18 July 2008 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Also please read OraFAQ Forum Guide and search before posting, this has been answered a couple of hours ago.

Regards
Michel
Re: How to solve the mentioned issue with a single query [message #339189 is a reply to message #334853] Thu, 07 August 2008 00:48 Go to previous messageGo to next message
rbhatia07
Messages: 7
Registered: February 2008
Location: Kolkata
Junior Member
I can't drop/redesign that table.
I have to use the same table with the field having comma seperated values.
Re: How to solve the mentioned issue with a single query [message #339190 is a reply to message #339189] Thu, 07 August 2008 00:58 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
rbhatia07 wrote on Wed, 06 August 2008 22:48
I can't drop/redesign that table.
I have to use the same table with the field having comma seperated values.


So proceed so do so.
Previous Topic: using order by clause in create view
Next Topic: need solution
Goto Forum:
  


Current Time: Thu Feb 06 16:56:29 CST 2025