Toad newbie with EXISTS question

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

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. :slight_smile:

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.

Just be careful with the trunc() statements because you can lose the benefit of your indexes unless you have an index that specifically does a trunc on that column.

The query Dennis gave you should be doing the same as your exists query. If you are still getting too many rows, you should probably look to see if there is another field you should be joining on.

On Fri, Feb 21, 2014 at 4:22 PM, Dennis Paulus bounce-Dennis_Paulus@toadworld.com wrote:

RE: Toad newbie with EXISTS question

Reply by Dennis Paulus
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. :slight_smile:

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.

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.

--
Phyllis Helton

BI MIddleware Engineer
Digital Strategies, Cru | Insight Team

Office :phone: 407-515-4452

phyllis.helton@cru.org

Yeah, if you don’t need all those TRIM’s and TRUNCs, then take them out. That could solve your problem right there.

I’m not working with SQL every day and I just happened to look at this post by hazard.

ddis.pts_mv_clm_stat itema,

ddis.pts_mv_clm_stat itemb

This is the same table, so we can use itema all over the place and get rid of itemb. Hence

and trim(itema.clm_cost_ctr_nbr) = ‘5P311’

and trim(itemb.clm_cost_ctr_nbr) = ‘6R641’

would become

and trim(itema.clm_cost_ctr_nbr) = ‘5P311’

and trim(itema.clm_cost_ctr_nbr) = ‘6R641’

which would never be true at the same time, hence probably no output at all. Steve wrote “…if there is a service on the same day for that individual for BOTH …” so we can’t AND both predicates. Steve could also have used ItemA in his second EXISTS as well, as that item is local to the select inside that EXISTS.

It doesn’t feel right …

Thanks, everyone. I just got back to this subject this morning. The weekend was spent putting un a new driveshaft in the truck. :slight_smile:

Dennis, I’d really like the chance to throw this to a DBA or Oracle developer in the organization - if there were any. We have one DBA who is swamped on other things (keeping he bills paid in a $750 million a year organization). We have a couple of ‘developers’ that aren’t worth wasting time on and I’ve been waiting on a particluar report from our one and only Crystal Developer for over a year. So, although I’m not a developer or a DBA I decided to tackle this kind of question myself from now on. I got access to the Test system (which they simply update occasionally) and I’m going to figure out how to answer the kinds of questions that are coming up.

Sorry if I confused you on two other things. First, I’m only dealing with one table. There aren’t two tables to link unless I alias the one I have. And second, I know the data quite well. What I meant to say was that I assuemd the query would return the two seperate rows rather than return the two records I’m looking for on the same row. Sorry for the confusiuon.

Now on to the suggestions. I’ll give your examaple a try. I love learning this lin of thing - kind of an ‘Ol’ dog - new trick’ think going on here. Learning keeps one youthful in my mind.

Phyllis and John, thanks for hte heads up on that. Once I get data returned the way I need I may have to optimize a bit. Unfortunatley, the data is dirty enough that I have to trim and trunc a bunch. There are several old FoxPro systems feeding the Oracle tables through a nightly upload process. The data integrity and consistancy issues are a true battle.

Andre, You’re hiting on some of my confusion as well. Basically, I want to link a table to iteslf using SSN and Date looking for a couple of values. Seems simple enough. I’m finding out it truely isn’t.

Thanks everyone for the input. I’ll post back as I work on this today for a while - then I have to take off ofr a short business and won’t get back to it for a couple of days. Thanks again. Looks like I found a gret community to interacti with!

Steve

Hey Steve,

If all you’re looking to do is return the rows where CLM_SSN and CLM_SERV_STRT are the same, but have different CLM_COST_CTR_NBR values, you can also try the following, which is a variation on Dennis’s suggestion above. This should return the records in two separate rows, rather than one:

select mv1.clm_ssn,
mv1.clm_serv_strt,
mv1.clm_cost_ctr_nbr,
mv1.clm_recv_amt
from ddis.pts_mv_clm_stat mv1,
ddis.pts_mv_clm_stat mv2
where mv1.clm_ssn = mv2.clm_ssn
and trunc(mv1.clm_serv_strt) = trunc(mv2.clm_serv_strt)
and trim(mv1.clm_cost_ctr_nbr) <> trim(mv2.clm_cost_ctr_nbr)
and trim(mv1.clm_cost_ctr_nbr) in (‘5P311’, ‘6R641’)
and mv1.clm_serv_strt between to_date (‘01-07-2013’,‘dd-mm-yyyy’) and to_date(‘31-07-2013’,‘dd-mm-yyyy’)
order by mv1.clm_ssn, mv1.clm_serv_strt, mv1.Clm_cost_ctr_nbr, mv1.clm_recv_amt

Alternatively, if you prefer the SQL-92 version:

