Home » SQL & PL/SQL » SQL & PL/SQL » Written Assignment (+ example code)
Written Assignment (+ example code) [message #672553] Wed, 17 October 2018 15:56 Go to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
Written Assignment (Write a Program Using a Return Statement)

I am having an hard time with this assignment. I keep getting the same error. I went over it more than 50 times so can someone please out if they can please.

This is the example the professor give up to use:

SET ECHO ON
SET SERVEROUT ON

DECLARE

emp_rec hr.employees%rowtype;
crnt_dept hr.employees.department_id%type := 0;
found_rows BOOLEAN := FALSE; --variable to test for no_data_found exception in the FOR LOOP

-- DECLARE AND DEFINE FUNCTION TO CALC AVG SALARY BY DEPT

FUNCTION avg_salary(
dept_id IN NUMBER)
RETURN NUMBER
AS
average NUMBER;
BEGIN
SELECT AVG(SALARY)
INTO average
FROM HR.EMPLOYEES
WHERE department_id = dept_id;

RETURN average;

EXCEPTION
WHEN others THEN
RETURN NULL;
END;

BEGIN
DBMS_OUTPUT.PUT_LINE('---------------------------------------');
DBMS_OUTPUT.PUT_LINE(' Average Salary by Department ');
DBMS_OUTPUT.PUT_LINE('---------------------------------------');

-- LOOP CALLS FUNCTION AVG_SALARY ON EACH CHANGE IN DEPT ID/DISPLAYS AVG SALARY

FOR emp_rec IN
(SELECT *
FROM hr.employees
ORDER BY department_id)
LOOP
found_rows := TRUE; --if data exist, sets variable to TRUE, so IF statement doesn't run
IF crnt_dept != emp_rec.department_id THEN
crnt_dept := emp_rec.department_id;
DBMS_OUTPUT.PUT_LINE('Department '||crnt_dept||': '||TO_CHAR(avg_salary(crnt_dept),'$99999.99'));

END IF;
END LOOP;

/* Used to throw exception for FOR LOOP when no rows are returned from SELECT query */
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;
/

This is what he want use to go by:
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

Here is mu coding with the assignment with the error in it:

SET SERVEROUT ON
DECLARE
ROOM_REC DDI.LEDGER_VIEW%ROWTYPE;
CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE : 0 ;
NIGHT_BOOK NUMBER;
FOUND_ROWS BOOLEAN: = FALSE; --VARIABLE TO TEST FOR NO_DATA_FOUND EXCEPTION IN THE FOR LOOP

--DECLARE AND DEFINE FUNCTION TO CALC AVG GUESTS PER NIGHT BY ROOM
FUNCTION AVG_GUESTS(
ROOMNUM IN NUMBER)

AS
AVERAGE NUMBER;
BEGIN
SELECT AVG(ADULTCNT+CHILDCNT)
INTO AVERAGE
FROM DDI.LEDGER_VIEW
WHERE ROOMNUM = ROOMNUM;

RETURN AVERAGE;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE ( '--------------------------------------- ');
DBMS_OUTPUT.PUT_LINE (' ROOM NUMBER AVERAGE GUEST PER NIGHT BOOKED NIGHTS ');
DBMS_OUTPUT.PUT_LINE ( '---------------------------------------' );
--LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM/DISPLAYS ROOM NUMBER, AVERAGE GUEST PER NIGHT AND BOOKED NIGHTS
FOR ROOM_REC IN
(SELECT *
FROM DDI.LEDGER_VIEW
ORDER BY ROOMNUM)
LOOP
FOUND_ROWS : = TRUE; --IF DATA EXITS, SETS VARIABLE TO TRUE, SO IF STATEMENT DOESN'T RUN
IF CRNT_ROOM != ROOM_REC.ROOMNUM THEN
CRNT_ROOM : = ROOM_REC.ROOMNUM;
SELECT COUNT(*) INTO NIGHTS_BOOK FROM DDI.LEDGER_VIEW WHERE ROOMNUM = CRNT_ROOM;
DBMS_OUTPUT.PUT_LINE ('DDI.LEDGER_VIEW' || CRNT_ROOM|| ': '||TO_CHAR(AVG_GUESTS(CRNT_ROOM), '9999999'));

END IF;
END LOOP;
/* USED TO THROW EXCEPTION for FOR LOOP WHEN NO ROWS ARE RETURNED FROM SELECT QUERY */
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;
ERROR at line 3:
ORA-06550: line 3, column 39:
PLS-00103: Encountered the symbol ":" when expecting one of the following:
:= ( ; not null range default character
Re: Written Assignment (+ example code) [message #672554 is a reply to message #672553] Wed, 17 October 2018 16:17 Go to previous messageGo to next message
BlackSwan
Messages: 26567
Registered: January 2009
Location: SoCal
Senior Member
>FOUND_ROWS BOOLEAN: = FALSE; --VARIABLE TO TEST FOR NO_DATA_FOUND EXCEPTION IN THE FOR LOOP
not as above, but as below
FOUND_ROWS BOOLEAN := FALSE; --VARIABLE TO TEST FOR NO_DATA_FOUND EXCEPTION IN THE FOR LOOP

Please read & heed content of URL below

http://www.orafaq.com/wiki/WHEN_OTHERS


Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Written Assignment (+ example code) [message #672557 is a reply to message #672553] Wed, 17 October 2018 16:36 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
Your code:
1 DECLARE
2 ROOM_REC DDI.LEDGER_VIEW%ROWTYPE;
3 CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE : 0 ;
etc

The error
ERROR at line 3:
ORA-06550: line 3, column 39:
PLS-00103: Encountered the symbol ":" when expecting one of the following:
:= ( ; not null range default character
So, on line 3, the compiler sees a : when it expects a ":=", a "(", a ";" etc.
...
Not sure what the difficulty is here.
Re: Written Assignment (+ example code) [message #672559 is a reply to message #672557] Wed, 17 October 2018 17:28 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
Here are the DDI.LEDGER_VIEW codes:

CREATE TABLE DDI.ROOMS (
RoomNum NUMBER NOT NULL,
RoomSize VARCHAR2(6) NOT NULL,
RoomBedCnt NUMBER NOT NULL,
RoomRate NUMBER(18,2) NOT NULL,
PRIMARY KEY (RoomNum))

CREATE TABLE DDI.PATRONS (
PatronID NUMBER NOT NULL,
FirstName VARCHAR2(16) NOT NULL,
LastName VARCHAR2(16) NOT NULL,
PhoneNum VARCHAR2(12) Null,
eMail VARCHAR2(100) Null,
PRIMARY KEY (PatronID))
TABLESPACE USERS;

CREATE TABLE DDI.REGISTRATIONS (
RegID NUMBER NOT NULL,
RegDate DATE NOT NULL,
PatronID NUMBER NOT NULL,
AdultCnt NUMBER Null,
ChildCnt NUMBER Null,
RoomNum NUMBER NOT NULL,
RegNote VARCHAR2(100) Null,
PRIMARY KEY (RegID),
FOREIGN KEY (PatronID)
REFERENCES DDI.PATRONS,
FOREIGN KEY (RoomNum)
REFERENCES DDI.ROOMS)
TABLESPACE USERS;

CREATE VIEW DDI.LEDGER_VIEW
AS
SELECT REG.RegID, REG.RegDate, P.LastName, P.FirstName,
REG.AdultCnt, REG.ChildCnt, REG.RoomNum,
RM.RoomSize, RM.RoomBedCnt, RM.RoomRate
FROM DDI.ROOMS RM, DDI.PATRONS P, DDI.REGISTRATIONS REG
WHERE REG.PatronID = P.PatronID
AND REG.RoomNum = RM.RoomNum
ORDER BY REG.RegDate, REG.RoomNum;
Re: Written Assignment (+ example code) [message #672560 is a reply to message #672559] Wed, 17 October 2018 17:39 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
what does this have to do with the question asked?
Re: Written Assignment (+ example code) [message #672563 is a reply to message #672560] Thu, 18 October 2018 04:07 Go to previous messageGo to next message
cookiemonster
Messages: 13643
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your assignment operator is wrong in multiple places.
It's := (no space in between).
You've got
: on line 3
: = on lines 34 and 36

You can (and should in real code) do everything that block does in a single query.
Re: Written Assignment (+ example code) [message #672579 is a reply to message #672563] Thu, 18 October 2018 17:05 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
I got these errors when trying to run my program

SP2-0552: Bind variable "0" not declared.

AS
*
ERROR at line 11:
ORA-06550: line 11, column 1:
PLS-00103: Encountered the symbol "AS" when expecting one of the following:
return
ORA-06550: line 24, column 2:
PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map
ORA-06550: line 34, column 12:
PLS-00103: Encountered the symbol "BOOLEAN" when expecting one of the
following:
:= . ( @ % ;
The symbol "." was substituted for "BOOLEAN" to continue.
ORA-06550: line 37, column 11:
PLS-00103: Encountered the symbol ":" when expecting one of the following:
:= . ( @ % ;


SET SERVEROUT ON
DECLARE
ROOM_REC DDI.LEDGER_VIEW%ROWTYPE;
CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE : 0 ;
NIGHT_BOOK NUMBER;
FOUND_ROWS BOOLEAN := FALSE; --VARIABLE TO TEST FOR NO_DATA_FOUND EXCEPTION IN THE FOR LOOP

--DECLARE AND DEFINE FUNCTION TO CALC AVG GUESTS PER NIGHT BY ROOM
FUNCTION AVG_GUESTS(
ROOMNUM IN NUMBER)

AS
AVERAGE NUMBER;
BEGIN
SELECT AVG(ADULTCNT+CHILDCNT)
INTO AVERAGE
FROM DDI.LEDGER_VIEW
WHERE ROOMNUM = ROOMNUM;

RETURN AVERAGE;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE ( '--------------------------------------- ');
DBMS_OUTPUT.PUT_LINE (' ROOM NUMBER AVERAGE GUEST PER NIGHT BOOKED NIGHTS ');
DBMS_OUTPUT.PUT_LINE ( '---------------------------------------' );
--LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM/DISPLAYS ROOM NUMBER, AVERAGE GUEST PER NIGHT AND BOOKED NIGHTS
FOR ROOM_REC IN
(SELECT *
FROM DDI.LEDGER_VIEW
ORDER BY ROOMNUM)
LOOP
FOUND_ROWS BOOLEAN := FALSE; --VARIABLE TO TEST FOR NO_DATA_FOUND EXCEPTION IN THE FOR LOOP

IF CRNT_ROOM != ROOM_REC.ROOMNUM THEN
CRNT_ROOM : = ROOM_REC.ROOMNUM;
SELECT COUNT(*) INTO NIGHTS_BOOK FROM DDI.LEDGER_VIEW WHERE ROOMNUM = CRNT_ROOM;
DBMS_OUTPUT.PUT_LINE ('DDI.LEDGER_VIEW' || CRNT_ROOM|| ': '||TO_CHAR(AVG_GUESTS(CRNT_ROOM), '9999999'));

END IF;
END LOOP;
/* USED TO THROW EXCEPTION for FOR LOOP WHEN NO ROWS ARE RETURNED FROM SELECT QUERY */
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;
Re: Written Assignment (+ example code) [message #672580 is a reply to message #672579] Thu, 18 October 2018 17:49 Go to previous messageGo to next message
BlackSwan
Messages: 26567
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

http://www.orafaq.com/wiki/WHEN_OTHERS
Re: Written Assignment (+ example code) [message #672581 is a reply to message #672579] Thu, 18 October 2018 22:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
cwilliam912 wrote on Thu, 18 October 2018 15:05
I got these errors when trying to run my program

SP2-0552: Bind variable "0" not declared.

...

As others have previously explained, the following line:

CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE : 0 ;

should be:

CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE := 0 ;

You are missing the = which causes it to think 0 is an undeclared bind variable.



Re: Written Assignment (+ example code) [message #672582 is a reply to message #672579] Thu, 18 October 2018 22:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
cwilliam912 wrote on Thu, 18 October 2018 15:05
I got these errors when trying to run my program

...

AS
*
ERROR at line 11:
ORA-06550: line 11, column 1:
PLS-00103: Encountered the symbol "AS" when expecting one of the following:
return
...
The error message is telling you that it found the word "AS" when it was expecting the word "return". That is because you failed to declare the return data type on the line before "AS".

[Updated on: Thu, 18 October 2018 22:54]

Report message to a moderator

Re: Written Assignment (+ example code) [message #672583 is a reply to message #672579] Thu, 18 October 2018 23:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
cwilliam912 wrote on Thu, 18 October 2018 15:05
I got these errors when trying to run my program

...

ORA-06550: line 34, column 12:
PLS-00103: Encountered the symbol "BOOLEAN" when expecting one of the
following:
:= . ( @ % ;
The symbol "." was substituted for "BOOLEAN" to continue.
ORA-06550: line 37, column 11:
PLS-00103: Encountered the symbol ":" when expecting one of the following:
:= . ( @ % ;

...
On line 34, you have:

FOUND_ROWS BOOLEAN := FALSE;

You have already declared the data type earlier. You cannot declare the data type again in your loop, just assign the value:

FOUND_ROWS := FALSE;

Re: Written Assignment (+ example code) [message #672584 is a reply to message #672579] Thu, 18 October 2018 23:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
cwilliam912 wrote on Thu, 18 October 2018 15:05
I got these errors when trying to run my program

...

ORA-06550: line 37, column 11:
PLS-00103: Encountered the symbol ":" when expecting one of the following:
:= . ( @ % ;

...

As previously stated by others, you still have not removed the space between : and = in the following line:

CRNT_ROOM : = ROOM_REC.ROOMNUM;
Re: Written Assignment (+ example code) [message #672585 is a reply to message #672579] Fri, 19 October 2018 00:31 Go to previous messageGo to next message
John Watson
Messages: 7957
Registered: January 2010
Location: Global Village
Senior Member
THis
SELECT AVG(ADULTCNT+CHILDCNT)
INTO AVERAGE
FROM DDI.LEDGER_VIEW
WHERE ROOMNUM = ROOMNUM;
will select every row in the table. Unless ROOMNUM is null. Is that what you want?

[Updated on: Fri, 19 October 2018 00:32]

Report message to a moderator

Re: Written Assignment (+ example code) [message #672588 is a reply to message #672585] Fri, 19 October 2018 03:25 Go to previous messageGo to next message
cookiemonster
Messages: 13643
Registered: September 2008
Location: Rainy Manchester
Senior Member
This:
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
Is extremely bad code, if there is an error in the function then you will be getting null back without knowing what the problem is.
Delete it - any error should be allowed to propagate so you can actually tell what went wrong.

This:
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;
Is a pointless use of an exception handler since you can just write this:
IF NOT FOUND_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('NO DATA FOUND.');
END IF;

END;


Also - learn to indent your code, it'll make it a lot easier to follow.
Re: Written Assignment (+ example code) [message #672592 is a reply to message #672588] Fri, 19 October 2018 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Is extremely bad code, if there is an error in the function then you will be getting null back without knowing what the problem is.

Please read WHEN_OTHERS



Re: Written Assignment (+ example code) [message #672594 is a reply to message #672585] Fri, 19 October 2018 04:20 Go to previous messageGo to next message
cookiemonster
Messages: 13643
Registered: September 2008
Location: Rainy Manchester
Senior Member
John Watson wrote on Fri, 19 October 2018 06:31
THis
SELECT AVG(ADULTCNT+CHILDCNT)
INTO AVERAGE
FROM DDI.LEDGER_VIEW
WHERE ROOMNUM = ROOMNUM;
will select every row in the table. Unless ROOMNUM is null. Is that what you want?
And just to clarify why it'll do that - in a select (or insert/update/delete/merge) oracle will check to see if a given name is a column name before looking to see if there is a variable with that name.

You thought you were comparing a column to a parameter, but oracle is comparing the column to itself.
To avoid that you need to either:
a) rename the parameter
b) prefix it with the function name:
WHERE ROOMNUM = AVG_GUESTS.ROOMNUM;
Re: Written Assignment (+ example code) [message #672615 is a reply to message #672594] Fri, 19 October 2018 14:15 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
My professor gave use an example to go by but 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.

Here is the example that the professor gave use:
SET ECHO ON
SET SERVEROUT ON

DECLARE

emp_rec hr.employees%rowtype;
crnt_dept hr.employees.department_id%type := 0;
found_rows BOOLEAN := FALSE; --variable to test for no_data_found exception in the FOR LOOP

-- DECLARE AND DEFINE FUNCTION TO CALC AVG SALARY BY DEPT

FUNCTION avg_salary(
dept_id IN NUMBER)
RETURN NUMBER
AS
average NUMBER;
BEGIN
SELECT AVG(SALARY)
INTO average
FROM HR.EMPLOYEES
WHERE department_id = dept_id;

RETURN average;

EXCEPTION
WHEN others THEN
RETURN NULL;
END;

BEGIN
DBMS_OUTPUT.PUT_LINE('---------------------------------------');
DBMS_OUTPUT.PUT_LINE(' Average Salary by Department ');
DBMS_OUTPUT.PUT_LINE('---------------------------------------');

-- LOOP CALLS FUNCTION AVG_SALARY ON EACH CHANGE IN DEPT ID/DISPLAYS AVG SALARY

FOR emp_rec IN
(SELECT *
FROM hr.employees
ORDER BY department_id)
LOOP
found_rows := TRUE; --if data exist, sets variable to TRUE, so IF statement doesn't run
IF crnt_dept != emp_rec.department_id THEN
crnt_dept := emp_rec.department_id;
DBMS_OUTPUT.PUT_LINE('Department '||crnt_dept||': '||TO_CHAR(avg_salary(crnt_dept),'$99999.99'));

END IF;
END LOOP;

/* Used to throw exception for FOR LOOP when no rows are returned from SELECT query */
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;
/

The problem:
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

My code that I did :

SET SERVEROUT ON
DECLARE
ROOMNUM_REC DDI.LEDGER_VIEW%ROWTYPE;
FOLLOW_ROWS DDI.LEDGER_VIEW.ROOMNUM%TYPE: = 0 ;
NIGHT_BOOK NUMBER;
FOUND_ROWS BOOLEAN := FALSE; --VARIABLE TO TEST FOR NO_DATA_FOUND EXCEPTION IN THE FOR LOOP

--DECLARE AND DEFINE FUNCTION TO CALC AVG GUESTS PER NIGHT BY ROOM
FUNCTION AVG_GUESTS(
ROOMNUM IN NUMBER)

AS
AVERAGE NUMBER;
BEGIN
SELECT AVG(ADULTCNT+CHILDCNT)
INTO AVERAGE
FROM DDI.LEDGER_VIEW
WHERE ROOMNUM = AVG_GUESTS.ROOMNUM;
RETURN AVERAGE;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE ( '--------------------------------------- ');
DBMS_OUTPUT.PUT_LINE (' ROOM NUMBER AVERAGE GUEST PER NIGHT BOOKED NIGHTS ');
DBMS_OUTPUT.PUT_LINE ( '---------------------------------------' );
--LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM/DISPLAYS ROOM NUMBER, AVERAGE GUEST PER NIGHT AND BOOKED NIGHTS
FOR ROOM_REC IN
(SELECT *
FROM DDI.LEDGER_VIEW
ORDER BY ROOMNUM)
LOOP
FOUND_ROWS := TRUE; --IF DATA EXITS, SET VARIABLE TO TRUE, SO IF STATEMENT DOOESN'T RUN
IF FOLLOW_ROWS != ROOM_REC.ROOMNUM THEN
FOLLOW_ROWS: = ROOM_REC.ROOMNUM;
SELECT COUNT(*) INTO NIGHTS_BOOK FROM DDI.LEDGER_VIEW WHERE ROOMNUM = FOLLOW_ROWS;
DBMS_OUTPUT.PUT_LINE ('DDI.LEDGER_VIEW' || FOLLOW_ROWS|| ': '||TO_CHAR(AVG_GUESTS(FOLLOW_ROWS), '$99999.99'));

END IF;
END LOOP;
/ * USED TO THROW EXCEPTION for FOR LOOP WHEN NO ROWS ARE RETURNED FROM SELECT QUERY * /
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;
/
Re: Written Assignment (+ example code) [message #672616 is a reply to message #672615] Fri, 19 October 2018 14:30 Go to previous messageGo to next message
BlackSwan
Messages: 26567
Registered: January 2009
Location: SoCal
Senior Member
Which looks better?
Yours above or mine below?
SET serverout ONDECLARE 
  roomnum_rec ddi.ledger_view%ROWTYPE; 
  follow_rows ddi.ledger_view.roomnum%TYPE: = 0 ; 
  night_book NUMBER; 
  found_rows BOOLEAN := FALSE; --VARIABLE TO TEST FOR NO_DATA_FOUND EXCEPTION IN THE FOR LOOP 
  --DECLARE AND DEFINE FUNCTION TO CALC AVG GUESTS PER NIGHT BY ROOM 
  FUNCTION avg_guests( roomnum IN NUMBER) AS average NUMBER; 
  BEGIN 
    SELECT avg(adultcnt+childcnt) 
    INTO   average 
    FROM   ddi.ledger_view 
    WHERE  roomnum = avg_guests.roomnum; 
     
    RETURN average; 
  EXCEPTION 
  WHEN OTHERS THEN 
    RETURN NULL; 
  END; 
  BEGIN 
    dbms_output.put_line ( '--------------------------------------- '); 
    dbms_output.put_line (' ROOM NUMBER AVERAGE GUEST PER NIGHT BOOKED NIGHTS '); 
    dbms_output.put_line ( '---------------------------------------' ); 
    --LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM/DISPLAYS ROOM NUMBER, AVERAGE GUEST PER NIGHT AND BOOKED NIGHTS
    FOR room_rec IN 
    ( 
             SELECT   * 
             FROM     ddi.ledger_view 
             ORDER BY roomnum) 
    LOOP 
      found_rows := TRUE; --IF DATA EXITS, SET VARIABLE TO TRUE, SO IF STATEMENT DOOESN'T RUN 
      IF follow_rows != room_rec.roomnum THEN 
        follow_rows: = room_rec.roomnum; 
        SELECT count(*) 
        INTO   nights_book 
        FROM   ddi.ledger_view 
        WHERE  roomnum = follow_rows; 
         
        dbms_output.put_line ('DDI.LEDGER_VIEW' 
        || follow_rows 
        || ': ' 
        ||to_char(avg_guests(follow_rows), '$99999.99')); 
      END IF; 
    END LOOP; 
    / * used TO throw 
  EXCEPTION 
    FOR FOR 
    LOOP 
    WHEN no ROWS are returned FROM 
      SELECT query * / 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; 
/

Why are you challenged by simple formatting ?

How to use {code} tags and make your code easier to read
Re: Written Assignment (+ example code) [message #672617 is a reply to message #672616] Fri, 19 October 2018 14:47 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
Why this error

follow_rows ddi.ledger_view.roomnum%TYPE: = 0 ;
*
ERROR at line 3:
ORA-06550: line 3, column 43:
PLS-00103: Encountered the symbol ":" when expecting one of the following:
:= ( ; not null range default character

3 follow_rows ddi.ledger_view.roomnum%TYPE: = 0 ;
43 END LOOP;
Re: Written Assignment (+ example code) [message #672618 is a reply to message #672617] Fri, 19 October 2018 14:58 Go to previous messageGo to next message
BlackSwan
Messages: 26567
Registered: January 2009
Location: SoCal
Senior Member
cwilliam912 wrote on Fri, 19 October 2018 12:47
Why this error

follow_rows ddi.ledger_view.roomnum%TYPE: = 0 ;
*
ERROR at line 3:
ORA-06550: line 3, column 43:
PLS-00103: Encountered the symbol ":" when expecting one of the following:
:= ( ; not null range default character

3 follow_rows ddi.ledger_view.roomnum%TYPE: = 0 ;
43 END LOOP;

Redundancy is the best way to teach slow learners.
Redundancy is the best way to teach slow learners.
Redundancy is the best way to teach slow learners.

How many more times do you need to be told there is no space character between ": ="?
Do like below.

ddi.ledger_view.roomnum%TYPE := 0;

Re: Written Assignment (+ example code) [message #672620 is a reply to message #672618] Fri, 19 October 2018 15:18 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
I am getting all these errors why

FUNCTION avg_guests( roomnum IN NUMBER) AS average NUMBER;
*
ERROR at line 7:
ORA-06550: line 7, column 43:
PLS-00103: Encountered the symbol "AS" when expecting one of the following:
return
ORA-06550: line 19, column 3:
PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map
ORA-06550: line 55, column 6:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
end not pragma final instantiable order overriding static
member constructor map

SQL> SET serverout ON
SQL> DECLARE
2 roomnum_rec ddi.ledger_view%ROWTYPE;
3 follow_rows ddi.ledger_view.roomnum%TYPE := 0;
4 night_book NUMBER;
5 found_rows BOOLEAN := FALSE; --VARIABLE TO TEST FOR NO_DATA_FOUND EXCEPTI
ON IN THE FOR LOOP
6 --DECLARE AND DEFINE FUNCTION TO CALC AVG GUESTS PER NIGHT BY ROOM
7 FUNCTION avg_guests( roomnum IN NUMBER) AS average NUMBER;
8 BEGIN
9 SELECT avg(adultcnt+childcnt)
10 INTO average
11 FROM ddi.ledger_view
12 WHERE roomnum = avg_guests.roomnum;
13
14 RETURN average;
15 EXCEPTION
16 WHEN OTHERS THEN
17 RETURN NULL;
18 END;
19 BEGIN
20 dbms_output.put_line ('--------------------------------------- ');
21 dbms_output.put_line (' ROOM NUMBER AVERAGE GUEST PER NIGHT BOOKED NIGH
TS ');
22 dbms_output.put_line ('---------------------------------------' );
23 --LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM/DISPLAYS ROO
M NUMBER, AVERAGE GUEST PER NIGHT AND BOOKED NIGHTS
24 FOR room_rec IN
25 (
26 SELECT *
27 FROM ddi.ledger_view
28 ORDER BY roomnum)
29 LOOP
30 found_rows := TRUE; --IF DATA EXITS, SET VARIABLE TO TRUE, SO IF STAT
EMENT DOOESN'T RUN
31 IF follow_rows != room_rec.roomnum THEN
32 follow_rows := room_rec.roomnum;
33 SELECT count(*)
34 INTO nights_book
35 FROM ddi.ledger_view
36 WHERE roomnum = follow_rows;
37
38 dbms_output.put_line ('DDI.LEDGER_VIEW'
39 || follow_rows
40 || ': '
41 ||to_char(avg_guests(follow_rows), '$99999.99'));
42 END IF;
43 END LOOP;
44 /* used TO throw
45 EXCEPTION
46 FOR FOR
47 LOOP
48 WHEN no ROWS are returned FROM
49 SELECT query */ IF NOT found_rows THEN RAISE no_data_found;
50
51 END IF;
52 EXCEPTION
53 WHEN no_data_found THEN
54 dbms_output.put_line('NO DATA FOUND.');
55 END;
56 /

Re: Written Assignment (+ example code) [message #672621 is a reply to message #672620] Fri, 19 October 2018 15:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
We have explained the reasons for many of your errors and told you how to fix them and yet you keep just posting the original question, disregarding all of the responses, without correcting any of the errors. Why? Are you just being lazy and expecting somebody to post the complete corrected code and do your homework for you or are you unable to read and comprehend the answers or what? If you cannot grasp these simple things, then you are not well-suited for programming and should quit and try something simpler.


Re: Written Assignment (+ example code) [message #672622 is a reply to message #672620] Fri, 19 October 2018 15:31 Go to previous messageGo to next message
BlackSwan
Messages: 26567
Registered: January 2009
Location: SoCal
Senior Member
First you need to eliminate the FUNCTION code from inside the anonymous PL/SQL block & compile the function separately.

PLEASE learn how to Read The Fine Manuals yourself

https://docs.oracle.com/database/121/SQLRF/statements_5012.htm#SQLRF01208

https://stackoverflow.com/questions/9708656/how-can-i-create-a-sql-function-with-a-parameter

https://docs.oracle.com/database/121/nav/portal_booklist.htm

https://docs.oracle.com/database/121/SQLRF/toc.htm



Re: Written Assignment (+ example code) [message #672624 is a reply to message #672621] Fri, 19 October 2018 16:09 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
I am not lazy all I am 62 year old women trying to understand these code and it is taking me sometime but I am learning them okay. I fix the other problem I had because I forgot the return number part and once I did that all the other error left except this one ERROR:ORA-01756: quoted string not properly terminated. I look over every string and still keep coming up with this ERROR:
ORA-01756: quoted string not properly terminated

SQL> SET SERVEROUT ON
SQL> DECLARE
2 ROOMNUM_REC DDI.LEDGER_VIEW%ROWTYPE;
3 FOLLOW_ROWS DDI.LEDGER_VIEW.ROOMNUM%TYPE :=0;
4 NIGHT_BOOK NUMBER;
5 FOUND_ROWS BOOLEAN := FALSE; --VARIABLE TO TEST FOR NO_DATA_FOUND EXCEPTION
IN THE FOR LOOP
6
7 --DECLARE AND DEFINE FUNCTION TO CALC AVG GUESTS PER NIGHT BY ROOMNUM
8 FUNCTION AVG_GUESTS(
9 ROOMNUM IN NUMBER)
10 RETURN NUMBER
11
12 AS
13 AVERAGE NUMBER;
14 BEGIN
15 SELECT AVG(ADULTCNT+CHILDCNT)
16 INTO AVERAGE
17 FROM DDI.LEDGER_VIEW
18 WHERE ROOMNUM = AVG_GUESTS.ROOMNUM;
19 RETURN AVERAGE;
20 EXCEPTION
21 WHEN OTHERS THEN
22 RETURN NULL;
23 END;
24 BEGIN
25 DBMS_OUTPUT.PUT_LINE('---------------------------------------');
26 DBMS_OUTPUT.PUT_LINE (' ROOM NUMBER AVERAGE GUEST PER NIGHT BOOKED NIGHTS
');
27 DBMS_OUTPUT.PUT_LINE('---------------------------------------');
28
29 --LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM/DISPLAYS ROOM NU
MBER, AVERAGE GUEST PER NIGHT AND BOOKED NIGHTS
30 FOR ROOM_REC IN
31 (SELECT *
32 FROM DDI.LEDGER_VIEW
33 ORDER BY ROOMNUM)
34 LOOP
35 FOUND_ROWS := TRUE; --IF DATA EXITS, SET VARIABLE TO TRUE, SO IF STATEMENT
DOOESN'T RUN
36 IF FOLLOW_ROWS != ROOM_REC.ROOMNUM THEN
37 FOLLOW_ROWS := ROOM_REC.ROOMNUM;
38 SELECT COUNT(*) INTO NIGHTS_BOOK FROM DDI.LEDGER_VIEW WHERE ROOMNUM = FOLLO
W_ROWS;
39 DBMS_OUTPUT.PUT_LINE (`DDI.LEDGER_VIEW' || FOLLOW_ROWS ||': '||TO_CH
AR(AVG_GUESTS(FOLLOW_ROWS ),'$99999.99'));
40
41 END IF;
42 END LOOP;
43 /* USED TO THROW EXCEPTION for FOR LOOP WHEN NO ROWS ARE RETURNED FROM SEL
ECT QUERY */
44 IF NOT FOUND_ROWS THEN RAISE NO_DATA_FOUND;
45 END IF;
46 EXCEPTION
47 WHEN NO_DATA_FOUND THEN
48 DBMS_OUTPUT.PUT_LINE('No data found.');
49 END;
50 /
ERROR:
ORA-01756: quoted string not properly terminated
Re: Written Assignment (+ example code) [message #672625 is a reply to message #672624] Fri, 19 October 2018 16:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
Quoted string not properly terminated means that you have something that starts with a quote and is missing an ending quote. In the following line:

`DDI.LEDGER_VIEW'

the starting quote looks like something other than a normal quote and it probably thinks the ending quote is the starting quote and cannot find an ending quote to match it. So, I would replace that ` with '.

Re: Written Assignment (+ example code) [message #672626 is a reply to message #672624] Fri, 19 October 2018 18:23 Go to previous messageGo to next message
BlackSwan
Messages: 26567
Registered: January 2009
Location: SoCal
Senior Member
cwilliam912 wrote on Fri, 19 October 2018 14:09

I am not lazy all I am 62 year old women trying to understand these code and it is taking me sometime but I am learning them okay.

Nobody here said anything about you being lazy.

Realize that up until this point in time you have only been struggling to eliminate the plethora of syntax errors you put into the code.

After you have code that will compile, then you be faced with run time errors plus errors in logic.
You have totally ignore other advice regarding the content of post code.
Folks here may just stop answering this thread since you ignore correct & well meaning advice & continue to report the same error.
Re: Written Assignment (+ example code) [message #672628 is a reply to message #672626] Sat, 20 October 2018 01:12 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
BlackSwan wrote on Sat, 20 October 2018 00:23

Nobody here said anything about you being lazy.
Barbara Boehmer wrote on Fri, 19 October 2018 21:27
... Why? Are you just being lazy and expecting somebody to post the complete...
Re: Written Assignment (+ example code) [message #672633 is a reply to message #672553] Sat, 20 October 2018 15:44 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
I made it this far and can't understand why I am not getting any result:

SQL> DECLARE
2 DDI_REC DDI.LEDGER_VIEW %ROWTYPE; --declaring DDI_REC
3 DDI_ROOM DDI.LEDGER_VIEW.ROOMNUM%type := 0; --declaring DDI_ROOM and assiging it to ZERO
4 FOUND_ROWS BOOLEAN := FALSE; --variable to test for no_data_found exception in the FOR LOOP
5
6 FUNCTION AVG_GUESTS(ROOM IN NUMBER)
7 RETURN NUMBER
8 AS AVERAGE NUMBER;
9
10 BEGIN
11 SELECT AVG(ADULTCNT + CHILDCNT)
12 INTO AVERAGE
13 FROM DDI.LEDGER_VIEW
14 WHERE ROOMNUM = ROOM;
15
16 RETURN AVERAGE;
17
18 EXCEPTION
19 WHEN OTHERS THEN
20 RETURN NULL;
21 END;
22
23
24 BEGIN
25 DBMS_OUTPUT.NEW_LINE;
26 DBMS_OUTPUT.PUT_LINE (' AVG ROOM RENTALS');
27 DBMS_OUTPUT.PUT_LINE (' PER DDI.LEDGER_VIEW');
28 DBMS_OUTPUT.NEW_LINE;
29 DBMS_OUTPUT.PUT_LINE (' ROOM AVG NUMBER BOOKED');
30 DBMS_OUTPUT.PUT_LINE (' NUM OF GUESTS NIGHTS');
31 DBMS_OUTPUT.PUT_LINE (' ---- ---------- ------');
32
33 -- LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM
34
35 FOR DDI_REC IN
36 (SELECT *
37 FROM DDI.LEDGER_VIEW
38 ORDER BY ROOMNUM)
39 LOOP
40 FOUND_ROWS := TRUE; --if data exist, sets variable to TRUE, so IF state
ment doesn't run
41 IF DDI_ROOM != DDI_REC.ROOMNUM THEN
42 DDI_ROOM := DDI_REC.ROOMNUM;
43 DBMS_OUTPUT.PUT_LINE(' ' ||DDI_ROOM||' '||TO_CHAR(AVG_GUESTS(DDI_ROOM)
, '9.99'));
44 END IF;
45 END LOOP; -- End of loop
46
47
48 IF NOT FOUND_ROWS THEN -- trigger the exception below when no results
are returned
49 RAISE NO_DATA_FOUND;
50 END IF;
51
52 EXCEPTION
53 WHEN NO_DATA_FOUND THEN
54 DBMS_OUTPUT.PUT_LINE('No data found.');
55 END;
56 /

PL/SQL procedure successfully completed.
Re: Written Assignment (+ example code) [message #672635 is a reply to message #672633] Sat, 20 October 2018 17:14 Go to previous messageGo to next message
BlackSwan
Messages: 26567
Registered: January 2009
Location: SoCal
Senior Member
Realize that your latest post is the equivalent to posting a picture of your car.
Proclaiming that the car does not go.
and asking "What is wrong with my car?"

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read


You need to provide us a complete TEST CASE as detailed in Posting Guidelines above.

When will you start providing us formatted code as explained in Posting Guidelines?

Since we don't have your tables or data, we can not run, debug, test, or improve posted code.
You have a mystery & give us no clues.

did you
SET SERVEROUTPUT ON
prior to the test run?
Re: Written Assignment (+ example code) [message #672636 is a reply to message #672633] Sat, 20 October 2018 19:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
Did you SET SERVEROUTPUT ON?

Is there any data in your tables?

What is the result of "select count(*) from ddi.ledger_view;"?

What is meant by "not getting any result"?

Does it show an error message?

Does it show that your procedure completed?

Does it show "no data found"?





[Updated on: Sat, 20 October 2018 19:35]

Report message to a moderator

Re: Written Assignment (+ example code) [message #672637 is a reply to message #672633] Sat, 20 October 2018 21:01 Go to previous messageGo to next message
BlackSwan
Messages: 26567
Registered: January 2009
Location: SoCal
Senior Member
cwilliam912 wrote on Sat, 20 October 2018 13:44

33 -- LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM
34
35 FOR DDI_REC IN
36 (SELECT *
37 FROM DDI.LEDGER_VIEW
38 ORDER BY ROOMNUM)
39 LOOP
40 FOUND_ROWS := TRUE; --if data exist, sets variable to TRUE, so IF state
ment doesn't run
41 IF DDI_ROOM != DDI_REC.ROOMNUM THEN
42 DDI_ROOM := DDI_REC.ROOMNUM;
43 DBMS_OUTPUT.PUT_LINE(' ' ||DDI_ROOM||' '||TO_CHAR(AVG_GUESTS(DDI_ROOM)
, '9.99'));
44 END IF;
45 END LOOP; -- End of loop
46
47
48 IF NOT FOUND_ROWS THEN -- trigger the exception below when no results are returned
Between line #39 & line #45 a LOOP exists.
How & when does code "exit" out of this LOOP?

How & when does line #48 ever get executed?
Re: Written Assignment (+ example code) [message #672639 is a reply to message #672637] Sat, 20 October 2018 22:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
BlackSwan wrote on Sat, 20 October 2018 19:01
cwilliam912 wrote on Sat, 20 October 2018 13:44

33 -- LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM
34
35 FOR DDI_REC IN
36 (SELECT *
37 FROM DDI.LEDGER_VIEW
38 ORDER BY ROOMNUM)
39 LOOP
40 FOUND_ROWS := TRUE; --if data exist, sets variable to TRUE, so IF state
ment doesn't run
41 IF DDI_ROOM != DDI_REC.ROOMNUM THEN
42 DDI_ROOM := DDI_REC.ROOMNUM;
43 DBMS_OUTPUT.PUT_LINE(' ' ||DDI_ROOM||' '||TO_CHAR(AVG_GUESTS(DDI_ROOM)
, '9.99'));
44 END IF;
45 END LOOP; -- End of loop
46
47
48 IF NOT FOUND_ROWS THEN -- trigger the exception below when no results are returned
Between line #39 & line #45 a LOOP exists.
How & when does code "exit" out of this LOOP?

How & when does line #48 ever get executed?

It is hard to follow the code without indentation, but it is a cursor for loop, so it exits when it runs out of data. Line 48 gets executed if there is no data in the loop, so nothing within the loop gets executed, and found_rows does not get set to true.

I have been able to run the code, with and without data, and get results, so I don't know what the o.p. is doing differently. I suspect that it is something simple like not setting serveroutput on and not having any committed data. This does produce results, but not necessarily the desired results or desired format. Please see the demonstration below.

-- empty tables and view provided by o.p. with not nulls removed for easier testing:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE ROOMS (
  2  RoomNum NUMBER ,
  3  RoomSize VARCHAR2(6) ,
  4  RoomBedCnt NUMBER ,
  5  RoomRate NUMBER(18,2) ,
  6  PRIMARY KEY (RoomNum))
  7  /

Table created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE PATRONS (
  2  PatronID NUMBER ,
  3  FirstName VARCHAR2(16) ,
  4  LastName VARCHAR2(16) ,
  5  PhoneNum VARCHAR2(12) ,
  6  eMail VARCHAR2(100) ,
  7  PRIMARY KEY (PatronID))
  8  TABLESPACE USERS
  9  /

Table created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE REGISTRATIONS (
  2  RegID NUMBER ,
  3  RegDate DATE ,
  4  PatronID NUMBER ,
  5  AdultCnt NUMBER ,
  6  ChildCnt NUMBER ,
  7  RoomNum NUMBER ,
  8  RegNote VARCHAR2(100) ,
  9  PRIMARY KEY (RegID),
 10  FOREIGN KEY (PatronID)
 11  REFERENCES PATRONS,
 12  FOREIGN KEY (RoomNum)
 13  REFERENCES ROOMS)
 14  TABLESPACE USERS
 15  /

Table created.

SCOTT@orcl_12.1.0.2.0> CREATE VIEW LEDGER_VIEW
  2  AS
  3  SELECT REG.RegID, REG.RegDate, P.LastName, P.FirstName,
  4  REG.AdultCnt, REG.ChildCnt, REG.RoomNum,
  5  RM.RoomSize, RM.RoomBedCnt, RM.RoomRate
  6  FROM ROOMS RM, PATRONS P, REGISTRATIONS REG
  7  WHERE REG.PatronID = P.PatronID
  8  AND REG.RoomNum = RM.RoomNum
  9  ORDER BY REG.RegDate, REG.RoomNum
 10  /

View created.

-- run of code on empty tables:
SCOTT@orcl_12.1.0.2.0> SET SERVEROUT ON
SCOTT@orcl_12.1.0.2.0> DECLARE
  2    DDI_REC LEDGER_VIEW %ROWTYPE; --declaring DDI_REC
  3    DDI_ROOM LEDGER_VIEW.ROOMNUM%type := 0; --declaring DDI_ROOM and assiging it to ZERO
  4    FOUND_ROWS BOOLEAN := FALSE; --variable to test for no_data_found exception in the FOR LOOP
  5    FUNCTION AVG_GUESTS(ROOM IN NUMBER)
  6  	 RETURN NUMBER
  7    AS
  8  	 AVERAGE NUMBER;
  9    BEGIN
 10  	 SELECT AVG(ADULTCNT + CHILDCNT)
 11  	 INTO AVERAGE
 12  	 FROM LEDGER_VIEW
 13  	 WHERE ROOMNUM = ROOM;
 14  	 RETURN AVERAGE;
 15    EXCEPTION
 16  	 WHEN OTHERS THEN
 17  	   RETURN NULL;
 18    END;
 19  BEGIN
 20    DBMS_OUTPUT.NEW_LINE;
 21    DBMS_OUTPUT.PUT_LINE (' AVG ROOM RENTALS');
 22    DBMS_OUTPUT.PUT_LINE (' PER LEDGER_VIEW');
 23    DBMS_OUTPUT.NEW_LINE;
 24    DBMS_OUTPUT.PUT_LINE (' ROOM AVG NUMBER BOOKED');
 25    DBMS_OUTPUT.PUT_LINE (' NUM OF GUESTS NIGHTS');
 26    DBMS_OUTPUT.PUT_LINE (' ---- ---------- ------');
 27    -- LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM
 28    FOR DDI_REC IN
 29  	 (SELECT *
 30  	  FROM LEDGER_VIEW
 31  	  ORDER BY ROOMNUM)
 32    LOOP
 33  	 FOUND_ROWS := TRUE; --if data exist, sets variable to TRUE, so IF statement doesn't run
 34  	 IF DDI_ROOM != DDI_REC.ROOMNUM THEN
 35  	   DDI_ROOM := DDI_REC.ROOMNUM;
 36  	   DBMS_OUTPUT.PUT_LINE(' ' ||DDI_ROOM||' '||TO_CHAR(AVG_GUESTS(DDI_ROOM), '9.99'));
 37  	 END IF;
 38    END LOOP; -- End of loop
 39    IF NOT FOUND_ROWS THEN -- trigger the exception below when no resultsare returned
 40  	 RAISE NO_DATA_FOUND;
 41    END IF;
 42  EXCEPTION
 43    WHEN NO_DATA_FOUND THEN
 44  	 DBMS_OUTPUT.PUT_LINE('No data found.');
 45  END;
 46  /
AVG ROOM RENTALS
PER LEDGER_VIEW
ROOM AVG NUMBER BOOKED
NUM OF GUESTS NIGHTS
---- ---------- ------
No data found.

PL/SQL procedure successfully completed.

-- insert some test data:
SCOTT@orcl_12.1.0.2.0> insert into rooms (roomnum) values (1)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> insert into rooms (roomnum) values (2)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> insert into patrons (patronid) values (10)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> insert into patrons (patronid) values (11)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> insert into patrons (patronid) values (20)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> insert into patrons (patronid) values (21)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO registrations (roomnum, adultcnt, childcnt, patronid, regid) values (1, 2, 3, 10, 10)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO registrations (roomnum, adultcnt, childcnt, patronid, regid) values (1, 2, 3, 11, 11)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO registrations (roomnum, adultcnt, childcnt, patronid, regid) values (2, 3, 4, 20, 20)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO registrations (roomnum, adultcnt, childcnt, patronid, regid) values (2, 3, 4, 21, 21)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> COMMIT
  2  /

Commit complete.

-- second run with data:
SCOTT@orcl_12.1.0.2.0> SET SERVEROUT ON
SCOTT@orcl_12.1.0.2.0> DECLARE
  2    DDI_REC LEDGER_VIEW %ROWTYPE; --declaring DDI_REC
  3    DDI_ROOM LEDGER_VIEW.ROOMNUM%type := 0; --declaring DDI_ROOM and assiging it to ZERO
  4    FOUND_ROWS BOOLEAN := FALSE; --variable to test for no_data_found exception in the FOR LOOP
  5    FUNCTION AVG_GUESTS(ROOM IN NUMBER)
  6  	 RETURN NUMBER
  7    AS
  8  	 AVERAGE NUMBER;
  9    BEGIN
 10  	 SELECT AVG(ADULTCNT + CHILDCNT)
 11  	 INTO AVERAGE
 12  	 FROM LEDGER_VIEW
 13  	 WHERE ROOMNUM = ROOM;
 14  	 RETURN AVERAGE;
 15    EXCEPTION
 16  	 WHEN OTHERS THEN
 17  	   RETURN NULL;
 18    END;
 19  BEGIN
 20    DBMS_OUTPUT.NEW_LINE;
 21    DBMS_OUTPUT.PUT_LINE (' AVG ROOM RENTALS');
 22    DBMS_OUTPUT.PUT_LINE (' PER LEDGER_VIEW');
 23    DBMS_OUTPUT.NEW_LINE;
 24    DBMS_OUTPUT.PUT_LINE (' ROOM AVG NUMBER BOOKED');
 25    DBMS_OUTPUT.PUT_LINE (' NUM OF GUESTS NIGHTS');
 26    DBMS_OUTPUT.PUT_LINE (' ---- ---------- ------');
 27    -- LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM
 28    FOR DDI_REC IN
 29  	 (SELECT *
 30  	  FROM LEDGER_VIEW
 31  	  ORDER BY ROOMNUM)
 32    LOOP
 33  	 FOUND_ROWS := TRUE; --if data exist, sets variable to TRUE, so IF statement doesn't run
 34  	 IF DDI_ROOM != DDI_REC.ROOMNUM THEN
 35  	   DDI_ROOM := DDI_REC.ROOMNUM;
 36  	   DBMS_OUTPUT.PUT_LINE(' ' ||DDI_ROOM||' '||TO_CHAR(AVG_GUESTS(DDI_ROOM), '9.99'));
 37  	 END IF;
 38    END LOOP; -- End of loop
 39    IF NOT FOUND_ROWS THEN -- trigger the exception below when no resultsare returned
 40  	 RAISE NO_DATA_FOUND;
 41    END IF;
 42  EXCEPTION
 43    WHEN NO_DATA_FOUND THEN
 44  	 DBMS_OUTPUT.PUT_LINE('No data found.');
 45  END;
 46  /
AVG ROOM RENTALS
PER LEDGER_VIEW
ROOM AVG NUMBER BOOKED
NUM OF GUESTS NIGHTS
---- ---------- ------
1  5.00
2  7.00

PL/SQL procedure successfully completed.







Re: Written Assignment (+ example code) [message #672640 is a reply to message #672639] Sat, 20 October 2018 23:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
Here is the full script that I ran to get the results in the previous post, in case anyone else wants to experiment with it. I forgot to mention that I also removed the schema reference for easier testing.

-- empty tables and view provided by o.p. with not nulls removed for easier testing:
CREATE TABLE ROOMS (
RoomNum NUMBER ,
RoomSize VARCHAR2(6) ,
RoomBedCnt NUMBER ,
RoomRate NUMBER(18,2) ,
PRIMARY KEY (RoomNum))
/
CREATE TABLE PATRONS (
PatronID NUMBER ,
FirstName VARCHAR2(16) ,
LastName VARCHAR2(16) ,
PhoneNum VARCHAR2(12) ,
eMail VARCHAR2(100) ,
PRIMARY KEY (PatronID))
TABLESPACE USERS
/
CREATE TABLE REGISTRATIONS (
RegID NUMBER ,
RegDate DATE ,
PatronID NUMBER ,
AdultCnt NUMBER ,
ChildCnt NUMBER ,
RoomNum NUMBER ,
RegNote VARCHAR2(100) ,
PRIMARY KEY (RegID),
FOREIGN KEY (PatronID)
REFERENCES PATRONS,
FOREIGN KEY (RoomNum)
REFERENCES ROOMS)
TABLESPACE USERS
/
CREATE VIEW LEDGER_VIEW
AS
SELECT REG.RegID, REG.RegDate, P.LastName, P.FirstName,
REG.AdultCnt, REG.ChildCnt, REG.RoomNum,
RM.RoomSize, RM.RoomBedCnt, RM.RoomRate
FROM ROOMS RM, PATRONS P, REGISTRATIONS REG
WHERE REG.PatronID = P.PatronID
AND REG.RoomNum = RM.RoomNum
ORDER BY REG.RegDate, REG.RoomNum 
/
-- run of code on empty tables:
SET SERVEROUT ON
DECLARE
  DDI_REC LEDGER_VIEW %ROWTYPE; --declaring DDI_REC
  DDI_ROOM 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
  FUNCTION AVG_GUESTS(ROOM IN NUMBER)
    RETURN NUMBER
  AS 
    AVERAGE NUMBER;
  BEGIN
    SELECT AVG(ADULTCNT + CHILDCNT)
    INTO AVERAGE
    FROM LEDGER_VIEW
    WHERE ROOMNUM = ROOM;
    RETURN AVERAGE;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END;
BEGIN
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT_LINE (' AVG ROOM RENTALS');
  DBMS_OUTPUT.PUT_LINE (' PER 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
    (SELECT *
     FROM LEDGER_VIEW
     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'));
    END IF;
  END LOOP; -- End of loop
  IF NOT FOUND_ROWS THEN -- trigger the exception below when no resultsare returned
    RAISE NO_DATA_FOUND;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found.');
END;
/
-- insert some test data:
insert into rooms (roomnum) values (1)
/
insert into rooms (roomnum) values (2)
/
insert into patrons (patronid) values (10)
/
insert into patrons (patronid) values (11)
/
insert into patrons (patronid) values (20)
/
insert into patrons (patronid) values (21)
/
INSERT INTO registrations (roomnum, adultcnt, childcnt, patronid, regid) values (1, 2, 3, 10, 10)
/
INSERT INTO registrations (roomnum, adultcnt, childcnt, patronid, regid) values (1, 2, 3, 11, 11)
/
INSERT INTO registrations (roomnum, adultcnt, childcnt, patronid, regid) values (2, 3, 4, 20, 20)
/
INSERT INTO registrations (roomnum, adultcnt, childcnt, patronid, regid) values (2, 3, 4, 21, 21)
/
COMMIT
/
-- second run with data:
SET SERVEROUT ON
DECLARE
  DDI_REC LEDGER_VIEW %ROWTYPE; --declaring DDI_REC
  DDI_ROOM 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
  FUNCTION AVG_GUESTS(ROOM IN NUMBER)
    RETURN NUMBER
  AS 
    AVERAGE NUMBER;
  BEGIN
    SELECT AVG(ADULTCNT + CHILDCNT)
    INTO AVERAGE
    FROM LEDGER_VIEW
    WHERE ROOMNUM = ROOM;
    RETURN AVERAGE;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END;
BEGIN
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT_LINE (' AVG ROOM RENTALS');
  DBMS_OUTPUT.PUT_LINE (' PER 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
    (SELECT *
     FROM LEDGER_VIEW
     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'));
    END IF;
  END LOOP; -- End of loop
  IF NOT FOUND_ROWS THEN -- trigger the exception below when no resultsare 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 #672641 is a reply to message #672640] Sun, 21 October 2018 00:34 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
Why am I not getting my booked per night
SQL> SET SERVEROUT ON
SQL> DECLARE
2 DDI_REC DDI.LEDGER_VIEW %ROWTYPE; --declaring DDI_REC
3 DDI_ROOM DDI.LEDGER_VIEW.ROOMNUM%type := 0; --declaring DDI_ROOM and assig
ning it to ZERO
4 FOUND_ROWS BOOLEAN := FALSE; --variable to test for no_data_f
ound exception in the FOR LOOP
5
6 FUNCTION AVG_GUESTS(ROOM IN NUMBER)
7 RETURN NUMBER
8 AS AVERAGE NUMBER;
9
10 BEGIN
11 SELECT AVG(ADULTCNT + CHILDCNT)
12 INTO AVERAGE
13 FROM DDI.LEDGER_VIEW
14 WHERE ROOMNUM = ROOM;
15
16 RETURN AVERAGE;
17
18 EXCEPTION
19 WHEN OTHERS THEN
20 RETURN NULL;
21 END;
22
23
24 BEGIN
25 DBMS_OUTPUT.NEW_LINE;
26 DBMS_OUTPUT.PUT_LINE ('AVG ROOM RENTALS');
27 DBMS_OUTPUT.PUT_LINE ('PER DDI.LEDGER_VIEW');
28 DBMS_OUTPUT.NEW_LINE;
29 DBMS_OUTPUT.PUT_LINE (' ROOM AVG NUMBER BOOKED');
30 DBMS_OUTPUT.PUT_LINE (' NUMBER OF GUESTS NIGHTS');
31 DBMS_OUTPUT.PUT_LINE (' ---- ---------- ------');
32
33 -- LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM
34
35 FOR DDI_REC IN
36 (SELECT *
37 FROM DDI.LEDGER_VIEW
38 ORDER BY ROOMNUM)
39 LOOP
40 FOUND_ROWS := TRUE; --if data exist, sets variable to TRUE, so IF state
ment doesn't run
41 IF DDI_ROOM != DDI_REC.ROOMNUM THEN
42 DDI_ROOM := DDI_REC.ROOMNUM;
43 DBMS_OUTPUT.PUT_LINE(' ' ||DDI_ROOM||' '||TO_CHAR(AVG_GUESTS(DDI_ROOM)
, '9.99'));
44 END IF;
45 END LOOP; -- End of loop
46
47
48 IF NOT FOUND_ROWS THEN -- trigger the exception below when no results
are returned
49 RAISE NO_DATA_FOUND;
50 END IF;
51
52 EXCEPTION
53 WHEN NO_DATA_FOUND THEN
54 DBMS_OUTPUT.PUT_LINE('No data found.');
55 END;
56 /
AVG ROOM RENTALS
PER DDI.LEDGER_VIEW
ROOM AVG NUMBER BOOKED
NUMBER 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
Re: Written Assignment (+ example code) [message #672643 is a reply to message #672641] Sun, 21 October 2018 11:18 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
Why do you still have
18 EXCEPTION
19 WHEN OTHERS THEN
20 RETURN NULL;
21 END;
in your code? Why ignore the advice given?
Why does your code still have this section:
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;
Why ignore the advice given?

Why have you not provided sample data (insert scripts) for your tables so that people can replicate your environment (as has been previously asked)?
Why do you still refuse to format your code and post it in [code][/code] tags?

From what I can see, you're not getting 'booked per night' because you haven't asked for it (I could be wrong, your code isn't formatted and in tags, so it's a little difficult to see)

Re: Written Assignment (+ example code) [message #672644 is a reply to message #672643] Sun, 21 October 2018 13:15 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
I am not ignore you advice I just post that when I got ready to go to work so I didn't have time to read the post sorry.
Re: Written Assignment (+ example code) [message #672645 is a reply to message #672643] Sun, 21 October 2018 13:19 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
Here is the sample data:

REM The next command will fail on the first run
DROP USER DDI CASCADE;
CREATE USER DDI PROFILE DEFAULT
IDENTIFIED BY oracle DEFAULT TABLESPACE USERS
ACCOUNT UNLOCK;
GRANT CONNECT TO DDI;
GRANT RESOURCE TO DDI;

CREATE TABLE DDI.ROOMS (
RoomNum NUMBER NOT NULL,
RoomSize VARCHAR2(6) NOT NULL,
RoomBedCnt NUMBER NOT NULL,
RoomRate NUMBER(18,2) NOT NULL,
PRIMARY KEY (RoomNum))
TABLESPACE USERS;
REM INSERTING into DDI.ROOMS
Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (101,'Double',1,44);
Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (102,'Double',2,49);
Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (103,'Queen ',2,61);
Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (104,'Queen ',2,61);
Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (105,'Queen ',2,61);
Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (106,'King ',1,61);
Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (107,'King ',1,61);
Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (108,'King ',1,61);

CREATE TABLE DDI.PATRONS (
PatronID NUMBER NOT NULL,
FirstName VARCHAR2(16) NOT NULL,
LastName VARCHAR2(16) NOT NULL,
PhoneNum VARCHAR2(12) Null,
eMail VARCHAR2(100) Null,
PRIMARY KEY (PatronID))
TABLESPACE USERS;
REM INSERTING into DDI.PATRONS
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (1,'Bill','Tulsa','555-485-8356','Bill.Tulsa@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (2,'Chet','Travis','555-972-3076','Chet.Travis@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (3,'Chip','Marino','555-933-2815','Chip.Marino@ourcampus.edu');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (4,'Dag','Renborn','555-595-6240','Dag.Renborn@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (5,'Darryl','Princeton','555-150-3607','Darryl.Princeton@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (6,'Donna','Smith','555-647-4949','Donna.Smith@ourcampus.edu');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (7,'Donna','Smith','555-708-3498','Donna.Smith@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (8,'Douglas','Fischer','555-664-2731','Douglas.Fischer@ourcampus.edu');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (9,'Elizabeth','Stanley','555-187-9958','Elizabeth.Stanley@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (10,'Eric','Jones','555-780-1222','Eric.Jones@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (11,'Eric','Parks','555-752-6266','Eric.Parks@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (12,'Fred','Price','555-176-4902','Fred.Price@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (13,'George','Berkely','555-662-3565','George.Berkely@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (14,'Hilary','Evans','555-860-5908','Hilary.Evans@somewhere.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (15,'James','Thompson','555-243-8344','James.Thompson@ourcampus.edu');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (16,'Jason','Tully','555-172-8639','Jason.Tully@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (17,'John','Watson','555-931-6794','John.Watson@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (18,'Kelly','Oxford','555-208-5106','Kelly.Oxford@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (19,'Linda','Becky','555-761-8562','Linda.Becky@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (20,'Lisa','Brisbon','555-115-5534','Lisa.Brisbon@ourcampus.edu');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (21,'Liz','Frier','555-141-4440','Liz.Frier@somewhere.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (22,'Marsha','Downs','555-248-7905','Marsha.Downs@somewhere.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (23,'Marsha','Downs','555-346-5630','Marsha.Downs@somewhere.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (24,'Mary','Harvard','555-558-2864','Mary.Harvard@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (25,'Matthew','Andrews','555-957-378','Matthew.Andrews@ourcampus.edu');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (26,'Matthew','Andrews','555-957-3780','Matthew.Andrews@ourcampus.edu');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (27,'Merle','Atkins','555-666-1794','Merle.Atkins@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (28,'Mike','Sailors','555-434-2463','Mike.Sailors@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (29,'Miles','Trent','555-971-2236','Miles.Trent@somewhere.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (30,'Pamela','Birch','555-139-1378','Pamela.Birch@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (31,'Richard','James','555-455-5163','Richard.James@somewhere.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (32,'Richard','James','555-815-4831','Richard.James@somewhere.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (33,'Richard','James','555-906-6780','Richard.James@somewhere.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (34,'Richardo','Romez','555-875-7101','Richardo.Romez@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (35,'Sally','Smith','555-329-4189','Sally.Smith@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (36,'Samantha','Jackson','555-632-7417','Samantha.Jackson@mymail.com');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (37,'Terry','Hwang','555-119-9217','Terry.Hwang@ourcampus.edu');
Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (38,'Tzu','Lai','555-986-2353','Tzu.Lai@ourcampus.edu');

CREATE TABLE DDI.REGISTRATIONS (
RegID NUMBER NOT NULL,
RegDate DATE NOT NULL,
PatronID NUMBER NOT NULL,
AdultCnt NUMBER Null,
ChildCnt NUMBER Null,
RoomNum NUMBER NOT NULL,
RegNote VARCHAR2(100) Null,
PRIMARY KEY (RegID),
FOREIGN KEY (PatronID)
REFERENCES DDI.PATRONS,
FOREIGN KEY (RoomNum)
REFERENCES DDI.ROOMS)
TABLESPACE USERS;
REM INSERTING into DDI.REGISTRATIONS
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (1,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,2,0,104,'On vacation');
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (2,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,0,2,103,'On vacation');
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (3,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),2,2,0,107,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (4,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),3,2,0,108,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (5,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),4,1,0,101,'Regular guest');
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (6,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),5,1,0,106,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (7,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),6,2,1,105,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (8,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),7,2,0,102,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (9,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,2,0,104,'On vacation');
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (10,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,0,2,103,'On vacation');
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (11,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),8,1,1,105,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (12,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),9,2,0,106,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (13,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),10,2,0,107,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (14,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),11,2,1,108,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (15,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,2,0,104,'On vacation');
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (16,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,0,2,103,'On vacation');
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (17,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),12,2,0,102,'Early arrival');
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (18,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),13,2,0,105,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (19,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),14,1,0,106,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (20,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),4,1,0,101,'Regular guest');
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (21,to_timestamp('04-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),15,2,1,103,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (22,to_timestamp('04-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),16,2,0,104,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (23,to_timestamp('04-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),17,2,1,106,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (24,to_timestamp('04-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),18,1,0,107,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (25,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),4,1,0,101,'Regular guest');
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (26,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),19,2,0,106,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (27,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),20,1,0,107,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (28,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),21,2,0,103,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (29,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),22,2,1,104,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (30,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),23,2,0,108,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (31,to_timestamp('06-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),24,2,0,105,'Early arrival');
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (32,to_timestamp('06-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),25,2,0,106,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (33,to_timestamp('06-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),26,1,0,101,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (34,to_timestamp('06-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),27,2,0,107,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (35,to_timestamp('06-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),28,1,0,108,'Different Donna Smith');
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (36,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),4,1,0,101,'Regular guest');
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (37,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),29,2,0,103,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (38,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),30,2,0,104,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (39,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),31,1,0,106,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (40,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),32,2,1,105,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (41,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),33,2,0,107,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (42,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),34,1,0,108,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (43,to_timestamp('08-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),32,2,0,102,'Early arrival');
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (44,to_timestamp('08-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),30,2,0,103,null);
Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (45,to_timestamp('08-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),23,2,0,106,null);

CREATE VIEW DDI.LEDGER_VIEW
AS
SELECT REG.RegID, REG.RegDate, P.LastName, P.FirstName,
REG.AdultCnt, REG.ChildCnt, REG.RoomNum,
RM.RoomSize, RM.RoomBedCnt, RM.RoomRate
FROM DDI.ROOMS RM, DDI.PATRONS P, DDI.REGISTRATIONS REG
WHERE REG.PatronID = P.PatronID
AND REG.RoomNum = RM.RoomNum
ORDER BY REG.RegDate, REG.RoomNum;
Re: Written Assignment (+ example code) [message #672646 is a reply to message #672645] Sun, 21 October 2018 13:35 Go to previous messageGo to next message
BlackSwan
Messages: 26567
Registered: January 2009
Location: SoCal
Senior Member
>Why am I not getting my booked per night
What is expected & desired results for booked per night based upon posted sample data?
How (& where) is booked per night calculated?
Re: Written Assignment (+ example code) [message #672647 is a reply to message #672645] Sun, 21 October 2018 13:43 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
I don't understand what you are trying to say. I am not being funny at all
Re: Written Assignment (+ example code) [message #672648 is a reply to message #672647] Sun, 21 October 2018 14:02 Go to previous messageGo to previous message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
cwilliam912 wrote on Sun, 21 October 2018 11:43
I don't understand what you are trying to say. I am not being funny at all
What part do you not understand?

What is expected & desired results for booked per night based upon posted sample data?

The following is what you are getting. What do you expect and desire to see instead of this, when you run your code with the data you provided?
AVG ROOM RENTALS
PER DDI.LEDGER_VIEW
ROOM AVG NUMBER BOOKED
NUMBER 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

How (& where) is booked per night calculated?

Are you saying that you are not getting any values at all for "booked per night" or the values are not what you expect?
There is nothing in your code called "booked for night". So, what are you calling it within your code?
We cannot tell what you want or what you are trying to do. Also, not trying to be funny, just trying to clarify things.
Previous Topic: STORE SQL PROCEDURES
Next Topic: a procedure with dinamic sql
Goto Forum:
  


Current Time: Tue Jul 23 12:54:09 CDT 2019