Dates in oracle

Hello Experts,

I want to generate the columns dynamically after passing the year for example if I  pass 2014 then I should get the data as below :

col1 , 1/1/2014	1/2/2014	1/3/2014	1/4/2014	1/5/2014	1/6/2014	1/7/2014	......31/12/2014
----------------------------------------------------------------------------------------------------------

Open in new window

LVL 17
Swadhin RaySenior Technical Engineer Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you really want to solve this in SQL / PL/SQL, or rather in some reporting tool?
generating 365 (366) columns is not going to be "trivial"...
0
Swadhin RaySenior Technical Engineer Author Commented:
If the year is a leap year then it should generate 29th Feb dates else it should only generate 28th date for the month of Feb.
And yes want to do it in PLSQL
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
I totally agree with Guy! You should defintely avoid this, but if you really want to do this, take a look at "method4 dynamic SQL" as shown here http://www.oracle-developer.net/display.php?id=422
This should take you where you want to go ;-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
as indicated, you will need dynamic sql:
http://docs.oracle.com/cd/B10500_01/appdev.920/a96590/adg09dyn.htm

this is how you would list up all the possible values:
select x.d1 + level - 1
from (select trunc(sysdate, 'YYYY') d1
        , add_months(trunc(sysdate, 'YYYY'),12) d2
         from dual
         ) x
connect by x.d1 + level - 1  < d2

Open in new window


but then, for using dynamic SQL, you will hit this limitation:
ORA-01489: result of string concatenation is too long
select listagg(  to_char(sq.d, 'DD/MM/YYYY'), ',' ) within group ( order by l ) x
from ( select d1 + level -1 d , level l
    from (select trunc(sysdate, 'YYYY') d1
          , add_months(trunc(sysdate, 'YYYY'),12) d2
          from dual
          ) x
      connect by x.d1 + level - 1  < d2
    ) sq

Open in new window


you could find alternatives by using some alternatives, but I come back to above comment: why PL/SQL and not a reporting tool which are build for exactly this
0
Swadhin RaySenior Technical Engineer Author Commented:
I have the function which will resolve this problem on ORA-01489:

	
	
	
	CREATE OR REPLACE TYPE CONCAT_AGG_TYPE AS OBJECT
(
        v_result CLOB,

        STATIC FUNCTION odciaggregateinitialize(ctx IN OUT concat_agg_type) RETURN NUMBER,

        MEMBER FUNCTION odciaggregateiterate(SELF     IN OUT concat_agg_type
                                            ,p_string IN clob) RETURN NUMBER,

        MEMBER FUNCTION odciaggregatemerge(SELF IN OUT concat_agg_type
                                          ,ctx2 IN concat_agg_type) RETURN NUMBER,

        MEMBER FUNCTION odciaggregateterminate(SELF        IN concat_agg_type
                                              ,returnvalue OUT CLOB
                                              ,flags       IN NUMBER) RETURN NUMBER
);

/

CREATE OR REPLACE TYPE BODY CONCAT_AGG_TYPE IS
        STATIC FUNCTION odciaggregateinitialize(ctx IN OUT concat_agg_type) RETURN NUMBER IS
        BEGIN
                ctx := concat_agg_type(NULL); -- initialize the concatenation to NULL
                RETURN odciconst.success;
        END odciaggregateinitialize;

        MEMBER FUNCTION odciaggregateiterate(SELF     IN OUT concat_agg_type
                                            ,p_string IN clob) RETURN NUMBER IS
        BEGIN
                IF nullif(length(SELF.v_result)
                         ,0) IS NULL
                THEN
                        SELF.v_result := p_string;
                ELSE
                        dbms_lob.append(SELF.v_result
                                       ,',' || p_string); -- Append a delimiter and new value
                END IF;
                RETURN odciconst.success;
        END odciaggregateiterate;

        MEMBER FUNCTION odciaggregatemerge(SELF IN OUT concat_agg_type
                                          ,ctx2 IN concat_agg_type) RETURN NUMBER IS
        BEGIN
                -- If merging, simply concatenate them together
                -- Since each string will either be NULL or delimiter prefixed, no need to re-delimit
                dbms_lob.append(SELF.v_result
                               ,ctx2.v_result);
                RETURN odciconst.success;
        END odciaggregatemerge;

        MEMBER FUNCTION odciaggregateterminate(SELF        IN concat_agg_type
                                              ,returnvalue OUT CLOB
                                              ,flags       IN NUMBER) RETURN NUMBER IS
        BEGIN
                -- Since we WERE prefixING the string initially with a comma, WE USED TO remove the extra here before returning.
                -- NOW WE CAN RETURN AS IS
                returnvalue := SELF.v_result;
                RETURN odciconst.success;
        END odciaggregateterminate;
END;

/
CREATE OR REPLACE FUNCTION conagg(p_string clob)
/***************************************************************************
-- FUNCTION conagg
-- PURPOSE: This Function pull the records from one column with comma  
--          separated values.
--
-- AUTHOR:
-- Sloba
-- Version 1.0
**************************************************************************/
    RETURN clob PARALLEL_ENABLE AGGREGATE
    USING concat_agg_type; 
	/

Open in new window



Now i have to run the SQL as like below in place of listagg:

select conagg(  to_char(sq.d, 'DD/MM/YYYY')) x
from ( select d1 + level -1 d , level l
    from (select trunc(sysdate, 'YYYY') d1
          , add_months(trunc(sysdate, 'YYYY'),12) d2
          from dual
          ) x
      connect by x.d1 + level - 1  < d2
    ) sq;
    

Open in new window

0
Swadhin RaySenior Technical Engineer Author Commented:
The final result I want like all  the dates should be the columns :

col1 , 1/1/2014      ,1/2/2014      ,1/3/2014


Column 1 = col1
column 2 = 1/1/2014
..
...

Column .. = 31/dec/2014
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please clarify: you want those dates as values or as column headers?
what is the big picture, here, I cannot see what you are trying to achieve...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I have the function which will resolve this problem on ORA-01489:
it will cut the resulting value , yes, but not really resolve the problem in either way
0
Swadhin RaySenior Technical Engineer Author Commented:
>> you want those dates as values or as column headers?

Want this to be into column headers.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and the values comes from some other column, related to "col1 value".
again: trying to do this will not be efficient, really.
I will suggest the column headers will be 01/01, 02/01, 03/01 ... etc until 31/12 (and including 29/02 in all cases), and you refer in the sql to the current year.
something like this:
select col1
  , sum( case when to_char(date_col , 'MMDD') = '0101' then other_col end) "01/01"
  , sum( case when to_char(date_col , 'MMDD') = '0102' then other_col end) "02/01"
  , sum( case when to_char(date_col , 'MMDD') = '0103' then other_col end) "03/01"
  ...
  , sum( case when to_char(date_col , 'MMDD') = '1231' then other_col end) "31/12"
from yourtable
group by col1

Open in new window

hope this works out for you and still be "efficient". of course, it may not be SUM() function, you may apply any other aggregate function...
0
Swadhin RaySenior Technical Engineer Author Commented:
thanks a lot experts ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.