The first thing is, this really is something that would be better thrown to Oracle or a DBA/Developer within your company familiar with the tables/data, since
it’s not really a Toad specific question, but I can try to give you some ideas…
This is very hard to help with because we cannot see the data your working with. I doubt the below will work for what you need, but it’s a shot in the dark
without having the data to play with and you might have already tried something like this.
The best thing is to break down your requirements into small queries first and get familiar with the data, then you can worry about linking them together
after your getting the correct smaller subsets of data. Like you said, you assumed the records are on separate rows, but that makes a huge difference on how you attack this. I don’t like the idea of linking tables by a date field. The best solution is to
link them by a key (primary/foreign) first if you can and then narrow down the criteria (in your case the date) in the where clause.
I know it’s not really what you want to hear, but your question is pretty specific to your environment. 
select
clm_ssn,
clm_serv_strt,
clm_cost_ctr_nbr,
clm_recv_amt
from
ddis.pts_mv_clm_stat mv,
ddis. pts_mv_clm_stat
itema,
ddis. pts_mv_clm_stat
itemb
where
trunc(itema.clm_serv_strt)
trunc(mv.clm_serv_strt)
and
trunc(itemb.clm_serv_strt)
trunc(mv.clm_serv_strt)
and
trunc(itema.clm_serv_strt)
trunc(itemb.clm_serv_strt)
and
trim(itema.clm_cost_ctr_nbr)
‘5P311’
and
trim(itemb.clm_cost_ctr_nbr)
‘6R641’
and mv.clm_serv_strt
between
to_date
(‘01-07-2013’,‘dd-mm-yyyy’)
and
to_date(‘31-07-2013’,‘dd-mm-yyyy’)
From: steve.lundwall [mailto:bounce-stevelundwall@toadworld.com]
Sent: Friday, February 21, 2014 3:32 PM
To: toadoracle@toadworld.com
Subject: [Toad for Oracle - Discussion Forum] Toad newbie with EXISTS question
Toad newbie with EXISTS question
Thread created by steve.lundwall
Please forgive my naivety, I’m an Oracle SQL newbie using Toad. I have a table with claims records, DDIS.PTS_MV_CLM_STAT. It has records of individuals (by CLM_SSN) the date of a service(CLM_SERV_STRT)
and the service delivered (by CLM_COST_CTR_NBR). It’s an Mview actually of other tables with final sales status in it.
I am trying to construct a query to return CLM_SSN, CLM_SERV_STRT, CLM_COST_CTR_NBR, CLM_RECV_AMT if there is a service on the same day for that individual for both CLM_COST_CTR_NBR=A and CLM_COST_CTR_NBR=B
if between CLM_SERV_STRT 7/1/2013 and 7/31/2013. If this condition exists I want both records returned. I assume the two records would be on separate rows.
I’ve been researching IN, EXISTS, and sub queries but have yet to strike upon the right approach. The table has about 7 million records on it so I need something fairly efficient. I’ve got hte
following but it is not restricting it to just the two values in the WHERE statement. I’m getting all records for that time frame. Where am I going wrong?
SELECT CLM_SSN, CLM_SERV_STRT, CLM_COST_CTR_NBR, CLM_RECV_AMT
FROM DDIS.PTS_MV_CLM_STAT mv
WHERE EXISTS (SELECT 1 FROM DDIS.PTS_MV_CLM_STAT ItemA WHERE TRIM(ItemA.CLM_COST_CTR_NBR) = ‘5P311’
AND TRUNC(ItemA.CLM_SERV_STRT) = TRUNC(mv.CLM_SERV_STRT))
AND EXISTS (SELECT 1 FROM DDIS.PTS_MV_CLM_STAT ItemB WHERE TRIM(ItemB.CLM_COST_CTR_NBR) = ‘6R641’
AND TRUNC(ItemB.CLM_SERV_STRT) = TRUNC(mv.CLM_SERV_STRT))
AND mv.CLM_SERV_STRT BETWEEN TO_DATE (‘01-07-2013’,‘dd-mm-yyyy’)
AND TO_DATE (‘31-07-2013’,‘dd-mm-yyyy’)
ORDER BY CLM_SSN, CLM_SERV_STRT
Thanks everyone
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
407-515-4452