Solved

extract day from TO_DSINTERVAL

Posted on 2004-05-01
14
733 Views
Last Modified: 2010-05-18
I'm trying to extract the day portion of an TO_DSINTERVAL data type using
extract (DAY FROM :ELAPSED_TIME), elapsed_time is the name of the field, I'm working in report builder. Below is what I've put in a function to extract the day:

function Elapsed_DaysFormula return NUMBER is

days_elapsed project_consultant.elapsed_time%type;

      CURSOR days_cursor IS
        SELECT extract(day from elapsed_time)
         FROM project_consultant;

      days_row days_cursor%RowType;

BEGIN


      FOR days_row IN days_cursor
      LOOP
        days_elapsed := days_row.elapsed_time;
        return days_elapsed;
      END LOOP;

END;

getting the error component elapsed_time must be declared.

0
Comment
Question by:Barbara69
  • 7
  • 3
  • 2
  • +1
14 Comments
 
LVL 3

Expert Comment

by:dbms_chu
ID: 10970593
You need to have a datetime value in the extract() function.

Try extract(day from some_date+elapsed_time) instead of extract(day from elapsed_time).

You will also need to change your variable data type.

create or replace function Elapsed_DaysFormula  
return NUMBER is

-- change datatype
--days_elapsed project_consultant.elapsed_time%type;
days_elapsed varchar2(20);


     CURSOR days_cursor IS
-- add interval expression that resolves to a date.
       SELECT extract(day from sysdate-elapsed_time) elapsed_time
        FROM project_consultant;

     days_row days_cursor%RowType;

BEGIN


     FOR days_row IN days_cursor
     LOOP
       days_elapsed := days_row.elapsed_time;
       return days_elapsed;
     END LOOP;

END;

0
 
LVL 9

Expert Comment

by:pratikroy
ID: 10970704
Sorry, your question, and function description do not match.

Could you tell me what is the datatype of project_consultant.elapsed_time ?

Assuming your datatype is INTERVAL DAY TO SECOND. And you need to find the day part in the interval.

You cannot use the EXTRACT function, because it works on a DATE field, and not on the interval field.

Try my example first :
SQL> desc tab_dsint
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ELAPSED_TIME                                       INTERVAL DAY(3) TO SECOND(6)

SQL> select * from tab_dsint;

ELAPSED_TIME
---------------------------------------------------------------------------
+100 10:00:00.000000
+001 00:00:00.000000
-050 10:00:00.000000
+000 12:40:00.000000

create or replace function Elapsed_DaysFormula
return NUMBER is
days_elapsed varchar2(10);
CURSOR days_cursor IS
select substr(elapsed_time,1,instr(elapsed_time,' ')) elapsed_days from tab_dsint;
begin
for c1_row in days_cursor
loop
select c1_row.elapsed_days into days_elapsed from dual;
return days_elapsed;
end loop;
end;
/

SQL> select Elapsed_DaysFormula from dual;

ELAPSED_DAYSFORMULA
-------------------
                100

---------------
Taking the same approach, lets look at a solution for you :
create or replace function Elapsed_DaysFormula
return NUMBER is
days_elapsed varchar2(10);
CURSOR days_cursor IS
select substr(elapsed_time,1,instr(elapsed_time,' ')) elapsed_days from project_consultant;
begin
for days_row in days_cursor
loop
select days_row.elapsed_days into days_elapsed from dual;
return days_elapsed;
end loop;
end;
/

Hope this helps. Please let me know if you have any more questions.

Note : You are selecting the INTERVAL field from a table, but you are returning only the day part of first interval that you get. Though this table might have more than one rows. Is this what you want ?

 


0
 

Author Comment

by:Barbara69
ID: 10971734
Here's the solution I came up with and it works, extracting the day part from an dsinterval data type:

       days_elapsed NUMBER(10);      

     CURSOR days_cursor IS
       SELECT extract(day from elapsed_time)elapsed_time
        FROM project_consultant
        WHERE elapsed_time = :elapsed_time;

     days_row days_cursor%RowType;

BEGIN


     FOR days_row IN days_cursor
     LOOP
       days_elapsed := days_row.elapsed_time;
     
     END LOOP;
  return days_elapsed;
END;



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 3

Expert Comment

by:dbms_chu
ID: 10972646
So your original error was corrected by specifying an alias: "elapsed_time"

It looks like extract(day from elapsed_time) returns the number of days in the interval.

Unless there is a unique constraint on elapsed_time, then your loop will return the last record in the cursor.  In other words, if multiple records in project_consultant table have the same elapsed_time, then your cursor will loop through all of the qualifying records and return the last record to the caller.  It seems like you would want to be more certain about which record you return.

0
 

Author Comment

by:Barbara69
ID: 11103261
this can be accomplished by just doing:
EXTRACT( DAY FROM PROJECT_CONSULTANT.ELAPSED_TIME ) "Elapsed Time"  
0
 

Author Comment

by:Barbara69
ID: 11441920
please close or delete, thank you.
0
 

Author Comment

by:Barbara69
ID: 11491335
Please close this question Q_20974993.html
0
 

Author Comment

by:Barbara69
ID: 11991360
Please close this question.
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 11995995
Hi Barbara, please raise a 0 point question in the Community Support TA at http://www.experts-exchange.com/Community_Support/ 

They would be able to close this question and refund your points.

0
 

Author Comment

by:Barbara69
ID: 12002592
I'm not understanding what you're telling me to do.
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 12005439
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 12064594
Please read entire thread.  Asker solved or content important.
Closed, 250 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Moderator
0
 

Author Comment

by:Barbara69
ID: 12073563
thanks
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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

809 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