Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Insert Multiple Values with Case Statement

627414Mar 12 2008 — edited Mar 12 2008
I'm working on a lab with a group and we're supposed to figure out how to insert multiple values using a CASE statement. Here is what we have, although the teacher says it's complete junk. We're really at a loss.

insert into price
(price_id, item_id, price_type, active_flag, start_date, amount, end_date, created_by, creation_date, last_updated_by, last_update_date) values (
CASE
WHEN (SELECT cl.common_lookup_id -- PRICE_TYPE
FROM common_lookup cl
WHERE cl.common_lookup_type = '1-DAY RENTAL') = '1002'
AND i.item_release_date <= (SYSDATE - 30)
THEN (SELECT price_s1.nextval -- PRICE_ID
, i.item_id -- ITEM_ID
,(SELECT cl.common_lookup_id -- PRICE_TYPE
FROM common_lookup cl
WHERE cl.common_lookup_type = '1-DAY RENTAL')
,'N' -- ACTIVE_FLAG
,(i.item_release_date + 31) -- START_DATE
,(SELECT cl.common_lookup_code -- AMOUNT
FROM common_lookup cl
WHERE cl.common_lookup_type = '1-DAY RENTAL')
, NULL -- END_DATE
, 1 -- CREATED_BY
, SYSDATE -- CREATION_DATE
, 1 -- LAST_UPDATED_BY
, SYSDATE -- LAST_UPDATE_DATE
FROM item i )
END)
/

The idea is to use a Case statement to insert multiple values into a table called price. This is supposed to check the video to see if it's a new release and then check the price on it. This price is stored in the common_lookup table. Any help would be useful thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 9 2008
Added on Mar 12 2008
2 comments
1,053 views