Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: dynamic sql

Re: dynamic sql

From: Sumit Khanna <sumit.khanna_at_bge.com>
Date: 31 Dec 2003 06:07:40 -0800
Message-ID: <74b6bbc8.0312310607.606a9af3@posting.google.com> 





Thanks, but i did not want to provide my whole code over here.  
I am using Oracle 9.2.0.4


v_ExecuteSql is created dynamically therefore not mentioned here. As
an example v_ExecuteSql can be :


SELECT RepData.LOAD_DATE, SL.CONTRACT_ID, RepData.REPORTEDSUPPLIER,
RepData.PSEUDOSUPPLIERBY, SUM(RepData.WEIGHTEDHOUR1) "WEIGHTEDHOUR1"

			 			       FROM SUPPLIER_LOOKUP SL, ( SELECT RDATA.REPORT_NAME,
				  			   RDATA.LOAD_DATE, 
				  			   RDATA.SUPPLIER, 
				  			   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,


SUBSTR(RDATA.SUPPLIER,1,3),RDSA.RESPONSIBLESUPPLIER)

"REPORTEDSUPPLIER",


							   RDATA.HOUR1,
							   RDATA.HOUR2,
							   RDATA.HOUR3,
							   RDATA.HOUR4,
							   RDATA.HOUR5,
							   RDATA.HOUR6,
							   RDATA.HOUR7,
							   RDATA.HOUR8,
							   RDATA.HOUR9,					
		   RDATA.HOUR10,
							   RDATA.HOUR11,
							   RDATA.HOUR12,
							   RDATA.HOUR13,
							   RDATA.HOUR14,
							   RDATA.HOUR15,
							   RDATA.HOUR16,
							   RDATA.HOUR17,
							   RDATA.HOUR18,
							   RDATA.HOUR19,
							   RDATA.HOUR20,
							   RDATA.HOUR21,
							   RDATA.HOUR22,
							   RDATA.HOUR22,
							   RDATA.HOUR24,
				  			   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL, 1,RDSA.WEIGHT)
"WEIGHT",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR1 "WEIGHTEDHOUR1",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR2 "WEIGHTEDHOUR2",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR3 "WEIGHTEDHOUR3",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR4 "WEIGHTEDHOUR4",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR5 "WEIGHTEDHOUR5",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR6 "WEIGHTEDHOUR6",
			   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR7 "WEIGHTEDHOUR7",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR8 "WEIGHTEDHOUR8",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR9 "WEIGHTEDHOUR9",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR10 "WEIGHTEDHOUR10",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR11 "WEIGHTEDHOUR11",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR12 "WEIGHTEDHOUR12",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR13 "WEIGHTEDHOUR13",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR14 "WEIGHTEDHOUR14",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR15 "WEIGHTEDHOUR15",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR16 "WEIGHTEDHOUR16",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR17 "WEIGHTEDHOUR17",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR18 "WEIGHTEDHOUR18",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR19 "WEIGHTEDHOUR19",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR20 "WEIGHTEDHOUR20",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR21 "WEIGHTEDHOUR21",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR22 "WEIGHTEDHOUR22",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,
1,RDSA.WEIGHT)*RDATA.HOUR23 "WEIGHTEDHOUR23",
							   DECODE(RDSA.RESPONSIBLESUPPLIER , NULL,


1,RDSA.WEIGHT)*RDATA.HOUR24 "WEIGHTEDHOUR24",  SUBSTR(RDATA.SUPPLIER,

1, 3 ) ||  SUBSTR(RDATA.SUPPLIER, 4, 4 ) ||  SUBSTR(RDATA.SUPPLIER,

13, 3 ) ||  SUBSTR(RDATA.SUPPLIER, 8, 5 ) "PSEUDOSUPPLIERBY"


							   FROM  TABLE(fn_getReconcileNodeData(
			  							   CURSOR(SELECT RD.REPORT_NAME, 
	 	  											   RD.EFFECTIVE_START_DATE,		
											   RD.EFFECTIVE_END_DATE,
													   RD.PSEUDO_SUPPL_AGGR_SCENARIO,
													   RN.*,
													   SUBSTR(RN.SUPPLIER,1,3) "SUPPLIER3",
													   SUBSTR(RN.SUPPLIER,4,46) "SUPPLIER_REST"
													   FROM RECONCILE_NODE RN, REPORT_DEFINITION RD 
	   												   WHERE RD.REPORT_NAME =  'T2EScheduleDetail' AND
RN.CASE_NAME = 'LVTEST1'
													    AND RD.EFFECTIVE_START_DATE <= RN.LOAD_DATE
													   	  AND (RD.EFFECTIVE_END_DATE >= RN.LOAD_DATE
	    												  	  OR RD.EFFECTIVE_end_DATE  IS NULL)  
	   													  AND RN.LOAD_DATE BETWEEN '01-SEP-03' AND
'01-SEP-03' ))) RDATA
														  LEFT OUTER JOIN  
														  TABLE(fn_getSupplAggrData(
										CURSOR(SELECT RD.REPORT_NAME,
													  RD.EFFECTIVE_START_DATE, 
													  RD.EFFECTIVE_END_DATE,	
												  RD.SUPPL_AGGR_SCENARIO, 
													  SA.ORIGSUPPLIER, 
													  SA.RESPONSIBLESUPPLIER, SA.WEIGHT
												FROM REPORT_DEFINITION RD, SUPPLIERAGGREGATION SA
												WHERE RD.SUPPL_AGGR_SCENARIO = SA.SCENARIO
												AND RD.REPORT_NAME =   'T2EScheduleDetail' ))) RDSA  ON
