Home » SQL & PL/SQL » SQL & PL/SQL » Select table based on condition (Oracle, 10g, Linux)
icon5.gif  Select table based on condition [message #399996] Fri, 24 April 2009 20:20 Go to next message
sagarkhushalani
Messages: 7
Registered: April 2009
Junior Member
Hi

CREATE OR REPLACE PROCEDURE GETRES(
PSSN IN NUMBER, LNA OUT VARCHAR(20), JB OUT CHAR, BD OUT DATE, 
TP OUT NUMBER, SL OUT NUMBER, TG OUT CHAR, EN OUT VARCHAR(12)) 
IS
BEGIN
SELECT LNAME,JOBTYPE,BDATE,SALARY INTO LNA,JB,BD,SL FROM EMPLOYEE WHERE SSN=PSSN;
IF (JB='S') THEN 
SELECT TYPINGSPEED INTO TP FROM SECRETARY WHERE SSN=PSSN;
TG:=NULL;
EN:=NULL;
END IF;
IF (JB='T') THEN 
SELECT TGRADE INTO TG FROM TECHNICIAN WHERE SSN=PSSN;
TP:=NULL;
EN:=NULL;
END IF;
IF (JB='E') THEN 
SELECT ENGTYPE INTO EN FROM ENGINEER WHERE SSN=PSSN;
TP:=NULL;
TG:=NULL;
END IF;
END;
/


I have four tables:

EMPLOYEE (SSN,LNAME,BDATE,SALARY,JOBTYPE)
SECRETARY (SSN,TYPINGSPEED)
TECHNICIAN (SSN,TGRADE)
ENGINEER (SSN,ENGTYPE)


What the procedure is supposed to do:
Given an SSN as input (PSSN), it should find the JOBTYPE, and retrieve the particular attribute (eg if JOBTYPE is 'S' (for secretary), then it should give the Typing speed for the corresponding row in SECRETARY).

Everytime I try to create the procedure, I get:

Warning: Procedure created with compilation errors.

I am attaching the table definition .sql file.

[Updated on: Sat, 25 April 2009 00:53] by Moderator

Report message to a moderator

Re: Select table based on condition [message #399998 is a reply to message #399996] Fri, 24 April 2009 22:15 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
CREATE OR REPLACE PROCEDURE Getres 
     (pssn  IN NUMBER, 
      lna   OUT VARCHAR(20), 
      jb    OUT CHAR, 
      bd    OUT DATE, 
      tp    OUT NUMBER, 
      sl    OUT NUMBER, 
      tg    OUT CHAR, 
      en    OUT VARCHAR(12)) 
IS 
BEGIN 
  SELECT lname, 
         jobtype, 
         bdate, 
         salary 
  INTO   lna,jb,bd,sl 
  FROM   employee 
  WHERE  ssn = pssn; 
   
  IF (jb = 'S') THEN 
    SELECT typingspeed 
    INTO   tp 
    FROM   secretary 
    WHERE  ssn = pssn; 
     
    tg := NULL; 
     
    en := NULL; 
  END IF; 
   
  IF (jb = 'T') THEN 
    SELECT tgrade 
    INTO   tg 
    FROM   technician 
    WHERE  ssn = pssn; 
     
    tp := NULL; 
     
    en := NULL; 
  END IF; 
   
  IF (jb = 'E') THEN 
    SELECT engtype 
    INTO   en 
    FROM   engineer 
    WHERE  ssn = pssn; 
     
    tp := NULL; 
     
    tg := NULL; 
  END IF; 
END; 
/


>Warning: Procedure created with compilation errors.
ERRORS? What Errors? I don't see any errors.

Do you expect us to guess what is wrong?
I am sorry that my crystal ball is in for repair.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

Re: Select table based on condition [message #400002 is a reply to message #399998] Fri, 24 April 2009 23:12 Go to previous messageGo to next message
sagarkhushalani
Messages: 7
Registered: April 2009
Junior Member
I don't know what the errors are, hence the post. All it says are errors while compilation. How do I find the errors?

You can be nice about it. I already read the posting guidelines, TWICE!
Re: Select table based on condition [message #400003 is a reply to message #400002] Fri, 24 April 2009 23:15 Go to previous messageGo to next message
sagarkhushalani
Messages: 7
Registered: April 2009
Junior Member
Never mind. I found the errors. No thanks for your help.
Re: Select table based on condition [message #400004 is a reply to message #399996] Fri, 24 April 2009 23:38 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
All we know is what you post.
We can not know what you did or did not do before posting.
We can not know what you saw but did not post.
All we know is what you post.
Re: Select table based on condition [message #400019 is a reply to message #399996] Sat, 25 April 2009 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you do in PL/SQL something that can be done in a single SQL query?

Regards
Michel
Re: Select table based on condition [message #400042 is a reply to message #400019] Sat, 25 April 2009 07:00 Go to previous messageGo to next message
sagarkhushalani
Messages: 7
Registered: April 2009
Junior Member
@BlackSwan - I already posted all that I did. I even posted the sql to create the tables

Btw, the error was in the VARCHAR declaration (there should be no limit, only type)

@Michel - we coulnd't figure out the single query. We tried a simple SELECT with CASE statements but that didn't work. Any ideas?
Re: Select table based on condition [message #400066 is a reply to message #400042] Sat, 25 April 2009 11:48 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have not your tables.
I have not your data.
I have not your indexes.
I have not your requirements.
...

Basically, something like (but many other possible depending on the previous points):
select ...
from ...
where ...
and jb = 'S'
union all
...
and jb = 'T'
union all
...
and jb = 'E'

Regards
Michel
Previous Topic: null values inserted through 'INSERT ALL' option
Next Topic: CASE and DECODE
Goto Forum:
  


Current Time: Sat Dec 10 16:32:55 CST 2016

Total time taken to generate the page: 0.12855 seconds