Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

PAYER_ID has both atributes

Posted on 2016-11-21
4
Medium Priority
?
59 Views
Last Modified: 2016-11-21
Hello expert,

Payers can be type Lab = 17006 and type Non_Injury_TPA = 17006.
Have drafted query that gives me PAYER_IDs that are both types,
copied herewith below.

By ordering by Payer_ ID it can be seen which Payers have both types
but there are 7k of them so a query is needed that lists only Payer
that have both types.

Tried using an AND clause and a couple of other attempts with no luck.

I think it may be a GROUP BY but not sure of the syntax.

Is there a query which will list only Payers that have PAYER_TYPE_CODE 17006
and 17007?

Thanks.

Allen in Dallas
 

SELECT PAYER_TYPE_ID,
  PAY.PAYER_ID,
  PAYER_NAME,
  PAYER_TYPE_CODE,
  PAY.LAST_UPDATE_USER,
  PAY.LAST_UPDATE_DATE,
  PAY.IS_DISABLED
FROM CCM.CCM_PAYER_TYPE PAT
join CCM_PAYER PAY on  PAY.PAYER_ID = pat.payer_id
where payer_type_code = 17006
or payer_type_code = 17007
order by PAY.PAYER_ID
0
Comment
Question by:Allen Pitts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 2000 total points
ID: 41896580
Assuming that I guess the correct table name, this should work.
SELECT payer_type_id, 
       PAY.payer_id, 
       payer_name, 
       payer_type_code, 
       PAY.last_update_user, 
       PAY.last_update_date, 
       PAY.is_disabled 
FROM   ccm.ccm_payer_type PAT 
       join ccm_payer PAY 
         ON PAY.payer_id = pat.payer_id 
WHERE  payer_type_code = 17006 
       AND EXISTS (SELECT 1 
                   FROM   ccm_payer pay2 
                   WHERE  pay.payer_id = pay2.payer_id 
                          AND pay2.payer_type_code = 17007) 
ORDER  BY PAY.payer_id 

Open in new window

If not, you will have to change the EXISTS subquery.
0
 

Author Closing Comment

by:Allen Pitts
ID: 41896650
Hello johnsone,

Works great. Did change

AND EXISTS (SELECT 1
                   FROM   ccm_payer_id pay2
to
AND EXISTS (SELECT 1
                   FROM   ccm_payer_type pay2

So a second WHERE condition is used to see if
 there is a payer_type_code = 17007 .
Simple and effective.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41896679
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41896707
Sorry.  There was no table alias indicating which table it came from, had to guess.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question