Solved

Dates in oracle

Posted on 2014-11-04
11
207 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]
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 142

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 142

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 142

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 142

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
null value 15 100
Can i Import Access Table Into Oracle Using Toad 36 170
Queries 15 38
Export table into csv file in oracle 10 76
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

776 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