Reference table from subquery in Oracle

Question

I have simplified my tables but essentially I have a table of accounts that have a cycle_no and end date. This end date is always set to the first of the month but I need to get the real end date by looking in the calendar details table. The real end date is the next date for this cycle_no.

To create the simplified tables and enter a few rows of data:

CREATE TABLE DATA_OWNER.ACCOUNT
(
  ACCNO     NUMBER(4),
  CYCLE_NO  NUMBER(4),
  ENDDATE   DATE
);

CREATE TABLE DATA_OWNER.CALENDAR_DETAILS
(
  CALENDAR_DT   DATE,
  BILL_CYCL_NO  NUMBER(4)
);

INSERT INTO calendar_Details
VALUES
('18/DEC/2017',
17);

INSERT INTO calendar_Details
VALUES
('23/DEC/2017',
20);

INSERT INTO calendar_Details
VALUES
('18/JAN/2018',
17);

INSERT INTO calendar_Details
VALUES
('23/JAN/2018',
20);

INSERT INTO calendar_Details
VALUES
('20/FEB/2018',
17);

INSERT INTO calendar_Details
VALUES
('21/FEB/2018',
20);

INSERT INTO account
VALUES
(1, 17, '01/DEC/2107');

INSERT INTO account
VALUES
(2, 20, '01/DEC/2107');

If we run this query though we get "ACC". "ENDDATE": invalid identifier:

SELECT accno, cycle_no, enddate, actual_date
FROM account acc
JOIN
(
  SELECT MIN(calendar_dt) actual_date
  FROM calendar_details cal
  WHERE calendar_dt > acc.enddate
)
ON acc.cycle_no = cal.bill_cycl_no;

Can anyone give us some pointers on the best way to achieve this please?

What is your Oracle version? From 12.1 you can use lateral etc. — William Robertson
– William Robertson, Commented Nov 10, 2017 at 14:11

Gordon Linoff · Accepted Answer · 2017-11-10 14:31:20Z

1

You cannot refer to outer table references in a subquery in the FROM. Just use a correlated subquery:

SELECT accno, cycle_no, enddate,
       (SELECT MIN(cal.calendar_dt) as actual_date
        FROM calendar_details cal
        WHERE cal.calendar_dt > acc.enddate AND acc.cycle_no = cal.bill_cycl_no
       ) as actual_date
FROM account acc;

edited Nov 10, 2017 at 14:31

answered Nov 10, 2017 at 14:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SmallFry Over a year ago

Thanks for your answer. I see what you're saying but when I run that query, I get no values in the actual_date column in the results?

Gordon Linoff Over a year ago

@PinkSalmon . . . Perhaps the cycle numbers don't exist in the calendar details table.

SmallFry Over a year ago

Think it was just my made-up data but using this as the basis for the query with the actual tables seems to be working. Sometimes, can't see the wood for the trees. Thanks again.

Collectives™ on Stack Overflow

Reference table from subquery in Oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related