Solved

PAYER_ID has both atributes

Posted on 2016-11-21
4
31 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:9apit
  • 2
4 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 500 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:9apit
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 76

Expert Comment

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

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle TEXT search question 9 48
sql query 9 38
VB.Net - Oracle BulkCopy from CSV Date Format 7 47
Converting a row into a column 2 44
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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now