• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 782
  • Last Modified:

extract day from TO_DSINTERVAL

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
Barbara69
Asked:
Barbara69
  • 7
  • 3
  • 2
  • +1
1 Solution
 
dbms_chuCommented:
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
 
pratikroyCommented:
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
 
Barbara69Author Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
dbms_chuCommented:
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
 
Barbara69Author Commented:
this can be accomplished by just doing:
EXTRACT( DAY FROM PROJECT_CONSULTANT.ELAPSED_TIME ) "Elapsed Time"  
0
 
Barbara69Author Commented:
please close or delete, thank you.
0
 
Barbara69Author Commented:
Please close this question Q_20974993.html
0
 
Barbara69Author Commented:
Please close this question.
0
 
pratikroyCommented:
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
 
Barbara69Author Commented:
I'm not understanding what you're telling me to do.
0
 
pratikroyCommented:
0
 
ee_ai_constructCommented:
Please read entire thread.  Asker solved or content important.
Closed, 250 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Moderator
0
 
Barbara69Author Commented:
thanks
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 7
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now