Solved

extract day from TO_DSINTERVAL

Posted on 2004-05-01
14
723 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
 
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
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.

 

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.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

760 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

20 Experts available now in Live!

Get 1:1 Help Now