RE: SQL dummy

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Fri, 6 Mar 2009 12:40:18 -0500
Message-ID: <F4C27E77F7A33E4CA98C19A9DC6722A2042645DA_at_EXCHANGE.corp.perceptron.com>

Oops, don't need 2 SELECTs:  

INSERT INTO lr_build
(

           num_build,
           task_type,
           task_name,
           task_status

)
SELECT v.num_build,

           v.task_type,
           v.task_name,
           'N Started' 

FROM lr_task_stat v, leecvl c

WHERE c.VARIABLE_VALUE = 'P'
  AND c.NUM_BUILD = v.num_build
  AND c.TASK_TYPE = v.TASK_TYPE
  AND c.TASK_NAME = v.TASK_NAME

  AND NOT EXISTS
  (SELECT 1 from lr_build b

    WHERE b.NUM_BUILD = c.NUM_BUILD
      AND b.TASK_TYPE = c.TASK_TYPE
      AND b.TASK_NAME = c.TASK_NAME);

 



________________________________

	From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Igor Neyman

	Sent: Friday, March 06, 2009 12:32 PM
	To: Lee.Robertson_at_acxiom.com; oracle-l
	Subject: RE: SQL dummy
	
	
	This should work:
	 
	INSERT INTO lr_build
	(
	           num_build,
	           task_type,
	           task_name,
	           task_status
	)
	SELECT
	SELECT v.num_build num_build,
	           v.task_type task_type,
	           v.task_name task_name,
	           'N Started' 
	FROM lr_task_stat v, leecvl c
	WHERE c.VARIABLE_VALUE = 'P'
	  AND c.NUM_BUILD = v.num_build
	  AND c.TASK_TYPE = v.TASK_TYPE
	  AND c.TASK_NAME = v.TASK_NAME
	  AND NOT EXISTS
	  (SELECT 1 from lr_build b
	    WHERE b.NUM_BUILD = c.NUM_BUILD
	      AND b.TASK_TYPE = c.TASK_TYPE
	      AND b.TASK_NAME = c.TASK_NAME);
	
	 
	Igor
	

________________________________

		From: oracle-l-bounce_at_freelists.org

[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Robertson Lee -
lerobe

		Sent: Friday, March 06, 2009 12:26 PM
		To: oracle-l
		Subject: SQL dummy
		
		
		OK, 
		 
		I am the first one to admit I very rarely am called upon

to write SQL, so when some does come along I have to struggle.                  

                What I need to do is insert some information into a table and include a literal                  

                This is what I have been trying but I just cannot get it to work. If I take the task_status bit out (the value I am trying to insert into that column is at the very bottom of the statement) then the whole thing works like a charm                  

                Please help, my brain is farting merrily on a Friday afternoon :-)                  

                The error I get is ORA-00913: too many values. I realise this is because I am trying to push too many values from my original select. I have redone this lots of ways but still to no avail.                  

                I realise I have to somehow pull the table values from the tables and the literal from dual but I cannot figure out how.                  

		INSERT INTO lr_build
		(
		           num_build,
		           task_type,
		           task_name,
		           task_status
		)
		SELECT
		(SELECT v.num_build num_build,
		           v.task_type task_type,
		           v.task_name task_name
		FROM lr_task_stat v, leecvl c
		WHERE c.VARIABLE_VALUE = 'P'
		  AND c.NUM_BUILD = v.num_build
		  AND c.TASK_TYPE = v.TASK_TYPE
		  AND c.TASK_NAME = v.TASK_NAME
		  AND NOT EXISTS
		  (SELECT 1 from lr_build b
		    WHERE b.NUM_BUILD = c.NUM_BUILD
		      AND b.TASK_TYPE = c.TASK_TYPE
		      AND b.TASK_NAME = c.TASK_NAME)),
		'N Started' task_status
		from dual
		;
		
	
************************************************************************
***
		The information contained in this communication is
confidential, is
		intended only for the use of the recipient named above,
and may be legally
		privileged.
		
		If the reader of this message is not the intended
recipient, you are
		hereby notified that any dissemination, distribution or
copying of this
		communication is strictly prohibited.
		
		If you have received this communication in error, please
resend this
		communication to the sender and delete the original
message or any copy
		of it from your computer system.
		
		Thank You.
	
************************************************************************

---

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 06 2009 - 11:40:18 CST