Home » SQL & PL/SQL » SQL & PL/SQL » Written Assignment-Printing a Record Using an If Statement
Written Assignment-Printing a Record Using an If Statement [message #672404] Sun, 14 October 2018 04:32 Go to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
My assignment is to do but he want use to the if statement to do You can use it to determine the Department they work for, like we did in the past with a CASE. You can use it to print a message if they make over a certain amount of money.
Maybe they are due a raise if they were hired before a certain date.
I am having problem with my coding because I am getting all these error please help


1. Set echo on
2. SET SERVEROUT ON
3. Set up a spool file to receive your output for submission. I would suggest c:\CS4210\wa5spool.txt .
4. DECLARE a record variable (Emp_rec) using %ROWTYPE
5. In the BEGIN block add a select statement to read a record into the declared variable from HR.EMPLOYEES
6. Add If Statement to print record
7. Add DBMS_OUTPUT lines to print EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and SALARY for the selected record
8. Use TO_CHAR to format the salary as $999,999
9. Add a EXCEPTION block to report when no data is found
10. Compile and run the procedure.
11. Close the spool file

(SELECT *
*
ERROR at line 7:
ORA-06550: line 7, column 1:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
in
The symbol "in" was substituted for "(" to continue.
ORA-06550: line 12, column 1:
PLS-00103: Encountered the symbol "IF" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current exists max min prior sql
ORA-06550: line 18, column 5:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:
if

SQL> SET SERVEROUT ON FORMAT TRUNCATED
SQL> DECLARE
2
3 EMP_REC HR.EMPLOYEES%ROWTYPE;
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE ('---------------------------------------');
6 FOR EMP_REC
7 (SELECT *
8 FROM HR.EMPLOYEES
9 WHERE EMPLOYEE_ID = 5)
10 LOOP
11 IF
12 IF (EMP_REC.EMPLOYEE_ID = 5) THEN
13 DBMS_OUTPUT.PUT_LINE ('EMPLOYEE_ID: ' || EMP_REC.EMPLOYEE_ID) ;
14 DBMS_OUTPUT.PUT_LINE ('FIRST_NAME: ' || EMP_REC.FIRST_NAME) ;
15 DBMS_OUTPUT.PUT_LINE ('LAST_NAME: ' || EMP_REC.LAST_NAME) ;
16 DBMS_OUTPUT.PUT_LINE ('SALARY: ' || TO_CHAR (EMP_REC.SALARY, '$999,999'));
17 DBMS_OUTPUT.PUT_LINE ('---------------------------------------');
18 END LOOP;
19 END IF;
20 EXCEPTION
21 WHEN NO_DATA_FOUND THEN
22 DBMS_OUTPUT.PUT_LINE ('NO DATA FOUND.');
23
24 END;
25 /

Re: Written Assignment-Printing a Record Using an If Statement [message #672405 is a reply to message #672404] Sun, 14 October 2018 04:47 Go to previous messageGo to next message
pablolee
Messages: 2881
Registered: May 2007
Location: Scotland
Senior Member
Have a look at the syntax for your cursor for loop. Here
Re: Written Assignment-Printing a Record Using an If Statement [message #672406 is a reply to message #672405] Sun, 14 October 2018 05:01 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
Yes, I look that the syntax for the cursor loop but I got to add the if statement into it too and get errors
Re: Written Assignment-Printing a Record Using an If Statement [message #672407 is a reply to message #672404] Sun, 14 October 2018 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Use SQL*Plus and copy and paste your session, the WHOLE session.

Re: Written Assignment-Printing a Record Using an If Statement [message #672409 is a reply to message #672406] Sun, 14 October 2018 06:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2987
Registered: January 2010
Location: Connecticut, USA
Senior Member
cwilliam912 wrote on Sun, 14 October 2018 06:01
Yes, I look that the syntax for the cursor loop but I got to add the if statement into it too and get errors
Count your IF statements.

SY.
Re: Written Assignment-Printing a Record Using an If Statement [message #672410 is a reply to message #672409] Sun, 14 October 2018 07:07 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
What do you mean by that
Re: Written Assignment-Printing a Record Using an If Statement [message #672411 is a reply to message #672410] Sun, 14 October 2018 07:27 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2987
Registered: January 2010
Location: Connecticut, USA
Senior Member
Count how many IF statements code you posted has. Then count how many END IF statements it has. Then ask yourself how many IF statements do you need.

SY.
Re: Written Assignment-Printing a Record Using an If Statement [message #672412 is a reply to message #672406] Sun, 14 October 2018 08:04 Go to previous messageGo to next message
pablolee
Messages: 2881
Registered: May 2007
Location: Scotland
Senior Member
And did you correct your code once you looked at the syntax? If so, post what your code now is (as well as correcting it based on Solomon Y's advice).

[Updated on: Sun, 14 October 2018 08:04]

Report message to a moderator

Re: Written Assignment-Printing a Record Using an If Statement [message #672413 is a reply to message #672412] Sun, 14 October 2018 09:11 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
This is what I got

SQL> SET SERVEROUT ON
SQL> DECLARE
2 EMP_REC HR.EMPLOYEES%ROWTYPE;
3
4 BEGIN
5 SELECT *
6 INTO EMP_REC
7 FROM HR.EMPLOYEES
8 WHERE EMPLOYEE_ID = 100;
9 IF EMP_REC.EMPLOYEE_ID = 100 THEN
10 DBMS_OUTPUT.PUT_LINE ('DEPARTMENT_ID: ' || EMP_REC.DEPARTMENT_ID) ;
11 DBMS_OUTPUT.PUT_LINE ('EMPLOYEE_ID:' || EMP_REC.EMPLOYEE_ID);
12 DBMS_OUTPUT.PUT_LINE ('FIRST_NAME: ' || EMP_REC.FIRST_NAME) ;
13 DBMS_OUTPUT.PUT_LINE ('LAST_NAME: ' || EMP_REC.LAST_NAME) ;
14 DBMS_OUTPUT.PUT_LINE ('SALARY: ' || TO_CHAR (EMP_REC.SALARY, '$999,999'));
15 END IF;
16 EXCEPTION
17 WHEN NO_DATA_FOUND THEN
18 DBMB_OUTPUT.PUT_LINE ('NO DATA FOUND.');
19
20 END;
21 /
DBMB_OUTPUT.PUT_LINE ('NO DATA FOUND.');
*
ERROR at line 18:
ORA-06550: line 18, column 1:
PLS-00201: identifier 'DBMB_OUTPUT.PUT_LINE' must be declared
ORA-06550: line 18, column 1:
PL/SQL: Statement ignored
Re: Written Assignment-Printing a Record Using an If Statement [message #672414 is a reply to message #672413] Sun, 14 October 2018 09:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Sun, 14 October 2018 12:58

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Use SQL*Plus and copy and paste your session, the WHOLE session.

[Updated on: Sun, 14 October 2018 09:47]

Report message to a moderator

Re: Written Assignment-Printing a Record Using an If Statement [message #672415 is a reply to message #672414] Sun, 14 October 2018 09:52 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
I figure out what I did wrong with the last part
Re: Written Assignment-Printing a Record Using an If Statement [message #672416 is a reply to message #672415] Sun, 14 October 2018 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Sun, 14 October 2018 16:46

Michel Cadot wrote on Sun, 14 October 2018 12:58

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
...

#12. If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.


[Updated on: Sun, 14 October 2018 11:21]

Report message to a moderator

Re: Written Assignment-Printing a Record Using an If Statement [message #672419 is a reply to message #672414] Sun, 14 October 2018 13:19 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
NOT 'DBMB_OUTPUT.PUT_LINE'BUT
'DBMS_OUTPUT.PUT_LINE
Re: Written Assignment-Printing a Record Using an If Statement [message #672421 is a reply to message #672419] Mon, 15 October 2018 03:35 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
That IF statement is pointless - the select specifies employeee_id must be 100, so if it finds anything the IF must be true.
If it doesn't find anything then you'll get a no_data_found error and code execution will go from the select straight to the exception handler, meaning the IF will not be executed in that case.

Your instructor really ought to come up with better examples for using IF.
Re: Written Assignment-Printing a Record Using an If Statement [message #672429 is a reply to message #672421] Mon, 15 October 2018 06:03 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
Don't forget the other requirements from your assignments, the spool

SET SERVEROUT ON
spool c:\CS4210\wa5spool.txt 
DECLARE
    Emp_rec   Hr.Employees%ROWTYPE;
BEGIN
    SELECT *
    INTO Emp_rec
    FROM Hr.Employees
    WHERE Employee_id = 100;

    IF Emp_rec.Employee_id = 100 THEN
        DBMS_OUTPUT.Put_line('DEPARTMENT_ID: ' || Emp_rec.Department_id);
        DBMS_OUTPUT.Put_line('EMPLOYEE_ID:' || Emp_rec.Employee_id);
        DBMS_OUTPUT.Put_line('FIRST_NAME: ' || Emp_rec.First_name);
        DBMS_OUTPUT.Put_line('LAST_NAME: ' || Emp_rec.Last_name);
        DBMS_OUTPUT.Put_line(
            'SALARY: ' || TO_CHAR(Emp_rec.Salary, '$999,999'));
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.Put_line('NO DATA FOUND.');
END;
/

spool off
Re: Written Assignment-Printing a Record Using an If Statement [message #672430 is a reply to message #672429] Mon, 15 October 2018 06:16 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2987
Registered: January 2010
Location: Connecticut, USA
Senior Member
And OP should remove that unnecessary IF.

SY.
Re: Written Assignment-Printing a Record Using an If Statement [message #672431 is a reply to message #672430] Mon, 15 October 2018 06:18 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
He is using the IF statement because the professor wanted him to include it. See requirements from first entry. It is completely unnecessary but required for his homework.

1. Set echo on
2. SET SERVEROUT ON
3. Set up a spool file to receive your output for submission. I would suggest c:\CS4210\wa5spool.txt .
4. DECLARE a record variable (Emp_rec) using %ROWTYPE
5. In the BEGIN block add a select statement to read a record into the declared variable from HR.EMPLOYEES
6. Add If Statement to print record
7. Add DBMS_OUTPUT lines to print EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and SALARY for the selected record
8. Use TO_CHAR to format the salary as $999,999
9. Add a EXCEPTION block to report when no data is found
10. Compile and run the procedure.
11. Close the spool file
Re: Written Assignment-Printing a Record Using an If Statement [message #672449 is a reply to message #672421] Mon, 15 October 2018 15:12 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
cookiemonster wrote on Mon, 15 October 2018 03:35
That IF statement is pointless - the select specifies employeee_id must be 100, so if it finds anything the IF must be true.
If it doesn't find anything then you'll get a no_data_found error and code execution will go from the select straight to the exception handler, meaning the IF will not be executed in that case.

Your instructor really ought to come up with better examples for using IF.
Do you have a better if example use instead of employee_id and it doesn't have to 100 that was something that I can up with? This is what he ask for You can use it to determine the Department they work for, like we did in the past with a CASE.
You can use it to print a message if they make over a certain amount of money.
Maybe they are due a raise if they were hired before a certain date.
Re: Written Assignment-Printing a Record Using an If Statement [message #672453 is a reply to message #672449] Tue, 16 October 2018 02:36 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Those last two are perfectly good examples.
Determining the department should be done in the select with a join.
Re: Written Assignment-Printing a Record Using an If Statement [message #672498 is a reply to message #672453] Tue, 16 October 2018 13:33 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
How would you write that in a sqlplus script so that it want give an error when you try to run sql?
Re: Written Assignment-Printing a Record Using an If Statement [message #672513 is a reply to message #672498] Wed, 17 October 2018 03:35 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't understand the question - write what exactly?
Re: Written Assignment-Printing a Record Using an If Statement [message #672514 is a reply to message #672513] Wed, 17 October 2018 03:57 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
I was asking how can I put something like if it finds anything the IF must be true.
If it doesn't find anything then you'll get a no_data_found error and code execution will go from the select straight to the exception handler, meaning the IF will not be executed in that case in sql so that is want give me an error when I run the script
Re: Written Assignment-Printing a Record Using an If Statement [message #672519 is a reply to message #672514] Wed, 17 October 2018 05:37 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's what the code you wrote above does.
Your selecting data where employee_id = 100.
So the select either finds a row that matches that, or it finds nothing and you get no_data_found.
If it finds a row where employee_id = 100 then the subsequent IF check on employee_id has to be true.
Re: Written Assignment-Printing a Record Using an If Statement [message #672549 is a reply to message #672519] Wed, 17 October 2018 14:05 Go to previous messageGo to next message
cwilliam912
Messages: 30
Registered: February 2009
Member
Can you tell me what I did wrong please

my line 3: CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE: =0;
my column 39: DBMS_OUTPUT.PUT_LINE (CRNT_ROOM ||' '||TO_CHAR(AVG_GUESTS(CRNT_ROOM),'$99999
.99')) ||' '||NIGHTS_BOOK);


CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE: =0;
*
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-Printing a Record Using an If Statement [message #672552 is a reply to message #672549] Wed, 17 October 2018 15:45 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
cwilliam912 wrote on Wed, 17 October 2018 15:05
Can you tell me what I did wrong please

my line 3: CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE: =0;
my column 39: DBMS_OUTPUT.PUT_LINE (CRNT_ROOM ||' '||TO_CHAR(AVG_GUESTS(CRNT_ROOM),'$99999
.99')) ||' '||NIGHTS_BOOK);


CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE: =0;
*
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
It's telling you exactly what the problem is
There must be a space before the colon and no space between the colon and the equal sign.

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

This says make a variable called crnt_room with the same structure as the column ROOMNUM in the table LEDGER_VIEW in the schema DDI and then assign an initial value of 0.


Re: Written Assignment-Printing a Record Using an If Statement [message #672562 is a reply to message #672549] Thu, 18 October 2018 04:02 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
cwilliam912 wrote on Wed, 17 October 2018 20:05
Can you tell me what I did wrong please

my line 3: CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE: =0;
my column 39: DBMS_OUTPUT.PUT_LINE (CRNT_ROOM ||' '||TO_CHAR(AVG_GUESTS(CRNT_ROOM),'$99999
.99')) ||' '||NIGHTS_BOOK);
No, column != line. Column is counting across.
line 3:
CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE: =0;
                                      ^
                                      |
                                      column 39 is here
Previous Topic: SQL Query to be grouped based on Column values
Next Topic: Run dbms_scheduler.create_job with arguments
Goto Forum:
  


Current Time: Tue Sep 29 21:48:20 CDT 2020