How to pipeline a function with a dynamic name of columns?

Question

I need to create a report which retrieve expenses for the last 12 months. Depends on the day I execute my report the name of the columns of my report will change (for example if I execute the report in JUL, it should retrieve twelve columns with the expenses in JUN, MAY, APR, MARCH. The name of the columns are the last 12 months).

The following PL/SQL code creates a view with dinamic name columns depending on the sysdate.

DECLARE

vSQL VARCHAR2(4000);

BEGIN

vSQL := 'CREATE OR REPLACE VIEW IFSAPP.RLDateTest AS SELECT 
            ip.part_no,
            IFSAPP.RD_PURCH_DEMAND_QTY_ISSUE_API.Get_Avr_Usage_Per_Month(ip.contract,ip.part_no ,to_char(sysdate, ''MM'')-1) ' || **SUBSTR(ADD_MONTHS(SYSDATE, -1),4,3)** ||
         ', IFSAPP.RD_PURCH_DEMAND_QTY_ISSUE_API.Get_Avr_Usage_Per_Month(ip.contract,ip.part_no ,to_char(sysdate, ''MM'')-2) ' || **substr(ADD_MONTHS(SYSDATE, -2),4,3)** || ' FROM ifsapp.inventory_part ip WHERE ip.contract = ''S03'' ';

EXECUTE IMMEDIATE vSQL;

END;

(|| SUBSTR(ADD_MONTHS(SYSDATE, -1),4,3) || is the alias of each colum)

The point is that once I execute my report, it should create the view and show all its rows. For that I had thought of using pipelined (to create a type table consisting of the result of my view and this would let me do SELECT * FROM TABLE(getView())) in my report.

My problem is that as the name of the columns of my view are not always the same I can't create my type table using pipelined.

Any idea how could do that or any other solution which let me use my view in a select statment?

Many thanks.

Do I understand correctly that the column alias in the view depends on sysdate? If so why ??? — Rene
– Rene, Commented Jul 7, 2011 at 11:48
Thanks for answering. Exactly, the column alias is dinamic, I have edited my question to try to explain it better — Tamara
– Tamara, Commented Jul 7, 2011 at 13:06
But aren't the columns names always going to be JAN, FEB, MAR... etc, just in a different order ? In which case, the 'trick' is generating the right column order in the SELECT or in your output tool — Gary Myers
– Gary Myers, Commented Jul 8, 2011 at 0:03
Hello, my first idea was to do a case in my select statment with all the months but it always retrieved JAN, FEB, MARCH...DIC (in that order) whereas my report should change depending on the month it is executed, I mean the result could be JUN, JUL, AUG...MAY (in that order) is something like that what you meant? thanks for answering — Tamara
– Tamara, Commented Jul 8, 2011 at 10:19

Michal Pravda · Accepted Answer · 2011-07-07 11:57:36Z

3

Either I have completely misunderstood your requirements or this is a design nonsense. It's a very rare situation where you have to dynamically do any DDL. To have to create dynamically a VIEW is very suspicious.

However you didn't name your columns in the select part so they get implicit names and that may be what makes tham "dynamic". When you make any expression in your select statements, always name them:

create or replace view v1 as 
select a + b as a_plus_b 
  from table t;

Have no idea what pipelining has to do with this at all.

answered Jul 7, 2011 at 11:57

Michal Pravda

8294 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tamara Over a year ago

Hello, first of all thanks, for answering so fast. I have tried to explain better my question I hope it helps..

Rene · Accepted Answer · 2011-07-08 07:44:20Z

0

Don't give a column the name of some parameter. Give it the name of the value it returns. In your case it would be: "Avr_Usage_this_Month", "Avr_Usage_previous_Month". Now the columns always have the same name. Much easier to use your view now.

Some further remarks:

SUBSTR(ADD_MONTHS(SYSDATE, -1),4,3)
substr(ADD_MONTHS(SYSDATE, -2),4,3)

Wouldn't this result in the same column name, unless you run this at the second day of the month?

Also, always use explicit date format strings. You cannot rely on these statements to always return the name of a month.

answered Jul 8, 2011 at 7:44

Rene

10.6k5 gold badges37 silver badges48 bronze badges

1 Comment

Tamara Over a year ago

Hello, your idea would be perfect if I just had two columns but I have twelve so I can't do that. And about the results that these two functions return are JUN and MAY. For example, the function ADD_MONTHS(SYSDATE, -1) returns 08/06/2011 09:10:50 and adding SUBSTR(ADD_MONTHS(SYSDATE, -1),4,3) it gets just the month, in that case JUN. And thanks for your advise, I will do this instead: SUBSTR(ADD_MONTHS(TO_DATE(SYSDATE,'DD/MM/YYYY'), -1),4,3)

Gary Myers · Accepted Answer · 2011-07-08 12:40:27Z

0

I'd go with one view/function that selects the twelve columns as

month_minus_1, month_minus_2,....month_minus_12

Then either the end client can figure out the report column headers title by itself. Failing that hardcode the 12 options and just have a fresh script run on the 1st of the month through DBMS_SCHEDULER.

answered Jul 8, 2011 at 12:40

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

2 Comments

Tamara Over a year ago

Hello Gary, I have been reading about DBMS_SCHEDULER and have managed to do this: ** But when trying to execute it I get the following error: a non-numeric character was found where a numeric was expected at line2. Any idea why? many thanks

Tamara Over a year ago

BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'my_job', job_type => 'PLSQL_BLOCK', job_action => 'DECLARE vSQL VARCHAR2(4000); BEGIN vSQL := CREATE OR REPLACE VIEW IFSAPP.RLDateTest AS my SELECT shown above; EXECUTE IMMEDIATE vSQL;end;', start_date => 'SYSDATE', repeat_interval => 'FREQ=MONTHLY;BYDAY=SA,SU;' ); END;

Collectives™ on Stack Overflow

How to pipeline a function with a dynamic name of columns?

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related