| Re: Written Assignment (+ example code) [message #672649 is a reply to message #672648] |
Sun, 21 October 2018 14:13   |
cwilliam912
Messages: 30
Registered: February 2009
|
Member |
|
|
I understand and this is what my professor want for the assignment:
For the assignment, create an average function similar to the example, and use that function to print to screen the average # of guests per room, ordered by room number. Your output will have three columns 1) room number, 2) avg guests per night, 3) # of nights booked. Make sure you round the average column to two places right of the decimal place, and base it on total guests, not just adults. Your output should be similar to the SQL statement below. Run this SQL statement from the PL/SQL prompt:
SELECT ROOMNUM, AVG(ADULTCNT + CHILDCNT) AS "AVG GUEST PER NIGHT", COUNT(*) AS "BOOKED NIGHTS" FROM DDI.LEDGER_VIEW GROUP BY ROOMNUM ORDER BY ROOMNUM;
You output should
1) Use a function
2) Be carefully formatted, something similar to the example, but also with column headers.
3) Code should use clear variable names. If you reuse code from other projects you shouldn't keep a variable named Salary and use it for room number without changing the name.
4) Include the exception handler call for the loop. Remember previously, if the for loop doesn't find any records it will not automatically call the no_data_found exception handler. You can test it by asking for the department_id at run time and choosing one that doesn't exist.
5) Include comments, header and code
SET SERVEROUT ON
DECLARE
DDI_REC DDI.LEDGER_VIEW %ROWTYPE; --declaring DDI_REC
DDI_ROOM DDI.LEDGER_VIEW.ROOMNUM%type := 0; --declaring DDI_ROOM and assigning it to ZERO
FOUND_ROWS BOOLEAN := FALSE; --variable to test for no_data_found exception in the FOR LOOP
BOOKED_ROOMS DDI.LEDGER_VIEW.ROOMNUM%TYPE := 0; --declaring BOOKED_ROOMS
FUNCTION AVG_GUESTS(ROOM IN NUMBER)
RETURN NUMBER
AS AVERAGE NUMBER;
BEGIN
SELECT AVG(ADULTCNT + CHILDCNT)
INTO AVERAGE
FROM DDI.LEDGER_VIEW
WHERE ROOMNUM = ROOM;
RETURN AVERAGE;
IF NOT FOUND_ROWS THEN
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND.');
END;
BEGIN
SELECT COUNT AS (*)
FROM DDI.LEDGER_VIEW
WHERE ROOMNUM = BOOKED_ROOMS;
IF NOT FOUND_ROWS THEN
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND.');
END;
BEGIN
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE ('AVG ROOM RENTALS');
DBMS_OUTPUT.PUT_LINE ('PER DDI.LEDGER_VIEW');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE (' ROOM AVG NUMBER BOOKED');
DBMS_OUTPUT.PUT_LINE (' NUMBER OF GUESTS NIGHTS');
DBMS_OUTPUT.PUT_LINE (' ---- ---------- ------');
-- LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM
FOR DDI_REC IN
(SELECT *
FROM DDI.LEDGER_VIEW
ORDER BY ROOMNUM)
LOOP
FOUND_ROWS := TRUE; --if data exist, sets variable to TRUE, so IF statement doesn't run
IF;
IF DDI_ROOM != DDI_REC.ROOMNUM
THEN
DDI_ROOM := DDI_REC.ROOMNUM;
DBMS_OUTPUT.PUT_LINE(' ' ||DDI_ROOM||' '||TO_CHAR(AVG_GUESTS(DDI_ROOM), '9.99')|| ' '||TO_CHAR(BOOKED_ROOMS));
END IF;
END LOOP; -- End of loop
IF
IF NOT FOUND_ROWS
THEN -- trigger the exception below when no results are returned
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found.');
END;
/
|
|
|
|
|
|
| Re: Written Assignment (+ example code) [message #672652 is a reply to message #672649] |
Sun, 21 October 2018 14:40 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
So, this is what you are getting:
AVG ROOM RENTALS
PER LEDGER_VIEW
ROOM AVG NUMBER BOOKED
NUM OF GUESTS NIGHTS
---- ---------- ------
101 1.00
102 2.00
103 2.14
104 2.17
105 2.40
106 1.75
107 1.67
108 1.80
PL/SQL procedure successfully completed.
And, this is what your instructor wants you to get:
SCOTT@orcl_12.1.0.2.0> COLUMN "AVG GUESTS PER NIGHT" FORMAT A20
SCOTT@orcl_12.1.0.2.0> SELECT ROOMNUM,
2 TO_CHAR(AVG(ADULTCNT + CHILDCNT),'9.99') AS "AVG GUESTS PER NIGHT",
3 COUNT(*) AS "BOOKED NIGHTS"
4 FROM DDI.LEDGER_VIEW
5 GROUP BY ROOMNUM
6 ORDER BY ROOMNUM
7 /
ROOMNUM AVG GUESTS PER NIGHT BOOKED NIGHTS
---------- -------------------- -------------
101 1.00 5
102 2.00 3
103 2.14 7
104 2.17 6
105 2.40 5
106 1.75 8
107 1.67 6
108 1.80 5
8 rows selected.
You should be able to see that there isn't any place in your code where you are selecting COUNT(*) from ddi.ledger_view for a roomnum, in order to get the "booked nights". There are a lot of ways to do that, the simplest of which your instructor has already provided, but apparently wants you to do some convuluted method in your code, just to be able to show that you can do certain things. Following the instructor's example, perhaps he wants you to create a function that counts the nights per room, in the same manner that you have a function that averages the guests per night. You should be able to figure out how to do that or perhaps come up with another manner, such as selecting directly into a variable. In either case, you will need to concatenate that to the rest of your output.
[Updated on: Sun, 21 October 2018 14:44] Report message to a moderator |
|
|
|
|
|
| Re: Written Assignment (+ example code) [message #672654 is a reply to message #672652] |
Sun, 21 October 2018 15:31 |
cwilliam912
Messages: 30
Registered: February 2009
|
Member |
|
|
I don't how you got booked_night because I am getting all these errors when I try:
ERROR at line 40:
ORA-06550: line 40, column 1:
PLS-00103: Encountered the symbol "BEGIN"
ORA-06550: line 52, column 42:
PLS-00103: Encountered the symbol "AS" when expecting one of the following:
. ( * % & - + / at loop mod remainder rem ..
<an exponent (**)> || multiset
ORA-06550: line 54, column 1:
PLS-00103: Encountered the symbol "FROM" when expecting one of the following:
, into bulk
SET SERVEROUT ON
DECLARE
DDI_REC DDI.LEDGER_VIEW %ROWTYPE; --declaring DDI_REC
DDI_ROOM DDI.LEDGER_VIEW.ROOMNUM%type := 0; --declaring DDI_ROOM and assiging it to ZERO
FOUND_ROWS BOOLEAN := FALSE; --variable to test for no_data_found exception in the FOR LOOP
BOOKED_ROOMS DDI.LEDGER_VIEW.ROOMNUM%type := 0; --declaring BOOKED_ROOMS
FUNCTION AVG_GUESTS(ROOM IN NUMBER)
RETURN NUMBER
AS AVERAGE NUMBER;
BEGIN
SELECT AVG(ADULTCNT + CHILDCNT)
INTO AVERAGE
FROM DDI.LEDGER_VIEW
WHERE ROOMNUM = ROOM;
RETURN AVERAGE;
IF NOT FOUND_ROWS THEN
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND.');
END;
BEGIN
SELECT COUNT(*)
FROM DDI.LEDGER_VIEW
WHERE ROOMNUM = BOOKED_ROOMS;
IF NOT FOUND_ROWS THEN
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND.');
END;
BEGIN
DBMS_OUTPUT.NEW_LINE (' ---- ---------- ------');
DBMS_OUTPUT.PUT_LINE (' AVG ROOM RENTALS');
DBMS_OUTPUT.PUT_LINE (' PER DDI.LEDGER_VIEW');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE (' ROOM AVG NUMBER BOOKED');
DBMS_OUTPUT.PUT_LINE (' NUM OF GUESTS NIGHTS');
DBMS_OUTPUT.PUT_LINE (' ---- ---------- ------');
-- LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM
FOR DDI_REC IN
TO_CHAR(AVG(ADULTCNT + CHILDCNT),'9.99') AS "AVG GUESTS PER NIGHT",
COUNT(*) AS "BOOKED NIGHTS"
FROM DDI.LEDGER_VIEW
GROUP BY ROOMNUM
ORDER BY ROOMNUM
LOOP
FOUND_ROWS := TRUE; --if data exist, sets variable to TRUE, so IF statement doesn't run
IF DDI_ROOM != DDI_REC.ROOMNUM THEN
DDI_ROOM := DDI_REC.ROOMNUM;
DBMS_OUTPUT.PUT_LINE(' ' ||DDI_ROOM||' '||TO_CHAR(AVG_GUESTS(DDI_ROOM), '9.99')|| ' '||TO_CHAR(BOOKED_ROOMS));
END IF;
END LOOP; -- End of loop
IF NOT FOUND_ROWS THEN -- trigger the exception below when no results are returned
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found.');
END;
|
|
|
|
|
|
| Re: Written Assignment (+ example code) [message #672656 is a reply to message #672653] |
Sun, 21 October 2018 18:59 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
cwilliam912 wrote on Sun, 21 October 2018 12:57I don't have any formula for the booked night at all
Quote:I don't how you got booked_night because I am getting all these errors when I try: ...
I got booked nights by running the select statement that your instructor provided in his instructions,
For the assignment, create an average function similar to the example, and use that function to print to screen the average # of guests per room, ordered by room number. Your output will have three columns 1) room number, 2) avg guests per night, 3) # of nights booked. Make sure you round the average column to two places right of the decimal place, and base it on total guests, not just adults. Your output should be similar to the SQL statement below. Run this SQL statement from the PL/SQL prompt:
SELECT ROOMNUM, AVG(ADULTCNT + CHILDCNT) AS "AVG GUEST PER NIGHT", COUNT(*) AS "BOOKED NIGHTS" FROM DDI.LEDGER_VIEW GROUP BY ROOMNUM ORDER BY ROOMNUM;
as I previously demonstrated.
|
|
|
|
| Re: Written Assignment (+ example code) [message #672657 is a reply to message #672654] |
Sun, 21 October 2018 19:11 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You are missing the SELECT keyword in the section below.
FOR DDI_REC IN
TO_CHAR(AVG(ADULTCNT + CHILDCNT),'9.99') AS "AVG GUESTS PER NIGHT",
COUNT(*) AS "BOOKED NIGHTS"
FROM DDI.LEDGER_VIEW
GROUP BY ROOMNUM
ORDER BY ROOMNUM
You are also missing the INTO clause here:
SELECT COUNT(*)
FROM DDI.LEDGER_VIEW
WHERE ROOMNUM = BOOKED_ROOMS;
You seem to have attempted to combine different methods and just made a mess of things.
In general, you seem to have no understanding of what you are doing. You seem to not be reading either the instructions from your instructor or our responses thoroughly.
When you receive an error message, you need to read it, understand what it means, look on that line of the code, and see what is wrong. When you see something about it seeing something and expecting something else, you need to look at the code before that point and see what is missing that it expects or wrong. When it finds "BEGIN" or "AS" unexpectedly, then look on the line above to see what is missing.
This assignment appears to be way beyond anything you are capable of. You should speak with your instructor and see if you can start from the beginning with the basics.
One thing I would like to point out is that your instructor is using an unrealistic example, having you do something simple in an unnecessarily complex way. I dislike these unrealistic examples that teach people to do things inefficiently.
[Updated on: Sun, 21 October 2018 22:53] Report message to a moderator |
|
|
|
|
|
| Re: Written Assignment (+ example code) [message #672659 is a reply to message #672658] |
Mon, 22 October 2018 03:33 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You're not going to learn very much if we just write the code for you.
You need to take a step back and slow down.
Do this in stages:
1) Write a loop that loops over the table and gets all the room numbers and print that out.
2) Only once that is done and working - write the function to calculate average guests per night and plug it in.
3) Only once that is done add some code to get nights booked.
Pay attention to what Barbara said about diagnosing compilation errors - they look scary but they're actually really obvious if you follow her advice.
And format your code - it makes a lot of mistakes easier to spot.
Also - sort out your code tags, it's not difficult - you put a [code] tag before the code and a [/code] after the code
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
