Change record field in each loop iteration

Question

I have to generate a report which shows sales for each country, per year. (Fixed amount of years)

I managed to create this query:

SELECT sum(dwfactsales.amount) as "2009" INTO v_dwrecord."2009"
        FROM dwfactsales
        JOIN dwdimcustomer ON dwdimcustomer.customerkey = dwfactsales.customerkey
        JOIN dwdimdate ON dwdimdate.datekey = dwfactsales.datekey
        WHERE dwdimcustomer.country = 'Australia' and dwdimdate.calenderyear = 2009;
        v_dwreport(1) := v_dwrecord;

This query works perfectly to gather the amount of sales for the year 2009 in Australia. However, I want to use this query for each year and each country. I can iterate the countries, but I can't seem to iterate the years.

My record and table is declared as:

TYPE t_DWRECORD IS RECORD(
    COUNTRY     dwdimcustomer.country%TYPE,
    "2009"      NUMBER,
    "2010"      NUMBER,
    "2011"      NUMBER,
    "2012"      NUMBER,
    "2013"      NUMBER,
    "2014"      NUMBER,
    TOTAL       NUMBER
    );
TYPE t_DWTABLE IS TABLE OF t_DWRECORD INDEX BY BINARY_INTEGER;

I created the following snippet:

v_count := 0;
FOR country_rec IN (SELECT DISTINCT country FROM dwdimcustomer order by country asc) LOOP
    FOR year_rec IN (SELECT DISTINCT calenderyear FROM dwdimdate order by calenderyear asc) LOOP
        SELECT sum(dwfactsales.amount) as "2009" INTO v_dwrecord."2009"
        FROM dwfactsales
        JOIN dwdimcustomer ON dwdimcustomer.customerkey = dwfactsales.customerkey
        JOIN dwdimdate ON dwdimdate.datekey = dwfactsales.datekey
        WHERE dwdimcustomer.country = country_rec.country and dwdimdate.calenderyear = year_rec.calenderyear;
        v_dwreport(v_count) := v_dwrecord;
        v_count := v_count+1;
    END LOOP;
END LOOP;

As you can see there's still the part INTO v_dwrecord."2009", I want to change it to the year_rec.calenderyear in each iteration. How can I do this? I tried using the EXECUTE IMMEDIATE with a VARCHAR2(500) variable, which contained a dynamic query. It looked like this: INTO v_dwrecord." || year_rec.calenderyear ||".

Working with the EXECUTE IMMEDIATE gave me the following errors:

00000 - "SQL command not properly ended" *Cause:
*Action:

00000 - "%s: invalid identifier" *Cause:
*Action:

ORA-00911: invalid character

Everything seems to break when I try out creating a dynamic query. How do I tackle this problem? Should I continue with dynamic queries or is there an easier way to change v_dwrecord."2009"?

Sample output:

Show the sample output. There are better ways to do it than using the block of code you have posted — Kaushik Nayak, Nov 15 '17 at 15:40

Jorge Campos · Accepted Answer · 2017-11-15T17:43:56.417

1

You can use a PIVOT technique to do what you want without the need of the FOR and the RECORD. It would be like this:

select dwdimcustomer.country,
       sum(case when dwdimdate.calenderyear = 2009 
                  then dwfactsales.amount else 0 end) as "2009",
       sum(case when dwdimdate.calenderyear = 2010 
                  then dwfactsales.amount else 0 end) as "2010",
       ....
       sum(case when dwdimdate.calenderyear = 2014 
                  then dwfactsales.amount else 0 end) as "2014",
       sum(dwfactsales.amount) as total
  FROM dwfactsales
      JOIN dwdimcustomer 
             ON dwdimcustomer.customerkey = dwfactsales.customerkey
      JOIN dwdimdate 
             ON dwdimdate.datekey = dwfactsales.datekey
 GROUP BY dwdimcustomer.country
 ORDER BY <what ever you want here>;

edited Nov 15 '17 at 17:43

answered Nov 15 '17 at 16:53

Jorge Campos

22,647
7
56
87

This select returns exactly what I need! I need to put all this data in a table and return it. My old solution was a table with t_DWRECORD records. What would be the most efficient way to convert your select into a table? – Tumladhir Nov 15 '17 at 18:01
You can create a function and return it as a cursor. See this thread: https://stackoverflow.com/questions/2829880/create-an-oracle-function-that-returns-a-table – Jorge Campos Nov 15 '17 at 21:05

score 1 · Answer 2 · answered Nov 15 '17 at 16:54

I think that you can do it with bulk collect and pivot, like here:

Sample data:

create table sales as (
  select 'Australia' country, 2009 year, 150 amount from dual union all
  select 'Brasil'    country, 2009 year, 927 amount from dual union all
  select 'Australia' country, 2010 year, 456 amount from dual );

PLSQL block:

declare 
    type t is record(
        country  sales.country%type, 
        s2009    number, 
        s2010    number, 
        total    number);
    type tt is table of t index by binary_integer;

    vtt tt;
begin
    select country, s2009, s2010, nvl(s2009, 0) + nvl(s2010, 0) total
        bulk collect into vtt
        from sales
        pivot (sum(amount) for year in (2009 as s2009, 2010 as s2010));

    -- now you have your collection ready in variable vtt, show some data
    for i in 1..vtt.count loop
        dbms_output.put_line(i);
        dbms_output.put_line(vtt(i).country);
        dbms_output.put_line(' 2009: '||vtt(i).s2009);
        dbms_output.put_line(' 2010: '||vtt(i).s2010);
        dbms_output.put_line('Total: '||vtt(i).total);
    end loop;
end;

Result:

1
Brasil
 2009: 927
 2010: 
Total: 927
2
Australia
 2009: 150
 2010: 456
Total: 606

Change record field in each loop iteration

2 Answers2