Dynamic SQL Issue?

tradle · Jun 2, 2005

Hi Everyone.

Let me start off by saying that this is my first attempt at dynamic sql. I've been programming with PL/SQL for about a year and a half, but this is the first d-sql I've done.

At issue is this:

I need to populate a column based on what month it is with information that is calculated based on the current date. At a very high level, this is it:

update table set month_column =
((((month1 + month2 + month3)/3) * other_data)* other data)

My code is below, and reflects my most recent attempt at success. Any suggestions would be very much appreciated.

Regards,
Tim

Code:

...
CURSOR three_cost IS
	SELECT r.leaf_node, r.node_05, s.p_id, s.driver_id, s.quarter, s.year
	FROM rqm437t.cost_idt_rollup r, vt_org_prod_vol_stage s
	WHERE r.node_05 = s.secondary_node
	AND s.quarter = 0
	AND (s.secondary_node, s.p_id, s.driver_id) NOT IN (
		SELECT secondary_node, p_id, driver_id
		FROM rqm437t.vtl_driver_duration)
	AND (s.secondary_node, s.p_id, s.driver_id, r.leaf_node) NOT IN (
		SELECT secondary_node, p_id, driver_id, leaf_node
		FROM rqm437t.vtl_driver_allocation)
	AND (r.leaf_node, s.p_id, s.driver_id) IN (
		SELECT org_unit_id, p_id, driver_id
		FROM vt_org_prod_vol_forecast);
...
(variable definitions)
...
BEGIN

SELECT 'MONTH_'||(to_char(sysdate, 'MM')) INTO cmon FROM dual;
SELECT TO_CHAR(sysdate, 'YYYY') INTO cyr FROM dual;
SELECT 'MONTH_'||(to_char(add_months(sysdate, -1), 'MM')) INTO mon1 FROM dual;
SELECT TO_CHAR(ADD_MONTHS(sysdate, -1), 'YYYY') INTO yr1 FROM dual;
SELECT 'MONTH_'||(to_char(add_months(sysdate, -2), 'MM')) INTO mos2 FROM dual;
SELECT TO_CHAR(ADD_MONTHS(sysdate, -2), 'YYYY') INTO yr2 FROM dual;
SELECT 'MONTH_'||(to_char(add_months(sysdate, -3), 'MM')) INTO mos3 FROM dual;
SELECT TO_CHAR(ADD_MONTHS(sysdate, -3), 'YYYY') INTO yr3 FROM dual;
SELECT 'MONTH_'||(to_char(add_months(sysdate, -4), 'MM')) INTO mos4 FROM dual;
SELECT TO_CHAR(ADD_MONTHS(sysdate, -4), 'YYYY') INTO yr4 FROM dual;
SELECT 'MONTH_'||(to_char(add_months(sysdate, -5), 'MM')) INTO mos5 FROM dual;
SELECT TO_CHAR(ADD_MONTHS(sysdate, -5), 'YYYY') INTO yr5 FROM dual;
SELECT 'MONTH_'||(to_char(add_months(sysdate, -6), 'MM')) INTO mos6 FROM dual;
SELECT TO_CHAR(ADD_MONTHS(sysdate, -6), 'YYYY') INTO yr6 FROM dual;
SELECT 'MONTH_'||(to_char(add_months(sysdate, -7), 'MM')) INTO mos7 FROM dual;
SELECT TO_CHAR(ADD_MONTHS(sysdate, -7), 'YYYY') INTO yr7 FROM dual;
SELECT 'MONTH_'||(to_char(add_months(sysdate, -8), 'MM')) INTO mos8 FROM dual;
SELECT TO_CHAR(ADD_MONTHS(sysdate, -8), 'YYYY') INTO yr8 FROM dual;
SELECT 'MONTH_'||(to_char(add_months(sysdate, -9), 'MM')) INTO mos9 FROM dual;
SELECT TO_CHAR(ADD_MONTHS(sysdate, -9), 'YYYY') INTO yr9 FROM dual;
SELECT 'MONTH_'||(to_char(add_months(sysdate, -10), 'MM')) INTO mos10 FROM dual;
SELECT TO_CHAR(ADD_MONTHS(sysdate, -10), 'YYYY') INTO yr10 FROM dual;
SELECT 'MONTH_'||(to_char(add_months(sysdate, -11), 'MM')) INTO mos11 FROM dual;
SELECT TO_CHAR(ADD_MONTHS(sysdate, -11), 'YYYY') INTO yr11 FROM dual;
SELECT 'MONTH_'||(to_char(add_months(sysdate, -12), 'MM')) INTO mos12 FROM dual;
SELECT TO_CHAR(ADD_MONTHS(sysdate, -12), 'YYYY') INTO yr12 FROM dual;

