[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

datediff in pl/sql

Posted on 2011-10-16
5
Medium Priority
?
1,382 Views
Last Modified: 2013-12-24
How to create ,pl/sql, a function called ORD_SHIP_SF that calculates the number of days between the date the basket was created and the shipped date . Where dt ordered and dtshipped are two columns in a table.
0
Comment
Question by:lojayn
  • 3
  • 2
5 Comments
 

Author Comment

by:lojayn
ID: 36975870
How to create ,pl/sql, a function called ORD_SHIP_SF that calculates the number of days between the date the basket was created and the shipped date . Where dt ordered and dtshipped are two columns in a table.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36976200

There is no need to create a function, as the function already exists...


 select  dateDiff(d,createdDate,shipDate)  as NumbDays




0
 

Author Comment

by:lojayn
ID: 36976274
--the company wants to analyze the time lapse between order date and shipping date. Any orders not shipped within a day of the order being placed is going to get someone in trouble!
--Create a function called ORD_SHIP_SF that calculates the number of days between the date the basket was created and the shipped date (this is called the shipping delay).
--Use your function in a SELECT to list the shipping delays for all baskets.
============================================================
I have to write a function that CREATE OR REPLACE FUNCTION ORD_SHIP_SF
(P_ID_BASKET NUMBER)
RETURN NUMBER
IS

lv_DIFF_DAYS NUMBER(5);

BEGIN
select TO_DATE(bs.dtstage,'dd-mon-yyyy')- TO_DATE(b.dtordered,'dd-mon-yyyy') As difference  INTO lv_DIFF_DAYS
from bb_basket b join bb_basketstatus bs
on b.idbasket = bs.idbasket
WHERE b.idbasket= P_ID_BASKET;
RETURN lv_DIFF_DAYS;
END;
/
 compiles fine, to test the function

SELECT ORD_SHIP_SF( IDBASKET)
FROM BB_BASKET
 
--Error report:
--SQL Error: ORA-01422: exact fetch returns more than requested number of rows

what do I need to fix?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36976337
ooohhh, no wonder it doesn't make any sense.  It's a school assignment!

It doesn't really make sense to pass the ID to the function and do the select again.. you already have the values you need in the select statement.   It should be like this...

 
ALTER function ORD_SHIP_SF (@orderDate dateTime, @shipDate dateTime) returns integer as
begin
   return dateDiff(d,@orderDate,@shipDate)
end


select ord_ship_sf (orderDate, shipDate)
 from bb_basket

Open in new window



However, assuming you HAVE to do it the way you posted, the reason for your error is this.

This query is returning more than one record.   Try putting into your database with the ID and see what returns...

select TO_DATE(bs.dtstage,'dd-mon-yyyy')- TO_DATE(b.dtordered,'dd-mon-yyyy') As difference  INTO lv_DIFF_DAYS
from bb_basket b join bb_basketstatus bs
on b.idbasket = bs.idbasket
WHERE b.idbasket= P_ID_BASKET;


When you SELECT ... INTO.... you need to fetch ONE record otherwise you will get an error.

Try and determine why you are getting more than one record... probably because of the BB_BasketStatus table has more than one record for the Basket ID ...

0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 36976380

Also, it seems like this is Oracle?   If not...  this may not be applicable...


It seems like you should not be converting a date to a date...  

select TO_DATE(bs.dtstage,'dd-mon-yyyy')- TO_DATE(b.dtordered,'dd-mon-yyyy')

In oracle, you can subtract dates, if you don't a decimal number as a result, then you can truncate it to an integer...

select   bs.dtstage - b.dtordered into lv_diff_days
from...



0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

834 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