Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2014
    Posts
    1

    Exclamation Unanswered: correlated query issue

    Hi All,

    From the below correlated sub query I am unable to get any record,being the inner query is unable to pick up "Schema.Table1" for the join. If I give the table name again in the inner query then the Total correlated sub query is returning records. I need to know what is the Issue in the Actual Query

    Actual query
    ------------

    SELECT D.REPORT_KEY, L.WI_ID
    FROM Schema.Table1 D
    JOIN Schema.Table2 L ON D.REPORT_ROLE = L.PERSON_ROLE
    JOIN schema.Table3 BP ON BP.BP_CEI = L.BP_CEI
    WHERE D.COUNTRY_LOGIC='I'
    AND BP.BP_COUNTRY IN (SELECT DC.COUNTRY_CODE FROM Schema.Table4 DC WHERE (DC.REPORT_KEY)=(D.REPORT_KEY) )

    changed query which is returning the records
    -------------------------------------------

    SELECT D.REPORT_KEY, L.WI_ID
    FROM Schema.Table1 D
    JOIN Schema.Table2 L ON D.REPORT_ROLE = L.PERSON_ROLE
    JOIN schema.Table3 BP ON BP.BP_CEI = L.BP_CEI
    WHERE D.COUNTRY_LOGIC='I'
    AND BP.BP_COUNTRY IN (SELECT DC.COUNTRY_CODE FROM Schema.Table4 DC,Schema.Table1 WHERE (DC.REPORT_KEY)=(D.REPORT_KEY) )



    From the outer query (Schema.Table1) can be used? in the inner query.
    Can 'IN' be utilized in the correlated sub query.


    Thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    First I thought was "wasn't there an alias D in IN subquery in changed query?"

    AND BP.BP_COUNTRY IN (SELECT DC.COUNTRY_CODE FROM Schema.Table4 DC,Schema.Table1 D WHERE (DC.REPORT_KEY)=(D.REPORT_KEY) )


    Second,
    "From the below correlated sub query I am unable to get any record, being the inner query is unable to pick up "Schema.Table1" for the join."
    was it mean "got error message(s)" or simply "no row was returned"?

    If "no row was returned",
    then "no row was returned from the IN subquery"?
    or "no row was returned from the main select"?

    If "no row was returned from the subquery",
    then how did you confirmed that?

    ...
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •