Solved

Dates in oracle

Posted on 2014-11-04
11
195 Views
Last Modified: 2014-11-04
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

0
Comment
Question by:Swadhin Ray
  • 5
  • 5
11 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
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
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 100 total points
Comment Utility
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
Comment Utility
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
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
>> you want those dates as values or as column headers?

Want this to be into column headers.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
Comment Utility
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
 
LVL 16

Author Closing Comment

by:Swadhin Ray
Comment Utility
thanks a lot experts ...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now