select mv1.clm_ssn,
mv1.clm_serv_strt,
mv1.clm_cost_ctr_nbr,
mv1.clm_recv_amt
from ddis.pts_mv_clm_stat mv1
inner join ddis.pts_mv_clm_stat mv2
on mv1.clm_ssn = mv2.clm_ssn
and trunc(mv1.clm_serv_strt) = trunc(mv2.clm_serv_strt)
and trim(mv1.clm_cost_ctr_nbr) <> trim(mv2.clm_cost_ctr_nbr)
where trim(mv1.clm_cost_ctr_nbr) in (‘5P311’, ‘6R641’)
and mv1.clm_serv_strt between to_date (‘01-07-2013’,‘dd-mm-yyyy’) and to_date(‘31-07-2013’,‘dd-mm-yyyy’)
order by mv1.clm_ssn, mv1.clm_serv_strt, mv1.Clm_cost_ctr_nbr, mv1.clm_recv_amt

You can include the specific CLM_COST_CTR_NBR values in the where clause of the query or completely leave them out if you want to pull all records with multiple services provided to a customer on a specific day.

I hope this helps!

-John

Hey, I got it working and it returns the results on thesame row. One caveat to this. In my orginal example I was looking for dates when both 5P311 and 6R641 existed. In actuality I wanted all the days where 5P311 and any of the values from the RES group exists - of which 6R641 is a member. The code below achieves the results as I need them:

SELECT ItemA.CLM_SSN,

ItemA.CLM_SERV_STRT Service_Date,

ItemA.CLM_COST_CTR_NBR,

ItemA.CLM_RECV_AMT,

ItemB.CLM_COST_CTR_NBR RES_Cost_Center,

ItemB.CLM_RECV_AMT,

GroupCode,

Service

FROM DDIS.PTS_MV_CLM_STAT ItemA,

DDIS.PTS_MV_CLM_STAT ItemB,

DDIS.CST_SERV

WHERE TRUNC(ItemA.CLM_SERV_STRT) between to_date (‘01-07-2013’,‘dd-mm-yyyy’) and to_date(‘31-07-2013’,‘dd-mm-yyyy’)

and TRUNC(ItemA.CLM_SERV_STRT) = TRUNC(ItemB.CLM_SERV_STRT)

and TRIM(ItemA.CLM_COST_CTR_NBR) = ‘5P311’

and ITEMB.FK_SERV = CST_SERV.PKSERVICE

and CST_SERV.GroupCode = ‘RES’

and Itema.CLM_SSN = ItemB.CLM_SSN

and ItemA.CLM_RECV_AMT <> 0

and ItemB.CLM_RECV_AMT <> 0

ORDER BY ItemA.CLM_SSN, ItemA.CLM_SERV_STRT

John, I like your example. I may try that as well. Thanks everyone for the help!

I’m glad you have it working, Steve! Good luck, and let us know if there’s anything else we can do to help.

-John

From: steve.lundwall [mailto:bounce-stevelundwall@toadworld.com]

Sent: Monday, February 24, 2014 12:05 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad newbie with EXISTS question

RE: Toad newbie with EXISTS question

Reply by steve.lundwall

Hey, I got it working and it returns the results on thesame row. One caveat to this. In my orginal example I was looking for dates when both 5P311 and 6R641 existed. In actuality I wanted all
the days where 5P311 and any of the values from the RES group exists - of which 6R641 is a member. The code below achieves the results as I need them:

SELECT ItemA.CLM_SSN,

   ItemA.CLM_SERV_STRT Service_Date,

   ItemA.CLM_COST_CTR_NBR,  

   ItemA.CLM_RECV_AMT,

   ItemB.CLM_COST_CTR_NBR RES_Cost_Center,

   ItemB.CLM_RECV_AMT,

   GroupCode,

   Service

FROM DDIS.PTS_MV_CLM_STAT ItemA,

   DDIS.PTS_MV_CLM_STAT ItemB,

   DDIS.CST_SERV

WHERE TRUNC(ItemA.CLM_SERV_STRT) between to_date (‘01-07-2013’,‘dd-mm-yyyy’) and to_date(‘31-07-2013’,‘dd-mm-yyyy’)

and TRUNC(ItemA.CLM_SERV_STRT) = TRUNC(ItemB.CLM_SERV_STRT)

and TRIM(ItemA.CLM_COST_CTR_NBR) = ‘5P311’

and ITEMB.FK_SERV = CST_SERV.PKSERVICE

and CST_SERV.GroupCode = ‘RES’

and Itema.CLM_SSN = ItemB.CLM_SSN

and ItemA.CLM_RECV_AMT <> 0

and ItemB.CLM_RECV_AMT <> 0

ORDER BY ItemA.CLM_SSN, ItemA.CLM_SERV_STRT

John, I like your example. I may try that as well. Thanks everyone for the help!

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.