How to get value from outer to inner SELECT

[Robert Konig]

Hello, i am at the beginning of learning SQL, and i cannot find the solution to my problem: I have got two tables, WORKCENTER and CALENDAR. WORKCENTER contains columns ID and NR_OF_DAYS, CALENDAR Contains columns DAY and IS_WORKING_DAY. DAY contains the numeric value of a day (for example 14824 for today, 2010-08-03), and IS_WORKING_DAY is CHAR Type and indicates with 'y/n' if it is a working day or not. I want for each Work Center to get the day from today + NR_OF_DAYS from table CALENDAR, excluding weekends and holidays. The result should be like this:

ID MAX_CALENDAR_DAY
1 14833
2 14840
...

Explanation: Work Center 1 has NR_OF_DAYS = 7, today is 14824, + 7 = 14831, but there is a weekend in between, so + 2 = 14833. Work Center 2 has NR_OF_DAYS = 12, today is 14824, + 12 = 14836, but there are two weekends in between, so + 4 = 14840. This is my sql query (Oracle):

SELECT WORKCENTER.ID AS WORKCENTER_ID, MAX_CALENDAR_DAY
FROM WORKCENTER,
(SELECT MAX(DAY) AS MAX_CALENDAR_DAY
FROM (SELECT DAY
FROM CALENDAR
WHERE DAY > TRUNC(TO_NUMBER(SYSDATE - TO_DATE('01.01.1970', 'DD.MM.YYYY')))
WHERE CALENDAR.IS_WORKING_DAY= 'y'
ORDER BY CALENDAR.DAY
)
WHERE ROWNUM <= WORKCENTER.NR_OF_DAYS
)
WHERE WORKCENTER.ID IN ( 1, 2, 6, 7, 9)

Oracle SQL Developer shows an error, WORKCENTER.NR_OF_DAYS is unknown. This is obvious, if inner queries are handled first (is this correct?). Could you tell me how i have to build this query? I hope you see what i am trying to do... Greetings

[Tim Carmichael]

In your first sub-select, you have MAX(DAY) as MAX_CALENDAR_DAY and you get that data from a further sub-select. Fine and good. However, on your where clause, you reference, WORKCENTER.NR_OF_DAYS. The WORKCENTER table was never referenced. Tim

[Bernhard Hiller]

Start with a query telling you CALENDAR.DAY for every NR_OF_DAYS and limit that to the maximum number found in the WORKCENTER table, maybe

SELECT ROWNUM as NR_OF_DAYS, DAY AS MAX_CALENDAR_DAY
FROM (SELECT DAY
FROM CALENDAR
WHERE DAY > TRUNC(TO_NUMBER(SYSDATE - TO_DATE('01.01.1970', 'DD.MM.YYYY')))
AND CALENDAR.IS_WORKING_DAY= 'y'
ORDER BY CALENDAR.DAY
)
WHERE ROWNUM <= (SELECT MAX(NR_OF_DAYS) FROM WORKCENTER WHERE WORKCENTER.ID IN ( 1, 2, 6, 7, 9))

and then INNER JOIN that to the WORKCENTER table ON NR_OF_DAYS.

[Robert Konig]

This is my query now:

SELECT ID,
DAY
FROM (SELECT ROWNUM as CAL_ROWNUM,
DAY AS MAX_CALENDAR_DAY
FROM (SELECT DAY
FROM CALENDAR
WHERE DAY > TRUNC(TO_NUMBER(SYSDATE - TO_DATE('01.01.1970', 'DD.MM.YYYY')))
AND CALENDAR.IS_WORKING_DAY= 'y'
ORDER BY CALENDAR.DAY)
WHERE ROWNUM <= (SELECT MAX(NR_OF_DAYS)
FROM WORKCENTER
WHERE ID IN (1, 2, 6, 7, 9))
)
INNER JOIN WORKCENTER
ON NR_OF_DAYS = CAL_ROWNUM
WHERE ID IN (1, 2, 6, 7, 9)

And it works, yeah! Thanks guys. Greetings