CODEWITHSUNDEEP
sqloracle10gsubquery
cms_mgr
cms_mgr

Reputation: 2017

Why would a working SQL query not work as a subquery?

I have the following query, which works and produces the output I need it to.

SELECT
*
FROM
(SELECT
  ref_year, ref_no, min(sequence_number) sequence_number
FROM
  case_decisions
WHERE
  stage = 'C'
AND
  decision_code <> 'IA'
GROUP BY
  ref_year,
  ref_no
) temp

INNER JOIN /* Produce table of first decisions only */
  case_decisions cdec
ON
  temp.ref_year = cdec.ref_year
AND
  temp.ref_no = cdec.ref_no
AND
  temp.sequence_number = cdec.sequence_number;

If, however, I try to use it as a subquery join (as follows) to another working query I get an error saying fdec.ref_no is not a valid identifier. I presume there is a problem with the way I've written the subquery but can't find it.

LEFT OUTER JOIN
(SELECT
  *
FROM
  (
  SELECT
    ref_year, ref_no, min(sequence_number) sequence_number
  FROM
    case_decisions
  WHERE
    stage = 'C'
  AND
    decision_code <> 'IA'
  GROUP BY
    ref_year,
    ref_no
  ) temp

INNER JOIN /* Produce table of first decisions only */
  case_decisions cdec
ON
  temp.ref_year = cdec.ref_year
AND
  temp.ref_no = cdec.ref_no
AND
  temp.sequence_number = cdec.sequence_number
) fdec
ON
  resolved.ref_year = fdec.ref_year
AND
  resolved.ref_no = fdec.ref_no

Giving a full query of:

SELECT
  resolved.*,
  fdec.decision_code initial_decision

FROM
  (SELECT
    t1.ref_year,
    t1.ref_no,
    t1.date_decision_status_achieved,
    t1.sequence_number
  FROM
    case_decisions t1
    LEFT OUTER JOIN
    case_decisions t2
  ON
    (t1.ref_year = t2.ref_year
  AND
    t1.ref_no = t2.ref_no
  AND
    t1.sequence_number < t2.sequence_number)
WHERE
  t2.ref_year IS NULL
AND
  t2.ref_no IS NULL
AND
  (t1.status = 8 OR t1.status =11)
AND
  t1.decision_code <> 'IA'
AND
  t1.date_decision_status_achieved between '01-APR-2008' and '01-JUN-2008' /* dates for testing purposes only */) resolved

LEFT OUTER JOIN
(SELECT
  *
FROM
  (SELECT
    ref_year, ref_no, min(sequence_number) sequence_number
  FROM
    case_decisions
  WHERE
    stage = 'C'
  AND
    decision_code <> 'IA'
  GROUP BY
    ref_year,
    ref_no
  ) temp

INNER JOIN /* Produce table of first decisions only */
  case_decisions cdec
ON
  temp.ref_year = cdec.ref_year
AND
  temp.ref_no = cdec.ref_no
AND
  temp.sequence_number = cdec.sequence_number) fdec
ON
  resolved.ref_year = fdec.ref_year
AND
  resolved.ref_no = fdec.ref_no
;

I've had a look at similar SO questions but none seem to be trying what I am (I expect an indication I'm doing something wrong). Any suggestions for what I'm missing here?

Upvotes: 0

Views: 94

Answers (2)

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171371

The problem is here:

select * 
from (
    select ref_year,
        ref_no,
        min(sequence_number) sequence_number
    from case_decisions
    where stage = 'C'
        and decision_code <> 'IA'
    group by ref_year,
        ref_no
    ) temp
inner join case_decisions cdec on temp.ref_year = cdec.ref_year
    and temp.ref_no = cdec.ref_no
    and temp.sequence_number = cdec.sequence_number
) fdec on resolved.ref_year = fdec.ref_year

You are selecting ref_no twice (once from temp and once from cdec because of the *), so it does not know which ref_no column to refer to. Instead of *, reference only the columns you need.

Try this version:

select resolved.*,
    fdec.decision_code initial_decision
from (
    select t1.ref_year,
        t1.ref_no,
        t1.date_decision_status_achieved,
        t1.sequence_number
    from case_decisions t1
    left outer join case_decisions t2 on (
            t1.ref_year = t2.ref_year
            and t1.ref_no = t2.ref_no
            and t1.sequence_number < t2.sequence_number
        )
    where t2.ref_year is null
        and t2.ref_no is null
        and (
            t1.status = 8
            or t1.status = 11
        )
        and t1.decision_code <> 'IA'
        and t1.date_decision_status_achieved between '01-APR-2008' and '01-JUN-2008' /* dates for testing purposes only */
) resolved
left outer join (
    select temp.ref_year, temp.ref_no, cdec.decision_code
    from (
        select ref_year,
            ref_no,
            min(sequence_number) sequence_number
        from case_decisions
        where stage = 'C'
            and decision_code <> 'IA'
        group by ref_year,
            ref_no
    ) temp
    inner join case_decisions cdec on temp.ref_year = cdec.ref_year
        and temp.ref_no = cdec.ref_no
        and temp.sequence_number = cdec.sequence_number
) fdec on resolved.ref_year = fdec.ref_year
    and resolved.ref_no = fdec.ref_no;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269503

My best guess is that the "*" brings in two columns named "ref_no" into the subquery, because temp and cdec both have the column.

Try changing the select to:

select cdec.*, temp.sequence_number

I've seen this error before in other databases, but the message is much clearer.

Upvotes: 1

Related Questions