--%>

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 : Microsoft Access I have attached a

    I have attached a database and a PDF with 5 questions. I need the 5 questions answered with 5 queries in the database. Question 3 needs a crosstab query. All the questions require the proper join.

  • Q : What is High level programming language

    High level programming language: Languages like Ada, Java, C++, and so on which give programmers with features like control structures, classes, methods, packages, and so forth. Such features are mostly independent of any specific instruction set, and

  • Q : Explain main quality of unsuitable life

    What are the main quality consequences of selecting an unsuitable life cycle model for a software project?

  • Q : Explain Look-and-feel Look-and-feel :

    Look-and-feel: The visual impression and interaction style given by a user interface. This is mainly the responsibility of the window manager (that is, in collaboration with the fundamental operating system) running on a specific computer. This refers

  • Q : Define Single line comment Single line

    Single line comment: A comment is in the form:     // this line will be avoided by the compiler.

  • Q : State the term URN State the term URN?

    State the term URN?

  • Q : Definition of Role Normal 0 false false

    Normal 0 false false

  • Q : Define the term Pixel Define the term

    Define the term Pixel: It is a `picture element' - usually a colored dot on a screen.

  • Q : Define Unbounded repetition Unbounded

    Unbounded repetition: The repetition where statements in a loop's body are executed an arbitrary number of times, according to the consequences of the statements in the loop's body. All of the loop control structures in the Java offer for unbounded re

  • Q : Introduction to Programming for

    The purpose of this assignment is to get you started with C++ programming. You'll develop simple programs (with input and output) to solve simple mathematical and engineering problems. 1. Write a program to compute the area A of an