Hi all, Im an Oracle newbie here. I have questions here that need guidance from Oracle Expert here. Here it goes:
- there are 3 databases that will be used: tableA (2000 records), tableAsub (45000 records), and tableBsub (850000 records)
- relationship between the 3 tables: "tableA" --link to--> "tableAsub" --link to--> "tableBsub"
- get the minimum "seq" from tableBsub
- get the first "startTime" and last "endTime" from tableBsub, group by an id
- if tableAsub's status is "1" and/or "2", then "period" is based on tableBsub's same id, the last "endTime" will minus with the first "startTime" of tableBsub, and if "tableBsub.endTime" is NULL, then display 0
- if tableAsub's status is "3" to "6", then "period" = sysdate - tableBsub.startTime
Just want to ask experts here can this be done in ONE sql statement using case? Or must use pl/sql's if else and cursor? But my main problem is I'm still fresh and it will take me long time before I'm able to understand Oracle's pl/sql.
Then are there any suggestions to improve the performance of the query? Cause I've done some queries on other situations but they took very long to execute.
Thanks in advance.
ps: Please forgive me for me poor English, do let me know if I do not state my situation clear enough and I'll try to edit it.