SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Error in Join transformation

Accepted Solution Solved
Reply
Super Contributor
Posts: 625
Accepted Solution

Error in Join transformation

When I tried to execute the join transformation, I'm receiving the error as mentioned below. Appreciate if someone of you help me understand the likely cause for the issue.

 

ERROR: Correlated reference to column PRODUCER_SK is not contained within a subquery.

 

2392             udmtcom.F_PRM_WRITTEN_PREMIUM inner join
2393             udmtcom.D_POL_PRODUCT
2394                on
2395                (
2396                   F_PRM_WRITTEN_PREMIUM.PRODUCER_SK = D_PTY_PRODUCER.PRODUCER_SK
2397                   and F_PRM_WRITTEN_PREMIUM.PRODUCT_SK = D_POL_PRODUCT.PRODUCT_SK
2398                   and F_PRM_WRITTEN_PREMIUM.CUSTOMER_SK = D_PTY_PARTY.PARTY_SK
2399                   and F_PRM_WRITTEN_PREMIUM.POLICY_SK = D_POL_POLICY.POLICY_SK
2400                ),
2401             udmtcom.D_PTY_PARTY,
2402             udmtcom.D_POL_POLICY,
2403             udmtcom.D_REF_DATE,
2404             udmtcom.D_PTY_PRODUCER
2405          ;
ERROR: Correlated reference to column PRODUCER_SK is not contained within a subquery.
ERROR: Correlated reference to column PARTY_SK is not contained within a subquery.
ERROR: Correlated reference to column POLICY_SK is not contained within a subquery.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

Accepted Solutions
Solution
a month ago
Respected Advisor
Posts: 4,680

Re: Error in Join transformation

@Babloo

If in the join properties IMPLICIT is set to NO then you need to formulate the join conditions per source available to the join. You can't just add all the logic to a single JOIN node as you've done it.

In below flow for the first JOIN table CLASS_2 is not available and though you can't add a join condition which references this table. If you do so then you'll get the error you observe and what the SAS Note referenced by @KurtBremser explains.

Capture.JPG

 

Alternatively set the JOIN property to IMPLICIT = YES or right click on the JOIN node and select it from the drop down list. Then add a WHERE node and formulate all your join conditions there.

View solution in original post


All Replies
Super User
Posts: 9,919

Re: Error in Join transformation

See here: http://support.sas.com/kb/32/646.html

Note that this is the first hit of a google search for "sas ERROR: Correlated reference to column PRODUCER_SK is not contained within a subquery". See Maxim 6.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 625

Re: Error in Join transformation

Posted in reply to KurtBremser
I'm unable to understand the solution for the issue. Could you please tell
me what should I do to resolve the issue?
Super User
Posts: 9,919

Re: Error in Join transformation

Inner joins can be more easily written as:

  udmtcom.F_PRM_WRITTEN_PREMIUM,
  udmtcom.D_POL_PRODUCT,
  udmtcom.D_PTY_PARTY,
  udmtcom.D_POL_POLICY,
  udmtcom.D_REF_DATE,
  udmtcom.D_PTY_PRODUCER
where
  F_PRM_WRITTEN_PREMIUM.PRODUCER_SK = D_PTY_PRODUCER.PRODUCER_SK
  and F_PRM_WRITTEN_PREMIUM.PRODUCT_SK = D_POL_PRODUCT.PRODUCT_SK
  and F_PRM_WRITTEN_PREMIUM.CUSTOMER_SK = D_PTY_PARTY.PARTY_SK
  and F_PRM_WRITTEN_PREMIUM.POLICY_SK = D_POL_POLICY.POLICY_SK
;

Note that you have not yet specified a correlation for D_REF_DATE

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
a month ago
Respected Advisor
Posts: 4,680

Re: Error in Join transformation

@Babloo

If in the join properties IMPLICIT is set to NO then you need to formulate the join conditions per source available to the join. You can't just add all the logic to a single JOIN node as you've done it.

In below flow for the first JOIN table CLASS_2 is not available and though you can't add a join condition which references this table. If you do so then you'll get the error you observe and what the SAS Note referenced by @KurtBremser explains.

Capture.JPG

 

Alternatively set the JOIN property to IMPLICIT = YES or right click on the JOIN node and select it from the drop down list. Then add a WHERE node and formulate all your join conditions there.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 201 views
  • 1 like
  • 3 in conversation