may not be outer-joined to a subquery 1

[eyetry]

getting this message but don't understand :/

"ORA-01799: a column may not be outer-joined to a subquery"

simple version of query looks something like...

select id.name,
cov.begin_date,
cov.end_date,
cov.maint_date
from identifcation id
coverage cov,
where id.member_id = 'X'
and cov.subscriber_id = id.association_id
and cov.begin_date = (select Max(begin_date) from coverage)

Essentially, because a member can have multiple coverage rows I want the row with the most relevant begin date.

What am I doing wrong?

 

[SantaMufasa]

Eyetry,

Actually, your "cut-down" version of the code is probably missing an important component that relates to the error you are receiving...for example the "(+)" operator.

To help you resolve this issue, it is probably best to just post a copy-and-paste of the actual code and the actual error message.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[eyetry]

ok.... keep in mind I don't know what I'm doing.....

------------------------------------------------------
select distinct(xref.eep_customer_set_id) set_id,
'10' record_type,
hdr.file_id,
hdr.file_config_id config,
hdr.gs_creation_date file_creation_date,
hdr.isa_sender sender_tin,
id.owner,
sub1.attribute_1_char operator,
sub1.attribute_2_char system_id,
id.identifier_type_vc ssn_vc,
id.identifier ssn,
id2.identifier_type_vc alt_vc,
id2.identifier alt_id,
sub1.last_name,
sub1.first_name,
sub1.addr1,
sub1.addr2,
sub1.city,
sub1.county,
sub1.state,
sub1.zip,
sub1.country,
sub1.date_of_birth dob,
sub1.gender_code gender,
sub1.cobra_qualifying_event_code cobra_qual_code,
sub1.cobra_qualifying_event_date cobra_date,
sub1.alternate_identifier alt_id_in,
cov2.hire_date,
cov1.level_code coverage_type,
cov2.begin_date,
cov2.end_date,
cov1.attribute_2_char,
prov1.location office
from sot01.fnd_eep_customer_xref xref,
sot01.fnd_file_config cfg,
sot01.fnd_maptool_header hdr,
sot01.fnd_maptool_subscriber sub1,
sot01.fnd_maptool_hlth_coverage cov1,
sot02.enr_coverage cov2,
sot01.fnd_maptool_provider prov1,
sot02.enr_identifier id,
sot02.enr_identifier id2
where cfg.file_config_id = '1111'
and hdr.file_id = '222222'
and hdr.maptool_header_id = sub1.maptool_header_id
and cfg.eep_customer_set_id = xref.eep_customer_set_id
and id.association_table = 'ENR_SUBSCRIBER'
and id.association_table = id2.association_table(+)
and id.owner = xref.customer_id
and id.owner = id2.owner
and sub1.unique_subscriber_identifier = id.identifier
and id.identifier_type_vc = 9512
and id2.association_id(+) = id.association_id
and id2.identifier_type_vc(+) = 9513
and cov1.maptool_header_id = sub1.maptool_header_id
and cov1.maptool_subscriber_id = sub1.maptool_subscriber_id
and cov1.maptool_dependent_id is null
and prov1.maptool_subscriber_id(+) = sub1.maptool_subscriber_id
and prov1.maptool_subscriber_id(+) is null
and cov2.subscriber_id(+) = id.association_id
and cov2.begin_date(+) = (select Max(cov2.begin_date) from sot02.enr_coverage)

-------------------------------------------------------
this worked until I added the last 2 conditions...
"
.
.
and cov2.subscriber_id(+) = id.association_id
and cov2.begin_date(+) = (select Max(cov2.begin_date) from sot02.enr_coverage)
"

 

[eyetry]

ps: the exeact message was

"ORA-01799: a column may not be outer-joined to a subquery"

 

[SantaMufasa]

Eyetry,

Given the behaviour of outer joins ["(+)"], I cannot imagine why you need the last "(+)". Therefore, please try this modification in your SQL statement:

...and cov2.begin_date = (select Max(cov2.begin_date) from sot02.enr_coverage)...

Let us know if your results are more satisfying.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[eyetry]

Yep! and even more so when I changed it from '=' to 'in'

Thanks from the beginner!

 

[SantaMufasa]

Thanks kindly, Eyetry, for the Star.

Just to clarify,

...and cov2.begin_date [b]IN[/b] (select Max(cov2.begin_date) from sot02.enr_coverage)...

...is certainly fine, but it is an unnecessary change since the "...SELECT MAX(..." query can return at most, and only, one result. Therefore using "=" works, in this case, identically to "IN".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]