IF cmon in ('MONTH_01')
THEN dsql_cost_str3 := 'UPDATE vt_org_prod_vol_forecast -'
					||' SET MONTH_01 = ) -'
						||'  (((((SELECT :mos3 FROM caa.cost_org_prod_vols_hist WHERE year = :yr3)  -'
						   ||' + (SELECT :mos2 FROM caa.cost_org_prod_vols_hist WHERE year = :yr2) -'
						   ||' + (SELECT :mon1 FROM caa.cost_org_prod_vols_hist WHERE year = :yr1)) / 3) -'
						   ||'	/ (SELECT MONTH_01 FROM vt_org_prod_vol_stage -'
								||'  WHERE secondary_node, p_id, driver_id IN ('||snd3s||', '||pid3s||', '||did3s||'))  -'
									||'	* (SELECT MONTH_01 FROM vt_org_prod_vol_stage -'
										||'  WHERE secondary_node, p_id, driver_id IN ('||snd3s||', '||pid3s||', '||did3s||')) -'
											||'  WHERE org_unit_id = '||oui3s||' -'
											||'  AND p_id = '||pid3s||' -'
											||'  AND driver_id = '||did3s||')';
ELSIF cmon in ('MONTH_02')
THEN dsql_cost_str3 := 'UPDATE vt_org_prod_vol_forecast -'
					||' SET MONTH_02 = ) -'
						||'  (((((SELECT :mos3 FROM caa.cost_org_prod_vols_hist WHERE year = :yr3)  -'
						   ||' + (SELECT :mos2 FROM caa.cost_org_prod_vols_hist WHERE year = :yr2) -'
						   ||' + (SELECT :mon1 FROM caa.cost_org_prod_vols_hist WHERE year = :yr1)) / 3) -'
						   ||'	/ (SELECT MONTH_02 FROM vt_org_prod_vol_stage -'
								||'  WHERE secondary_node, p_id, driver_id IN ('||snd3s||', '||pid3s||', '||did3s||'))  -'
									||'	* (SELECT MONTH_02 FROM vt_org_prod_vol_stage -'
										||'  WHERE secondary_node, p_id, driver_id IN ('||snd3s||', '||pid3s||', '||did3s||')) -'
											||'  WHERE org_unit_id = '||oui3s||' -'
											||'  AND p_id = '||pid3s||' -'
											||'  AND driver_id = '||did3s||')';
ELSIF cmon in ('MONTH_03')
THEN dsql_cost_str3 := 'UPDATE vt_org_prod_vol_forecast -'
					||' SET MONTH_03 = ) -'
						||'  (((((SELECT :mos3 FROM caa.cost_org_prod_vols_hist WHERE year = :yr3)  -'
						   ||' + (SELECT :mos2 FROM caa.cost_org_prod_vols_hist WHERE year = :yr2) -'
						   ||' + (SELECT :mon1 FROM caa.cost_org_prod_vols_hist WHERE year = :yr1)) / 3) -'
						   ||'	/ (SELECT MONTH_03 FROM vt_org_prod_vol_stage -'
								||'  WHERE secondary_node, p_id, driver_id IN ('||snd3s||', '||pid3s||', '||did3s||'))  -'
									||'	* (SELECT MONTH_03 FROM vt_org_prod_vol_stage -'
										||'  WHERE secondary_node, p_id, driver_id IN ('||snd3s||', '||pid3s||', '||did3s||')) -'
											||'  WHERE org_unit_id = '||oui3s||' -'
											||'  AND p_id = '||pid3s||' -'
											||'  AND driver_id = '||did3s||')';
... same pattern up to MONTH_12.
OPEN three_cost;
LOOP

FETCH three_cost INTO oui3s, snd3s, pid3s, did3s, qtr3s, yr3s;
EXIT WHEN three_cost%NOTFOUND;


EXECUTE IMMEDIATE dsql_cost_str3
	 USING oui3s, snd3s, pid3s, did3s, qtr3s, yr3s, mos3, mos2, mon1, cmon, yr3, yr2, yr1;
...
vt_fcb_status := 1;

END LOOP;
CLOSE three_cost;

COMMIT;

EXCEPTION

	WHEN others THEN DBMS_OUTPUT.PUT_LINE('Problems: '||sqlerrm);
	
	ROLLBACK;

	vt_fcb_status := 2;

END;
/
SHOW ERRORS;

eagertotry · Jun 2, 2005

