1 Replies - 2398 Views - Last Post: 15 October 2010 - 04:57 AM Rate Topic: -----

#1 IronMan8  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 11-May 10

Oracle insert statement problem

Posted 14 October 2010 - 09:14 PM

I am having a problem with one of the insert statements in oracle. I have created a table named price that has a column that called end_date. Basically it is for a movie rental store and if a movie is past 30 days of its release_date then it switches from being an new release to an old release. The column end_date has a check constraint on it that is not optional . Here is the code that I made to create my table


BEGIN
  FOR i IN (SELECT null FROM user_tables WHERE table_name = 'PRICE') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE price CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT null FROM user_sequences WHERE sequence_name = 'RENTAL_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE price_s1';
  END LOOP;
END;
/

CREATE TABLE PRICE 
( price_id                NUMBER
, item_id                 NUMBER CONSTRAINT nn_price_1 NOT NULL
, price_type              NUMBER   
, active_flag             VARCHAR2(1)   CONSTRAINT nn_price_3 NOT NULL
, start_date              DATE CONSTRAINT nn_price_2 NOT NULL
, end_date                DATE
, amount                  NUMBER
, created_by              NUMBER CONSTRAINT nn_price_4 NOT NULL
, creation_date           DATE   CONSTRAINT nn_price_5 NOT NULL
, last_updated_by         NUMBER CONSTRAINT nn_price_6 NOT NULL
, last_update_date        DATE   CONSTRAINT nn_price_7 NOT NULL
, CONSTRAINT pk_price_1      PRIMARY KEY(price_id)
, CONSTRAINT cc_price_1      CHECK (active_flag IN('Y','N'))
, CONSTRAINT cc_price_11     CHECK (end_date IN (start_date + 30))
, CONSTRAINT fk_price_1      FOREIGN KEY(item_id) REFERENCES ITEM (item_id)
, CONSTRAINT fk_price_3      FOREIGN KEY(created_by) REFERENCES system_user(system_user_id)
, CONSTRAINT fk_price_4      FOREIGN KEY(last_updated_by) REFERENCES system_user(system_user_id));

-- Create a sequence.
CREATE SEQUENCE price_s1 START WITH 1001;




Here is the code where i am trying to insert something into the end_date but query doesn't work

INSERT INTO    price
(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 = '5-DAY-RENTAL')
 ,'N'                              -- ACTIVE_FLAG
 , CASE                            -- START_DATE
     WHEN (TRUNC(SYSDATE) - TRUNC(i.release_date)) > 30
       THEN i.release_date
       ELSE i.release_date  
   END
 , (SELECT   p.start_date
   FROM     price p
   WHERE    p.end_date = p.start_date + 30)                     -- END_DATE
 ,(SELECT   CASE                   -- AMOUNT
              WHEN (TRUNC(SYSDATE) - TRUNC(i.release_date)) > 30
               THEN 5
               ELSE TO_NUMBER(cl.common_lookup_code)
             END
   FROM   common_lookup cl
   WHERE   cl.common_lookup_type = '5-DAY-RENTAL')
 , 1                               -- CREATED_BY
 , TRUNC(SYSDATE)                         -- CREATION_DATE
 , 1                               -- LAST_UPDATED_BY
 , TRUNC(SYSDATE)                         -- LAST_UPDATE_DATE
 FROM   (item i));


If anyone could help that would be great.

Is This A Good Question/Topic? 0
  • +

Replies To: Oracle insert statement problem

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5795
  • View blog
  • Posts: 12,628
  • Joined: 16-October 07

Re: Oracle insert statement problem

Posted 15 October 2010 - 04:57 AM

Let me just format that select:
SELECT price_s1.NEXTVAL, i.item_id,
		(
			SELECT cl.common_lookup_id 
				FROM  common_lookup cl 
				WHERE cl.common_lookup_type = '5-DAY-RENTAL'
		),
		'N', 
		CASE
			WHEN (TRUNC(SYSDATE) - TRUNC(i.release_date)) > 30 THEN i.release_date
			ELSE i.release_date  
		END,
		(
			SELECT   p.start_date
			FROM price p
			WHERE p.end_date = p.start_date + 30
		),
		(
			SELECT 
					CASE 
						WHEN (TRUNC(SYSDATE) - TRUNC(i.release_date)) > 30 THEN 5
						ELSE TO_NUMBER(cl.common_lookup_code)
					END
				FROM   common_lookup cl
				WHERE   cl.common_lookup_type = '5-DAY-RENTAL'
		), 
		1, TRUNC(SYSDATE),1,TRUNC(SYSDATE)
	FROM item i;



Does this even run?

For this to work, price would have to have one, and only one, value:
(
	SELECT   p.start_date
	FROM price p
	WHERE p.end_date = p.start_date + 30
),


The other sub queries have the same requirement.

This does nothing, really. Both sides of your logic return the same value
[/code]
CASE
WHEN (TRUNC(SYSDATE) - TRUNC(i.release_date)) > 30 THEN i.release_date
ELSE i.release_date
END,
[/code]


All those sub queries are points of failure. If you're doing identical ones, like against common_lookup, they're also a point of locking.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1