A report which dynamically ignores all zero value columns
A risk reporting table contains exposure values by counterparty for the last 7 days.
The structure of the table is as follows:
COUNTERPARTY VARCHAR2(50)
T NUMBER
T-1 NUMBER
T-2 NUMBER
T-3 NUMBER
T-4 NUMBER
T-5 NUMBER
T-6 NUMBER
Values for Saturdays and Sundays will always be zero.
T to T-6 will be a moving window, therefore we can not hardcode which days are Saturdays or Sundays.
The requirement is to load the data into another table, where the final product will be a CSV, but users do not want to include the Saturday/Sunday all zero columns.
How do we do this?
A quick way would be to write a code like this
|
declare
v_t number; v_t1 number; v_t2 number; v_t3 number; v_t4 number; v_t5 number; v_t6 number; v_sql varchar2(2000); begin v_sql := ‘insert into newtable as select ‘ v_sql := v_sql ||’ from table1 ;’ execute immediate v_sql; end; |