RDATA.EFFECTIVE_START_DATE = RDSA.EFFECTIVE_START_DATE
							  AND RDATA.EFFECTIVE_END_DATE = RDSA.EFFECTIVE_END_DATE
							  AND RDATA.SUPPLIER3 = RDSA.ORIGSUPPLIER  ORDER BY 1,3  )


RepData  WHERE SL.THREE_BYTE_SUPPLIER = RepData.REPORTEDSUPPLIER 
GROUP BY RepData.LOAD_DATE, SL.CONTRACT_ID,
 RepData.REPORTEDSUPPLIER, RepData.PSEUDOSUPPLIERBY
 


The reason .NEXTVAL is done before is that i only need 1 value for all
the inserts so that will not work.



I want to take 24 values of WeightedHour1 and insert them into 24
rows.  To do that i was trying to make my column names on the fly. 
Let me know if this helps and you can answer my question.



Thanks


Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1072826066.536732_at_yasure>...


> Sumit Khanna wrote:

> > i have code 

> > OPEN csr_CDPM for v_ExecuteSql; 

> > LOOP 


> > FETCH csr_CDPM INTO Cdpm_eSchedule_rec; 

> > exit WHEN csr_CDPM%NOTFOUND; 

> > insert_Ctr := insert_Ctr + 1; 

> > 

> > IF (insert_Ctr = 1) THEN 

> > -- get the next CDPM_OUTpUT ID VALUE 

> > SELECT cdpm_output_seq.NEXTVAL 

> > INTO v_CDPM_OUTPUT_ID 

> > FROM DUAL; 


> > END IF; 


> > 

> > CASE LTRIM(RTRIM(UPPER(v_reportdefrec_tab(1).report_type))) 

> > WHEN c_rpt_type_escheduled THEN 

> > FOR j in 1 .. 24 

> > LOOP 


> > str_Insert_Sql := ''; 

> > v_DataName := 'HOUR' || TO_CHAR(j); 

> > v_DataValueName := 'Cdpm_eSchedule_rec.WEIGHTEDHOUR' || TO_CHAR(j); 

> > v_DataValue := v_DataValueName; 

> > str_Insert_Sql := 'INSERT INTO CDPM_OUTPUT (ID, 

> > PJM_SHORT_NAME, 


> > BASE_CONTRACT, 


> > AGGR_PSEUDO_SUPPLIER_CODE, 


> > RFP_TICKET_NO, 


> > DATA_DATE, 


> > DATA_NAME, 


> > DATA_VALUE) 


> > VALUES (' 


> > || v_cdpm_output_id 

> > || ' , ''' || Cdpm_eSchedule_rec.PjmShortName 

> > || ''' , ''' || Cdpm_eSchedule_rec.ContractId 

> > || ''' , ''' || Cdpm_eSchedule_rec.PseudoSupplBy 

> > || ''' , ''' || Cdpm_eSchedule_rec.RfpTicketNo 

> > || ''' , ' || Cdpm_eSchedule_rec.Load_Date 

> > || ' , ''' || v_DataName 

> > || ''' , '|| v_DataValue || ' )'; 

> > EXECUTE IMMEDIATE str_Insert_Sql; 

> > END LOOP; 


> > ELSE 


> > NULL; 


> > END CASE; 


> > END LOOP; 


> > close csr_CDPM; 

> > 

> > How do i get the value of v_DataValue to be correctly substituted when

> > building my insert statement so that i can execute it correctly.

> > 

> > I am getting 

> > INSERT INTO CDPM_OUTPUT (ID, 


> > PJM_SHORT_NAME, 


> > BASE_CONTRACT, 


> > AGGR_PSEUDO_SUPPLIER_CODE, 


> > RFP_TICKET_NO, 


> > DATA_DATE, 


> > DATA_NAME, 


> > DATA_VALUE) 


> > 

> > VALUES (19 , 'CPSI10' , '15190' , 'SOSRXXXOPXSECKV' , '' , 01-SEP-03 ,


> > 'HOUR24' , Cdpm_eSchedule_rec.WEIGHTEDHOUR24 )

> > i want the value of Cdpm_eSchedule_rec.WEIGHTEDHOUR24 

> > 

> > Any help will be appreciated.

> 

> I'm rying to be constructive but ...

> 

> Don't know what version of Oracle you are using but your SQL indicates 

> something over 10 years old while EXECUTE IMMEDIATE indicates 8i? What 

> is it you are working with?

> 

> Assuming pre-9i you can not use CASE WHEN as you are doing it. It must 

> be in native dynamic SQL. But I can't imaging the syntax you have there 

> working no matter what version of Oracle.

> 

> Your statement using v_ExecuteSql is intended to do what? v_ExecuteSql 

> has no value.

> 

> You might want to rewrite your cursor using an implicit cursor FOR loop.

> 

> And your statement

> SELECT cdpm_output_seq.NEXTVAL

> INTO v_CDPM_OUTPUT_ID

> FROM DUAL;


> does nothing that couldn't be done by by putting cdpm_output_seq.NEXTVAL 

> into your insert statement: It is wasted work.

> 

> And please please stop the LTRIM(RTRIM( ... the syntax is TRIM().

> 

> My overall impression of the code you provided is that you tried a bunch 

> of things ... put them all together ... like making a stew. But never 

> stopped along the way to see if any of it actually worked.

> 

> I'd suggest you throw away what you did and start over ... incrementally 

> trying things and only using them after proving that they work.

Received on Wed Dec 31 2003 - 08:07:40 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US