Can't say I completely understand what you're doing here (honestly, didn't spend too much time on it), here are a few suggestions:

1. You don't need all those SELECT...FROM DUAL. Simply assign variables in PL/SQL:

Code:

mos2 := 'MONTH_'||(to_char(add_months(sysdate, -2), 'MM'));

This would make the code simpler and efficient.

2. Think PL/SQL collections (arrays) if the only thing that is changing in those 12 patterns is the MONTH.

HTH

SantaMufasa · Jun 2, 2005

Tradle,

I agree with EagerToTry...It's hard to follow what you are trying to do. In any case, if you are virtually replicating your given code for each of 12 months, them I'm anticipating that we could consolidate your total code down by roughly 95%. Your existing logic for one-month's worth of processing seems, itself, to contain about 80% more code than it needs to. So, IMHO, if we can squeeze this code down by 80% and re-use the code via a procedure, you can imagine the code-saving benefits.

So the first job at hand is for you to provide us a clear, concise functional (non-technical) narrative of what you are trying to do. From that, perhaps we can suggest more compact code.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

tradle · Jun 3, 2005

Thanks for your respective replies, guys. In English, this is what we're doing: data from a staging table (populated from user input) and a history table (populated by other monthly processes) is ultimately being averaged and then populated into a forecast table for accounting purposes. The averages could be 3, 6, 9 or 12 months, as indicated in a different table based on user input.

The thing is, I have to derive some way of determining the columns to use based on what processing month we're in. For example, if we are processing in June and are looking for a 3 month average, I'd be looking to make a query like this:

update forecast
set month_06 = ((((select month_05 + month_04 + month_03
from history)/3) * (stage month_06)) *
(stage month_06))
where x = x
and y = y
and z = z (unique record definition);

In the history table, we have mutliple years of data. Thus, the month_## values have to be derived with the appropriate year.

I'm sorry for the haphazard nature of the code I posted; I've tried so many different variations that the code has gotten a bit out of hand.

Thanks for your help.

Regards,
Tim

tradle · Jun 6, 2005

Hi All -

I've looked at using PL/SQL collections as suggested by HTH, and I'm afraid I don't see how they would help. Given the requirements that I have for this code, I'm still looking for a good solution.

Any suggestions will be much appreciated.

Regards,
Tim

SantaMufasa · Jun 6, 2005

Tradle,

Sorry for my absence over the weekend.

Could you please post the table DESCRIBEs that apply in your case. Also, please tell us the latitude you have to modify the structure of each table in case we see a design inefficiency once you post the DESCRIBEs.

My anticipation of some design flaws results from your references to column names such as "MONTH_05", "MONTH_04", et cetera), which subvert First Normal Form (1NF), a fundamental rule of quality database design.

Looking forward to hearing back from you.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

tradle · Jun 6, 2005

Dave -

I have little or no say over the table structure. Nevertheless, here you go...

STAGE:

Name Null? Type
----------------------------------------- -------- -------------
SECONDARY_NODE NOT NULL NUMBER(14)
P_ID NOT NULL VARCHAR2(15)
DRIVER_ID NOT NULL NUMBER(5)
YEAR NOT NULL VARCHAR2(4)
QUARTER NOT NULL NUMBER(1)
MONTH_01 NUMBER(14,2)
MONTH_02 NUMBER(14,2)
MONTH_03 NUMBER(14,2)
MONTH_04 NUMBER(14,2)
MONTH_05 NUMBER(14,2)
MONTH_06 NUMBER(14,2)
MONTH_07 NUMBER(14,2)
MONTH_08 NUMBER(14,2)
MONTH_09 NUMBER(14,2)
MONTH_10 NUMBER(14,2)
MONTH_11 NUMBER(14,2)
MONTH_12 NUMBER(14,2)
ENTRY_TYPE NOT NULL NUMBER(1)
CALC_FLAG NOT NULL NUMBER(1)

FORECAST:

Name Null? Type
----------------------------------------- -------- --------------
APPL_ID NUMBER(3)
ORG_UNIT_ID NUMBER(14)
DRIVER_ID NUMBER(5)
P_ID VARCHAR2(15)
YEAR VARCHAR2(4)
QUARTER NUMBER(1)
MONTH_01 NUMBER(14,2)
MONTH_02 NUMBER(14,2)
MONTH_03 NUMBER(14,2)
MONTH_04 NUMBER(14,2)
MONTH_05 NUMBER(14,2)
MONTH_06 NUMBER(14,2)
MONTH_07 NUMBER(14,2)
MONTH_08 NUMBER(14,2)
MONTH_09 NUMBER(14,2)
MONTH_10 NUMBER(14,2)
MONTH_11 NUMBER(14,2)
MONTH_12 NUMBER(14,2)
ACTIVE_FLAG NUMBER(1)
CALC_FLAG NUMBER(1)

HISTORY:

Name Null? Type
----------------------------------------- -------- ------------
APPL_ID NOT NULL NUMBER(3)
ORG_UNIT_ID NOT NULL NUMBER(14)
PRODUCT_ID NOT NULL NUMBER(14)
DRIVER_ID NOT NULL NUMBER(5)
YEAR NOT NULL VARCHAR2(4)
MONTH_01 NUMBER(14,2)
MONTH_02 NUMBER(14,2)
MONTH_03 NUMBER(14,2)
MONTH_04 NUMBER(14,2)
MONTH_05 NUMBER(14,2)
MONTH_06 NUMBER(14,2)
MONTH_07 NUMBER(14,2)
MONTH_08 NUMBER(14,2)
MONTH_09 NUMBER(14,2)
MONTH_10 NUMBER(14,2)
MONTH_11 NUMBER(14,2)
MONTH_12 NUMBER(14,2)

Regards,
Tim

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Dynamic SQL Issue?

tradle

Programmer

eagertotry

MIS

SantaMufasa

Technical User

tradle

Programmer

tradle

Programmer

SantaMufasa

Technical User

tradle

Programmer

Similar threads

Part and Inventory Search

Sponsor