Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle Apex Modfiy Grid query to fetch previous quarter data if current not available

VinipandaMay 12 2021

I have an apex application that shows department wise project data for every quarter.
So on home page we select department, quarter and year and then for the same data is shown in all pages.

For one page that shows KPI data in interactive grid ,I need to modify my query as per logic required.

It needs to fetch data quarterly, however if say data is not available for a particular quarter it'd take the data from previous quarter. 

So, if for Quarter 1,2021 data is not available, it should fetch from Quarter 4,2020.
If Quarter 2,2021 is not available, it should fetch Quarter 1,2021 data.

Tables with data for clarity:

The first tale is DEPT_QUARTER_DATA which stores id for each quarter of department.

User:

So, each dept_id for each quarter has a particular quarter_pk.

Also 10 is First quarter, 11 is Second quarter,12 is Third quarter and 13 is fourth quarter.

DEPT_DATA table which has main data:

User:

It has sample data for 2 departments.

So, ìn first row,dept_quarter_id =1 indicates Quarter 1,2020 data, for dept_id=1.

Also,
DEPT_QUARTER_DATA.QUARTER_PK = DEPT_DATA.DEPT_QUARTER_ID

Now the query should be, say there is no data for dept_id=1 in dept_quarter_id=2, then it should fetch data of dept_quarter_id=1.i.e. go backwards.

If there is no data for dept_quarter_id=5, i.e.Quarter 1,2021, it should fetch data from dept_quarter_id=4, i.e. Quarter 4,2020.

Current Query is and i understand it needs to be modified:

  SELECT DEPT_ID,DEPT_QUARTER_ID,
      ,case when KPI_1 is null then (Select KPI_1 from DEPT_DATA where DEPT_QUARTER_ID
  in(select quarter_pk from DEPT_QUARTER_DATA where DEPT_ID=:DEPT_ID and
  quarter = :QUARTER-1 and year = YEAR and QUARTER_PK= :DEPT_QUARTER_ID)and PROJECT = :P1_PROJECT) else KPI_1 end as KPI_1
  from DEPT_DATA
  WHERE  
    DEPT_QUARTER_ID=:DEPT_QUARTER_ID
  AND   PROJECT = :P1_PROJECT 

QUARTER, PROJECT,YEAR are all global items being passed based on DEPT_QUARTER_ID of the project which has been set as application item.

Apex: 20.2

Comments

Solomon Yakobson

It couldn't unless dropped in both schemas directly or indirectly (e.g. there is after DDL trigger). Also, second schema could have not the view but rather a synonym to first schema view, although in such case you'd get "synonym translation no longer valid" rather than "table or view doesn't exist". You could check DBA_AUDIT_TRAIL to find out who and when dropped what, assuming DDL audit it enabled.
SY.

1 person found this helpful
Kamran Agayev A.

Dear OP
If audit wasn't enabled in the database, it's hard to answer the question "who dropped the view". If the data is still in UNDO, you can use flashbcak query to get the dropped views back. Check the following blog post for more information on this:
How to flashback procedure or package - OracleDocs.com (0 Bytes)BR,
Kamran Aghayev A.

Solomon Yakobson

There is no need to dig into flashback unless OP has no saved view definition script (which would be strange and would indicate much deeper organizational issues).
SY.

1 person found this helpful
Kamran Agayev A.

OP mentioned that views in other schemas were dropped which means that most probably they didn't saved the script of the views which dropped by accident

user545194

enable_ddl_logging is not enabled. We are on SE, therefore do not have flashback query. I can check the alert log to see what happened. Aren't dropped objects moved to the recyclebin?

Solomon Yakobson

If so, then, as I already mentioned, it would indicate much deeper organizational issues. Any changes must be scripted and saved to CMS before deploying.
SY.

Kamran Agayev A.

Views are just scripts which are not moved to the recycle bin. Do you have a test environment for this particular database to imitate the same action there as well and see if views are dropped (which shouldn't )

1 person found this helpful
user545194

Yes, we have a test environment and the views are there, but they are not in sync with the production env. But I can perform the same action and see what the outcome is. For production however, we'll have to restore from a recent backup.
Good news: Suddenly the views "re-appeared" and are visible in the SQL Developer tree view.
Really strange. Thanks to all for your feedback.

Kamran Agayev A.

Sure you have to restore it from backup anyways. What I'm wondering the abnormal behavior that happened in your system. I would suggest you to enable the audit on the test environment, drop the mentioned views and check if other views are also dropped
BR,
Kamran Aghayev A.

1 person found this helpful
user545194
Answer

Yes, I will definitely run this in the test environment. Thanks again!

Marked as Answer by user545194 · Jan 25 2021
1 - 10

Post Details

Added on May 12 2021
#plsql, #sql
3 comments
72 views
1 mention