Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2007
    Posts
    32

    Unanswered: SYSDATE and Oracle problems

    Hi. I am trying to do an assignment for uni and am having a small problem... My Oracle database is for managing a DVD Hire system.. and this question is to write a query to view all DVD's that are due tomorrow. The way I thought to do this would be to write 'SYSDATE+1'... but I get 0 rows returned, however If i change the statement to have tomorrow actual date, i do get 1 row returned.

    Does anybody know why? Is my SYSDATE+1 not right?

    Below is my script i am working on, and I am positive their is a record with tomorrows date that should be returned because it works when I try the script with the actual tomorrows date, rather than SYSDATE+1

    Code:
    SELECT MOVIE.MOVIE_TITLE AS "Movie Title"
    FROM RENTAL,COPY,MOVIE
    WHERE RENTAL.COPY_ID=COPY.COPY_ID AND RENTAL.RENT_DUE_DATE=SYSDATE+1
    ORDER BY MOVIE.MOVIE_TITLE ASC;
    Many thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    SYSDATE actually returns the date and time so if you run the query today at 12:34:56 then your query is looking for rows with a rent_due_date of tomorrow at exactly 12:34:56!

    Use TRUNC to remove the time component of the any date:

    SELECT MOVIE.MOVIE_TITLE AS "Movie Title"
    FROM RENTAL,COPY,MOVIE
    WHERE RENTAL.COPY_ID=COPY.COPY_ID AND RENTAL.RENT_DUE_DATE=TRUNC(SYSDATE)+1
    ORDER BY MOVIE.MOVIE_TITLE ASC;

    BTW You seem to be missing the join conditions for MOVIE...

  3. #3
    Join Date
    Aug 2007
    Posts
    32
    Oooooo well that would explain it ey.

    Thanks heaps for that. One more quick question... my specifications says to "display the string 'Currently in Stock'.... how would i go about this... the question is

    "for each movie which has copies currently in stock: display the string 'Currently in Stock', the movie id, movietitle, the total purchase cost of the in stock copies, the number of in stock copies and the number of times thesein stock copies of this movie have been borrowed"

    which i am fine with, but I just dont understand how I get a string to be displayed.

    Many thanks

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    select 'Currently in stock' as title, ...

  5. #5
    Join Date
    Aug 2007
    Posts
    32
    it ran fine..but still not title?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Please show what you ran, and what you got.

  7. #7
    Join Date
    Aug 2007
    Posts
    32
    Well I spent all yesterday and even most of the night doing my assignment, and its gone! Its no longer on my memory stick or anywhere i dont konw why! Anyways im doing it again... and the question actually was more than i thought.. it says this..


    Code:
    for each movie which has copies currently in stock: display the string 'Currently in Stock', the movie id, movie
    title, the total purchase cost of the in stock copies, the number of in stock copies and the number of times these
    in stock copies of this movie have been borrowed, and
    for each movie which has copies which have been disposed of: display the string 'Disposed of Stock', the movie
    id, movie title, the total cost of the disposed of copies, the number of disposed of copies and the number of
    times these disposed of copies of this movie have been borrowed.
    The results from this listing should be displayed in a single result output. (6 marks)
    I can write the code as I have the following:
    Code:
    /* Displays all movies which has copies in stock */
    SELECT MOVIE.MOVIE_ID AS "Movie ID",
           MOVIE.MOVIE_TITLE AS "Movie Title",
           SUM(COPY.COPY_PURCHASE_PRICE) AS "Total Purchase Price",
           COUNT(*) AS "Total Instock Copies"
    FROM   MOVIE,RENTAL,COPY
    WHERE  MOVIE.MOVIE_ID=COPY.MOVIE_ID AND COPY.COPY_ID=RENTAL.COPY_ID AND COPY.COPY_INSTOCK='Y'
    GROUP BY MOVIE.MOVIE_ID, MOVIE.MOVIE_TITLE
    ORDER BY MOVIE.MOVIE_TITLE ASC;
    
    /* Displays all movies which have been disposed of */
    SELECT MOVIE.MOVIE_ID AS "Movie ID",
           MOVIE.MOVIE_TITLE AS "Movie Title",
           SUM(COPY.COPY_PURCHASE_PRICE) AS "Total Purchase Price",
           COUNT(*) AS "Total Instock Copies"
    FROM   MOVIE,RENTAL,COPY
    WHERE  MOVIE.MOVIE_ID=COPY.MOVIE_ID AND COPY.COPY_ID=RENTAL.COPY_ID AND COPY.COPY_INSTOCK='N'
    GROUP BY MOVIE.MOVIE_ID, MOVIE.MOVIE_TITLE
    ORDER BY MOVIE.MOVIE_TITLE ASC;
    But, I dont know how to get it into a single result output that have headings such as "Currently in Stock" and "Disposed of Stock"

    Can you please provide just a nudge in the right direction if you know how?

    Thanks a heap

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    /* Displays all movies which has copies in stock */
    SELECT MOVIE.MOVIE_ID AS "Movie ID",
           MOVIE.MOVIE_TITLE AS "Movie Title",
           SUM(COPY.COPY_PURCHASE_PRICE) AS "Total Purchase Price",
           COUNT(*) AS "Total  Copies",
           sum(decode,COPY.COPY_INSTOCK,'Y',1,0) "Copies in stock",
           sum(decode,COPY.COPY_INSTOCK,'N',1,0) "Copies disposed"
    FROM   MOVIE,RENTAL,COPY
    WHERE  MOVIE.MOVIE_ID=COPY.MOVIE_ID AND COPY.COPY_ID=RENTAL.COPY_ID
    GROUP BY MOVIE.MOVIE_ID, MOVIE.MOVIE_TITLE
    ORDER BY MOVIE.MOVIE_TITLE ASC;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •