--%>
Assignment Help - homework help

Compute Days Between Ordering and Shipping in SQL

An analyst in the quality assurance office reviews the time lapse between receiving an order and shipping an order. Any orders which have not been shipped in a day of the order being positioned are investigated. Build a function named ORD_SHIP_SF which computes the number of days between the date the basket was made and the shipping date. The function must return a character string which indicates “OK” when the order was shipped in a day or “CHECK” if it was not. The IDSTAGE column of the BB_BASKETSTATUS table ppoints out the item is shipped with a value of 5 and the DTSTAGE column is the shipping date. The TDORDERED column of the BB_BASKET table is the order date. Utilize the function in an anonymous block which uses a host variable to receive the basket id to check basket 3.

E

Expert

Verified

create or replace function "ORD_SHIP_SF"
(baskid in NUMBER)
return VARCHAR2
is
NUMDAYS NUMBER;
begin
SELECT TO_DATE(bs.DTSTAGE,'DD-MM-YYYY')-TO_DATE(bb.DTORDERED,'DD-MM-YYYY') INTO NUMDAYS FROM BB_BASKET BB,BB_BASKETSTATUS BS WHERE BB.IDBASKET=BS.IDBASKET and BB.IDBASKET=baskid;
IF NUMDAYS>0 THEN
   RETURN 'OK';
END IF;
RETURN 'CHECK';
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No Data Found');
      RETURN 'CHECK';
  WHEN OTHERS THEN
      RETURN 'CHECK';
 
end;

   Related Questions in Programming Languages

  • Q : XPATH selector Normal 0 false false

    Normal 0 false false

  • Q : What is Timeslice Timeslice : It is the

    Timeslice: It is the amount of running time assigned to a process or thread prior to the scheduler considers the other to be run. The process or thread will not be capable to employ its full allocation of time when it becomes blocked or preempted thro

    		•
  • Q : Define Uniform Resource Locator Uniform

    Uniform Resource Locator: It is a Uniform Resource Locator (abbreviated as URL) expands the concept of file access from a wholly local context to one in which the resources are named uniformly, irrespective of where they may be physically situated. A

    		•
  • Q : Define the term Blank final variable

    Blank final variable: A final variable which is not initialized as portion of its declaration. This variable should be initialized in either an instance initialization block or every of the constructors for its class before it is employed. A static bl

    		•
  • Q : Define Package Package : The named

    Package: The named grouping of classes and interfaces which gives a package namespace. Classes, interfaces and class members devoid of an explicit public, protected or private access modifier {access!modifier} encompass package visibility. The public

    		•
  • Q : CSS and CSS3 State some of the

    State some of the difference between the CSS and the CSS3.

    		•
  • Q : State the term multi threading State

    State the term multi threading.

    		•
  • Q : Differences between logical and

    What are the differences between logical and physical address spaces?

    		•
  • Q : Explain If-else statement If-else

    If-else statement: It is a control structure employed to select between performing one of two alternative events.     if(boolean-expression){        // Statem

    		•
  • Q : What is an Iterator pattern Iterator

    Iterator pattern: It is a common pattern in which the contents of a collection are iterated above in order. The Iterator pattern frees a client of data from requiring details of how the data is stored. This pattern is maintained by the Iterator and Li

    		•