CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloperplsqldeveloper
user3098728
user3098728

Reputation: 131

Using Left Join in 3 tables

I'm trying to join 3 tables using LEFT JOIN, but I always have an error. Please help me.

Thank you

The output that I want:

Name            Date              Open               Close
POS             01/01/2014         4                  10
ONE             01/02/2014         6                  5
...
...
...

My query:

SELECT 'Data'
||','||TO_CHAR(D.DTIME_DAY,'MM/dd/yyyy')
||','||NVL(o.CNT_OPENED,0) --as cnt_opened
||','||NVL(c.CNT_CLOSED,0) --as cnt_closed
||','||q.NAME
FROM OWNER_DWH.DC_DATE d
LEFT JOIN APP_ACCOUNT.OTRS_QUEUE q
ON t.queue_id = q.id
LEFT JOIN (
SELECT TRUNC(t.CREATE_TIME) AS report_date,count(*) AS cnt_opened
FROM APP_ACCOUNT.OTRS_TICKET t
WHERE t.CREATE_TIME BETWEEN SYSDATE -120 AND SYSDATE
GROUP BY TRUNC(t.CREATE_TIME)
) o ON d.DTIME_DAY=o.REPORT_DATE
LEFT JOIN (
SELECT TRUNC(t.CLOSE_TIME) as report_date,count(*) AS cnt_closed
FROM APP_ACCOUNT.OTRS_TICKET t
WHERE t.CLOSE_TIME BETWEEN SYSDATE -120 AND SYSDATE
GROUP BY TRUNC(t.CLOSE_TIME)
) c ON D.DTIME_DAY=c.REPORT_DATE
WHERE d.DTIME_DAY BETWEEN SYSDATE -120 AND TRUNC(SYSDATE) -1
AND d.DTIME_DAY = TRUNC(d.DTIME_DAY)
AND TRUNC(d.DTIME_DAY)= d.DTIME_DAY
ORDER BY D.DTIME_DAY;

Upvotes: 0

Views: 148

Answers (1)

Arnab Bhagabati
Arnab Bhagabati

Reputation: 2715

You dont have any table alias for "t" in your first left join. So oracle doesn't know what is the "t" in t.queue_id yet at the join condition

ON t.queue_id = q.id.

from your code:

SELECT 'Data'
||','||TO_CHAR(D.DTIME_DAY,'MM/dd/yyyy')
||','||NVL(o.CNT_OPENED,0) --as cnt_opened
||','||NVL(c.CNT_CLOSED,0) --as cnt_closed
||','||q.NAME
FROM OWNER_DWH.DC_DATE d
LEFT JOIN APP_ACCOUNT.OTRS_QUEUE q
ON t.queue_id = q.id
LEFT JOIN (
SELECT TRUNC(t.CREATE_TIME) AS report_date,count(*) AS cnt_opened
FROM APP_ACCOUNT.OTRS_TICKET t
WHERE t.CREATE_TIME BETWEEN SYSDATE -120 AND SYSDATE
GROUP BY TRUNC(t.CREATE_TIME)
)

the alias "t" is defined inside the block :

LEFT JOIN ( 
SELECT TRUNC(t.CREATE_TIME) AS report_date,count(*) AS cnt_opened
FROM APP_ACCOUNT.OTRS_TICKET t
WHERE t.CREATE_TIME BETWEEN SYSDATE -120 AND SYSDATE
GROUP BY TRUNC(t.CREATE_TIME)
)

Now, as the join ON t.queue_id = q.id is otside this block (outside the brackets), the alias "t" is not available at that point.

hence the error.

Upvotes: 1

Related Questions