Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Dates in oracle

Posted on 2014-11-04
11
208 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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40421045
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
ID: 40421053
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
ID: 40421061
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
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 143

Assisted Solution

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

Author Comment

by:Swadhin Ray
ID: 40421096
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40421248
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40421249
>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
ID: 40421262
>> you want those dates as values or as column headers?

Want this to be into column headers.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 40421273
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
ID: 40421471
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Shredding xml into an oracle 11g Database 2 58
oracle DR - data guard failover. 18 45
oracle forms question 22 38
PL/SQL: ORA-00979: not a GROUP BY expression 3 26
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

791 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