PL/SQL If Statement Question

Dev Shed Forums Sponsor:

April 27th, 2006, 10:51 AM

RoLLiNLoW54

Contributing User

Join Date: Dec 2004

Posts: 219

Time spent in forums: 1 Day 16 h 37 m 6 sec
Reputation Power: 4

PL/SQL If Statement Question

Hey guys, im relatively new to Oracle, and im caught in a bind.

I have a view with employee training data.

I have a table with training course reviews. The training course reviews only contain records when someone fills out a course review form.

I'm pulling all of the employee training data from the view and using it in a reporting system. Somehow, I need to find out whether each record (or employee / course record) has filled out a review and return a 1 or 0 so that I may search the view and sort by the value.

I'm assuming this will involve some sort of if statement or while loop. Any help is greatly appreciated, thanks!

April 27th, 2006, 11:18 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 7th Plane (8000 - 8499 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 8,073

Time spent in forums: 2 Months 4 Weeks 4 h 19 m 37 sec
Reputation Power: 281

Post your table structure and some sample data (preferably as INSERT statements), I'm pretty shure it can be done with a query.

__________________
My blog
Tutorials about OSS databases, DBMonster ...
Contribute to OSS Development, fill bug reports!
Developer Shed eSupport
Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins
Random data (with a bias)

April 27th, 2006, 11:53 AM

RoLLiNLoW54

Contributing User

Join Date: Dec 2004

Posts: 219

Time spent in forums: 1 Day 16 h 37 m 6 sec
Reputation Power: 4

Table PROJECT_USERS
- registration_id
- person_id
- class_id
- last_name
- first_name
- org
- title
- vendor
- instructor_name

Insert into PROJECT_USERS
(REGISTRATION_ID, CLASS_ID, PERSON_ID, LAST_NAME, FIRST_NAME, ORG, TITLE, VENDOR, INSTRUCTOR_NAME)
Values
(8, 65, 99, 'Doe', 'John', 'HR', 'Demostration Class', 'Dynadeen Corporation', 'Ryan Jackson');
COMMIT;

That would be an example of the 'view' which is actually be created from 8 different tables.

I have this table:

Table PROJECT_REVIEWS
- review_id
- class_id
- person_id
- anonymous
- comments
- overall_rating

Insert into PROJECT_REVIEWS
(REVIEW_ID, CLASS_ID, PERSON_ID, ANONYMOUS, COMMENTS, OVERALL_RATING)
Values
(1, 64, 3738, '0', 'Good Course', '3');
COMMIT;

Now, there are many instances of PROJECT_USERS who have not filled out a review, so there is no record in PROJECT_REVIEWS for their specific class_id and person_id. In this case, in the VIEW above, there would theoretically be a column titled "REVIEW_STATUS", and if someone hasn't filled out a review, it would return 0 for that. if someone has filled out a review, meaning there is a record in PROJECT_REVIEWS with the same person_id and class_id as a record in PROJECT_USERS, it would return 1 for that.

Let me know if that made any sense

April 27th, 2006, 12:11 PM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 8,073

Time spent in forums: 2 Months 4 Weeks 4 h 19 m 37 sec
Reputation Power: 281

You need a left outer join to place nulls for missing reviews (review_id as review_status), then use a coalesce/case/decode to change the nulls into 0.
You've been kind enough to post inserts, why not posting create table also?

April 27th, 2006, 12:28 PM

RoLLiNLoW54

Contributing User

Join Date: Dec 2004

Posts: 219

Time spent in forums: 1 Day 16 h 37 m 6 sec
Reputation Power: 4

this should work:

Code:

CREATE TABLE PROJECT_USERS
(
  REGISTRATION_ID             INTEGER           NOT NULL,
  CLASS_ID                    INTEGER,
  PERSON_ID                   INTEGER,
  LAST_NAME                   VARCHAR2(20),
  FIRST_NAME                  VARCHAR2(50),
  ORG                         VARCHAR2(50),
  TITLE                       VARCHAR2(4000),
  VENDOR                      VARCHAR2(4000),
  INSTRUCTOR_NAME             VARCHAR2(4000)
)
TABLESPACE USERS
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       4096
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           );


ALTER TABLE PROJECT_USERS ADD (
  PRIMARY KEY
 (REGISTRATION_ID)
    USING INDEX 
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          128K
                NEXT             128K
                MINEXTENTS       1
                MAXEXTENTS       4096
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
               ));




CREATE TABLE MSL_REVIEWS
(
  REVIEW_ID       INTEGER                       NOT NULL,
  CLASS_ID        INTEGER,
  PERSON_ID       INTEGER,
  ANONYMOUS       CHAR(1),
  COMMENTS        VARCHAR2(4000),
  OVERALL_RATING  CHAR(1),
)
TABLESPACE USERS
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       4096
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           );

ALTER TABLE MSL_REVIEWS ADD (
  PRIMARY KEY
 (REVIEW_ID)
    USING INDEX 
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          128K
                NEXT             128K
                MINEXTENTS       1
                MAXEXTENTS       4096
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
               ));

thanks for your help, by the way

Viewing: Dev Shed Forums > Databases > Oracle Development > PL/SQL If Statement Question

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

More Free IT White Papers!