Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.
Solved
PL/SQL Two PAYER_IDs with PAYER_TYPES these two types
Posted on 2016-10-18
Hello expert,
Payers have PAYER_IDS and PAYER_TYPE_CODES.
Looking for Payers that have two PAYER_TYPE_CODES
where those two PAYER_TYPES_CODES are 17001 and 17002.
Tried the query copied herewith below.
Returns all PAYER_IDS w PAYER_TYPE__CODES 17002
not just the PAYER_IDS w PAYER_TYPE__CODES 17002
and PAYER_TYPE__CODES 17001
Tried the query copied below with AND instead of OR
but this returned no rows.
Also tried WHERE IN (17001, 17002) Returns similar to OR.
I think I have done this using paretheses but can't remember.
Thanks.
Allen Pitts, Dallas Texas
With cte as (
select
PAyer_ID
FROM
CCM_PAYER_TYPE
group by PAyer_ID
having Count(*) =2
order by PAYER_ID)
SELECT PAYER_TYPE_ID,
CTE.PAYER_ID,
PAYER_TYPE_CODE
FROM CTE
join CCM_PAYER_TYPE PAT on PAT.payer_id =CTE.payer_id
where (PAYER_TYPE_CODE = 17001 or
PAYER_TYPE_CODE = 17002)
order by 2, 3
Payers have PAYER_IDS and PAYER_TYPE_CODES.
Looking for Payers that have two PAYER_TYPE_CODES
where those two PAYER_TYPES_CODES are 17001 and 17002.
Tried the query copied herewith below.
Returns all PAYER_IDS w PAYER_TYPE__CODES 17002
not just the PAYER_IDS w PAYER_TYPE__CODES 17002
and PAYER_TYPE__CODES 17001
Tried the query copied below with AND instead of OR
but this returned no rows.
Also tried WHERE IN (17001, 17002) Returns similar to OR.
I think I have done this using paretheses but can't remember.
Thanks.
Allen Pitts, Dallas Texas
With cte as (
select
PAyer_ID
FROM
CCM_PAYER_TYPE
group by PAyer_ID
having Count(*) =2
order by PAYER_ID)
SELECT PAYER_TYPE_ID,
CTE.PAYER_ID,
PAYER_TYPE_CODE
FROM CTE
join CCM_PAYER_TYPE PAT on PAT.payer_id =CTE.payer_id
where (PAYER_TYPE_CODE = 17001 or
PAYER_TYPE_CODE = 17002)
order by 2, 3
[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
3
4 Comments
Active today
You didn't provide any sample data or expected results so I dummied up some test data that I think is what you are after.
Take a look at the select below. If it isn't correct, please add to the test case and explain where it went wrong.
Take a look at the select below. If it isn't correct, please add to the test case and explain where it went wrong.
/*
--drop table tab1 purge;
create table tab1(payer_ids number, payer_type_codes number);
insert into tab1 values(1,17001);
insert into tab1 values(1,17002);
insert into tab1 values(2,17001);
insert into tab1 values(2,17003);
insert into tab1 values(3,17001);
insert into tab1 values(3,17001);
insert into tab1 values(4,17001);
commit;
*/
select payer_ids from (
select payer_ids,
count(case when payer_type_codes = 17001 then 1 end) has_17001,
count(case when payer_type_codes = 17002 then 1 end) has_17002,
count(*) total_count
from tab1
group by payer_ids
)
where total_count=2 and has_17001=1 and has_17002=1
/
Thanks. Works. Tested solution:
with CTE as
(
select payer_id from (
select payer_id,
count(case when payer_type_code = 17001 then 1 end) has_17001,
count(case when payer_type_code = 17002 then 1 end) has_17002,
count(*) total_count
from CCM_PAYER_TYPE
group by payer_id
)
where total_count=2 and has_17001=1 and has_17002=1
)
SELECT
CTE.PAYER_ID,
PAYER_TYPE_CODE
from CTE
join CCM_PAYER_TYPE PAT on CTE.PAYER_ID = PAT.PAYER_ID
with CTE as
(
select payer_id from (
select payer_id,
count(case when payer_type_code = 17001 then 1 end) has_17001,
count(case when payer_type_code = 17002 then 1 end) has_17002,
count(*) total_count
from CCM_PAYER_TYPE
group by payer_id
)
where total_count=2 and has_17001=1 and has_17002=1
)
SELECT
CTE.PAYER_ID,
PAYER_TYPE_CODE
from CTE
join CCM_PAYER_TYPE PAT on CTE.PAYER_ID = PAT.PAYER_ID
Active today
CTE's are nice when you need them but you really don't need it here.
Here is a revised test case with two tables that pulls data from both:
Here is a revised test case with two tables that pulls data from both:
/*
drop table tab1 purge;
create table tab1(payer_id number, payer_type_codes number);
insert into tab1 values(1,17001);
insert into tab1 values(1,17002);
insert into tab1 values(2,17001);
insert into tab1 values(2,17003);
insert into tab1 values(3,17001);
insert into tab1 values(3,17001);
insert into tab1 values(4,17001);
commit;
drop table tab2 purge;
create table tab2(payer_id number, some_other_column char(1));
insert into tab2 values(1,'A');
insert into tab2 values(2,'B');
commit;
*/
select t1.payer_id, t2.some_other_column from (
select payer_id,
count(case when payer_type_codes = 17001 then 1 end) has_17001,
count(case when payer_type_codes = 17002 then 1 end) has_17002,
count(*) total_count
from tab1
group by payer_id
) t1
join tab2 t2 on t1.payer_id=t2.payer_id
where total_count=2 and has_17001=1 and has_17002=1
/
Active today
You can even move the join inside the outer query but you need to join on all the columns you want. You would need to test them to see which performs better:
select payer_id, some_other_column from (
select t1.payer_id, t2.some_other_column,
count(case when payer_type_codes = 17001 then 1 end) has_17001,
count(case when payer_type_codes = 17002 then 1 end) has_17002,
count(*) total_count
from tab1 t1 join tab2 t2 on t1.payer_id=t2.payer_id
group by t1.payer_id, some_other_column
) t1
where total_count=2 and has_17001=1 and has_17002=1
/
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Background
In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface. I made a script to download the Alert Log, look for errors, and email me the trace files. In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility. The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month11 days, 16 hours left to enroll
730 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.