| Not exists with union query-Help [message #648371] |
Mon, 22 February 2016 08:28  |
 |
Ratika
Messages: 5
Registered: February 2016
Location: India
|
Junior Member |
|
|
Hi Sir
I am new to this forum and having 1yr exp in oracle . Thanks for having this kind of forum.
I want to convert the below query as view. I have tried with minus and outer join but the output didn't come properly.
I have read your forum guidelines, that you need sample but I am sorry sir that I couldn't put any data here because of security breach and business restriction.
Sir if you could give some idea that will help me to rewrite this query as a view.
the commented line as input.
select distinct cpmt.lbl,
cpmt.end_dt
from cpmsr_t cpmt, rctms_t rct
where cpmt.co_id = rct.co_id
and cpmt.lbl = rct.lbl
and rct.stat_cd = 'IN'
and sysdate between cpmt.eff_dt and cpmt.end_dt
--and rct.pg_id = 1010
-- and rct.time_id > 10
--and rct.co_id = 77)
union
(select cpmt.ndc_lbl, cpmt.end_dt,
from cpmsr_t cpmt, pmsr_t pmt
where pmt.lbl = cpmt.lbl
and pmt.prod = cpmt.prod
and pmt.pckg = cpmt.pckg
--AND cpmt.co_id=77
and pmt.elig_stat_cd = 'EL'
and not exists
(select rct.ndc_lbl
from rctms_t rct
where rct.ndc_lbl =cpmt.ndc_lbl
--and rct.pg_id = 1010
--and rct.time_id > 10
--and rct.co_id =77 )
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Not exists with union query-Help [message #648376 is a reply to message #648374] |
Mon, 22 February 2016 09:01  |
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You say
Quote:i need to have pg_id,time_id,co_id should be in select
so add those columns to the projection list.
This isn't difficult, you know. Or is it something more complicated?
|
|
|
|
|
|
| Re: Not exists with union query-Help [message #648378 is a reply to message #648377] |
Mon, 22 February 2016 09:08 |
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I have no idea what you are talking about.
I think you mean that you have a problem with a predicate. That has nothing to do with column projection.
You had better start again, and explain your problem. Please try to use the correct terms.
|
|
|
|
|
|
| Re: Not exists with union query-Help [message #648380 is a reply to message #648379] |
Mon, 22 February 2016 09:40 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
The problem is the NOT EXISTS sub-query. OP wants to be able to have all the un-commented bits in a view and the commented bits in the where clause of the select against the view:
e.g.
SELECT *
FROM view
WHERE pg_id = 1010
AND time_id > 10
AND co_id = 77
When you write a where clause against a view you're effectively taking the output of an existing query and applying further restrictions to it. This can't work for the sub-query as nothing it selects appears in the output of the query.
@Ratika - Do you really need those restrictions on the aub-query? Aren't you just trying to find, in the 2nd part, records in cpmsr_t that do join to pmsr_t but don't have a corresponding record in rctms_t?
In other words does this give the same output as your original query:
select distinct cpmt.lbl,
cpmt.end_dt
from cpmsr_t cpmt, rctms_t rct
where cpmt.co_id = rct.co_id
and cpmt.lbl = rct.lbl
and rct.stat_cd = 'IN'
and sysdate between cpmt.eff_dt and cpmt.end_dt
and rct.pg_id = 1010
and rct.time_id > 10
and rct.co_id = 77)
union
(select cpmt.ndc_lbl, cpmt.end_dt,
from cpmsr_t cpmt, pmsr_t pmt
where pmt.lbl = cpmt.lbl
and pmt.prod = cpmt.prod
and pmt.pckg = cpmt.pckg
AND cpmt.co_id=77
and pmt.elig_stat_cd = 'EL'
and not exists
(select rct.ndc_lbl
from rctms_t rct
where rct.ndc_lbl =cpmt.ndc_lbl)
|
|
|
|
| Re: Not exists with union query-Help [message #648385 is a reply to message #648380] |
Mon, 22 February 2016 14:48 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
try
create or replace my_view as
select cpmt.lbl,cpmt.end_dt,cpmt.co_id,null elig_stat_cd,null ndc_lbl
from cpmsr_t cpmt, rctms_t rct
where cpmt.co_id = rct.co_id
and cpmt.lbl = rct.lbl
and rct.stat_cd = 'IN'
and sysdate between cpmt.eff_dt and cpmt.end_dt
and rct.pg_id = 1010
and rct.time_id > 10
union
select cpmt.ndc_lbl, cpmt.end_dt,cpmt.co_id,pmt.elig_stat_cd,rct.ndc_lbl
from rctms_t rct,cpmsr_t cpmt, pmsr_t pmt
where pmt.lbl = cpmt.lbl
and pmt.prod = cpmt.prod
and pmt.pckg = cpmt.pckg
and rct.ndc_lbl(+) =cpmt.ndc_lbl;
and use it like this
select lbl,end_dt
from my_view
where co_id = 77
and ndc_lbl is null;
This is untested code. Modify if it doesn't work. The UNION suppresses duplicates so you don't need the distinct.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
