--%>
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 : What is Hash function Hash function : A

    Hash function: A function employed to generate a hash code from the random contents of an object. The classes can override the hash Value method, inherited from the Object class, to state their own hash function.

    		•
  • Q : Explain Multiple inheritance Multiple

    Multiple inheritance: The capability of a class or interface to expand more than one interface or class. In Java, multiple inheritance is only accessible in the circumstances which are shown below: An interf

    		•
  • Q : State the term snooping State the term

    State the term snooping?

    		•
  • Q : State-space Reduction in Promela model

    State-space Reduction: Two language statements are used to reduce the number of states in a Promela model: atomic and d-step. 1553_state</span></p> </div> <!-- /comment-box --> </li> </td> </tr><tr> <td> <li> <div class=

    Q : What is Carriage return Carriage return

    Carriage return: The \r character. It is also used as a synonym for the `Return' or `Enter' key employed to terminate a line of text and the name derives from the carriage on mechanical typewriter.

    		•
  • Q : Explain the role XSL that dynamically

    Explain the role which XSL can play while dynamically generating HTML pages by a relational database?

    		•
  • Q : What is Avoid Redundancy Avoid

    Avoid Redundancy: While not every form of redundancy is as bad from a verification perspective as it is from a maintenance point of view, behavioral redundancy to re-create (local) state can impose problems because the model checker does not distingui

    		•
  • Q : Define Statement Statement : The

    Statement: The fundamental building block of Java method. There are numerous different kinds of statement in Java, for example, the assignment statement, if statement, while loop and return statement.

    		•
  • Q : Explain Relative filename Relative

    Relative filename: It is a filename whose full path is associative to some point within a file system tree-frequently the present working folder (that is, directory). For example:   

    		•
  • Q : State the term VHDL or Verilog State

    State the term VHDL or Verilog? Answer: VHDL is extremely high speep integrated chips hardware descripted language as well as verilog is use to verify logic.

    		•