Taming a big query/view 2

[mrliam69]

I have this query for a complicated costing routine and it's ended up a complicated query that I have just spent 1 hour tidying up so I can see the wood for the trees...

What I would like to know is there anyway of optimizing the code that is blatently obvious to you guru's


=============================================================

SELECT ID, ASSY_STKCODE, TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars)
AS weldsmvtotal2,

((CASE WHEN (TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars)) > 0 THEN
(SELECT v12 FROM TBLSMVVars) ELSE 0 END))
AS TotalWeldSet,

(((CASE WHEN (TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars)) > 0 THEN
(SELECT v12 FROM TBLSMVVars) ELSE 0 END)) / AVG_BATCH)
AS IndWeldSet,

((CASE WHEN (b1count) IN (1, 2) THEN 10
WHEN (b1count) BETWEEN 3 AND 10 THEN (b1count) * 5 - 5 ELSE 0 END)
+ (CASE WHEN (b2count)
IN (1, 2) THEN 10
WHEN (b2count) BETWEEN 3 AND 10 THEN (b2count) * 5 - 5 ELSE 0 END)
+ (CASE WHEN (b3count) IN (1, 2) THEN 10
WHEN (b3count) BETWEEN 3 AND 10 THEN (b3count) * 5 - 5 ELSE 0 END)
+ (CASE WHEN (b4count) IN (1, 2) THEN 10
WHEN (b4count) BETWEEN 3 AND 10 THEN (b4count) * 5 - 5 ELSE 0 END))
AS bendsettotal,

((CASE WHEN (b1count) IN (1, 2) THEN 10
WHEN (b1count) BETWEEN 3 AND
10 THEN (b1count) * 5 - 5 ELSE 0 END)
+ (CASE WHEN (b2count) IN (1, 2) THEN 10
WHEN (b2count) BETWEEN 3 AND 10 THEN (b2count) * 5 - 5 ELSE 0 END)
+ (CASE WHEN (b3count) IN (1, 2) THEN 10 WHEN (b3count)
BETWEEN 3 AND 10 THEN (b3count) * 5 - 5 ELSE 0 END)
+ (CASE WHEN (b4count) IN (1, 2) THEN 10
WHEN (b4count) BETWEEN 3 AND 10 THEN (b4count) * 5 - 5 ELSE 0 END))
/ AVG_BATCH
AS indbendsettotal,

TW_TOTAL *
(SELECT v09 FROM TBLSMVVars)
AS totalweldlen1,

(OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars)
AS totalweldlen2,

TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars)
AS weldsmvtotal,

DFORM_QTY *
(SELECT v01 FROM TBLSMVVars) + TEF_QTY *
(SELECT v11 FROM TBLSMVVars) + FLARE_QTY *
(SELECT v07 FROM TBLSMVVars) +
(SELECT v02 FROM TBLSMVVars) * (BRKT1_OPS_QTY + BRKT2_OPS_QTY + BRKT3_OPS_QTY + BRKT4_OPS_QTY) + HS1_OPS_QTY *
(SELECT v03 FROM TBLSMVVars) + FLAT_PIPE_OPS_QTY * 1 + DRILL_LAMBDA_OPS_QTY *
(SELECT v10 FROM TBLSMVVars)
AS opssmvtotal,

DFORM_QTY *
(SELECT v14 FROM TBLSMVVars) + TEF_QTY *
(SELECT v14 FROM TBLSMVVars) + FLARE_QTY *
(SELECT v15 FROM TBLSMVVars) +
(SELECT v16 FROM TBLSMVVars) * (BRKT1_OPS_QTY + BRKT2_OPS_QTY + BRKT3_OPS_QTY + BRKT4_OPS_QTY) + HS1_OPS_QTY *
(SELECT v13 FROM TBLSMVVars) + FLAT_PIPE_OPS_QTY * 1 + DRILL_LAMBDA_OPS_QTY *
(SELECT v17 FROM TBLSMVVars)
AS opssettotal,

(DFORM_QTY *
(SELECT v14 FROM TBLSMVVars) + TEF_QTY *
(SELECT v14 FROM TBLSMVVars) + FLARE_QTY *
(SELECT v15 FROM TBLSMVVars) +
(SELECT v16 FROM TBLSMVVars) * (BRKT1_OPS_QTY + BRKT2_OPS_QTY + BRKT3_OPS_QTY + BRKT4_OPS_QTY) + HS1_OPS_QTY *
(SELECT v13 FROM TBLSMVVars) + FLAT_PIPE_OPS_QTY * 1 + DRILL_LAMBDA_OPS_QTY *
(SELECT v17 FROM TBLSMVVars)) / AVG_BATCH
AS indopssettotal,

(SELECT v04 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL)) +
(SELECT v06 FROM TBLSMVVars) * TRIM_MITRE_QTY +
(SELECT v04 FROM TBLSMVVars) * CUT_FERRULE_QTY
AS cutsmvtotal,

(SELECT v21 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL)) + COALESCE (B1TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B2TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B3TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B4TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0)
AS bendsmvtotal,

((SELECT v04 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL)) +
(SELECT v06 FROM TBLSMVVars) * TRIM_MITRE_QTY +
(SELECT v04 FROM TBLSMVVars) * CUT_FERRULE_QTY +
(SELECT v21 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL)) + COALESCE (B1TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B2TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B3TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B4TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + DFORM_QTY *
(SELECT v01 FROM TBLSMVVars) + TEF_QTY *
(SELECT v11 FROM TBLSMVVars) + FLARE_QTY *
(SELECT v07 FROM TBLSMVVars) +
(SELECT v02 FROM TBLSMVVars) * (BRKT1_OPS_QTY + BRKT2_OPS_QTY + BRKT3_OPS_QTY + BRKT4_OPS_QTY) + HS1_OPS_QTY *
(SELECT v03 FROM TBLSMVVars) + FLAT_PIPE_OPS_QTY * 1 + DRILL_LAMBDA_OPS_QTY *
(SELECT v10 FROM TBLSMVVars) + TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars)) *
(SELECT v19 FROM TBLSMVVars)
AS overheaddir,

(SELECT v04 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL)) +
(SELECT v06 FROM TBLSMVVars) * TRIM_MITRE_QTY +
(SELECT v04 FROM TBLSMVVars) * CUT_FERRULE_QTY +
(SELECT v21 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL)) + COALESCE (B1TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B2TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B3TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B4TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + DFORM_QTY *
(SELECT v01 FROM TBLSMVVars) + TEF_QTY *
(SELECT v11 FROM TBLSMVVars) + FLARE_QTY *
(SELECT v07 FROM TBLSMVVars) +
(SELECT v02 FROM TBLSMVVars) * (BRKT1_OPS_QTY + BRKT2_OPS_QTY + BRKT3_OPS_QTY + BRKT4_OPS_QTY) + HS1_OPS_QTY *
(SELECT v03 FROM TBLSMVVars) + FLAT_PIPE_OPS_QTY * 1 + DRILL_LAMBDA_OPS_QTY *
(SELECT v10 FROM TBLSMVVars) + TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars)
AS dirsmvtotal,

(SELECT v20 FROM TBLSMVVars) *
((SELECT v04 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL)) +
(SELECT v06 FROM TBLSMVVars) * TRIM_MITRE_QTY +
(SELECT v04 FROM TBLSMVVars) * CUT_FERRULE_QTY)
AS cutsmvvaltotal,

(SELECT v20 FROM TBLSMVVars) *
((SELECT v21 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL)) + COALESCE (B1TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B2TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B3TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B4TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0))
AS bendsmvvaltotal,

(SELECT v20 FROM TBLSMVVars) * (DFORM_QTY *
(SELECT v01 FROM TBLSMVVars) + TEF_QTY *
(SELECT v11 FROM TBLSMVVars) + FLARE_QTY *
(SELECT v07 FROM TBLSMVVars) +
(SELECT v02 FROM TBLSMVVars) * (BRKT1_OPS_QTY + BRKT2_OPS_QTY + BRKT3_OPS_QTY + BRKT4_OPS_QTY) + HS1_OPS_QTY *
(SELECT v03 FROM TBLSMVVars) + FLAT_PIPE_OPS_QTY * 1 + DRILL_LAMBDA_OPS_QTY *
(SELECT v10 FROM TBLSMVVars))
AS opssmvvaltotal,

((SELECT v20 FROM TBLSMVVars) *
(SELECT v09 FROM TBLSMVVars)) * (TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars))
AS weldsmvvaltotal,

(SELECT v20 FROM TBLSMVVars) *
((SELECT v04 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL)) +
(SELECT v06 FROM TBLSMVVars) * TRIM_MITRE_QTY +
(SELECT v04 FROM TBLSMVVars) * CUT_FERRULE_QTY) +
(SELECT v20 FROM TBLSMVVars) *
((SELECT v21 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL)) + COALESCE (B1TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B2TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B3TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B4TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0)) +
(SELECT v20 FROM TBLSMVVars) * (DFORM_QTY *
(SELECT v01 FROM TBLSMVVars) + TEF_QTY *
(SELECT v11 FROM TBLSMVVars) + FLARE_QTY *
(SELECT v07 FROM TBLSMVVars) +
(SELECT v02 FROM TBLSMVVars) * (BRKT1_OPS_QTY + BRKT2_OPS_QTY + BRKT3_OPS_QTY + BRKT4_OPS_QTY) + HS1_OPS_QTY *
(SELECT v03 FROM TBLSMVVars) + FLAT_PIPE_OPS_QTY * 1 + DRILL_LAMBDA_OPS_QTY *
(SELECT v10 FROM TBLSMVVars)) +
((SELECT v20 FROM TBLSMVVars) *
(SELECT v09 FROM TBLSMVVars)) * (TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars))
AS TotalSMVVal,

(SELECT v05 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL) + sign(CUT_FERRULE_QTY) + sign(TRIM_MITRE_QTY))
AS batchcutsettotal,

(SELECT v05 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL) + sign(CUT_FERRULE_QTY) + sign(TRIM_MITRE_QTY)) / AVG_BATCH
AS Indcutsettotal,

(((CASE WHEN (b1count) IN (1, 2)
THEN 10 WHEN (b1count) BETWEEN 3 AND 10 THEN
(b1count) * 5 - 5 ELSE 0 END) + (CASE WHEN (b2count) IN (1, 2) THEN 10
WHEN (b2count) BETWEEN 3 AND 10 THEN (b2count) * 5 - 5 ELSE 0 END)
+ (CASE WHEN (b3count) IN (1, 2) THEN 10
WHEN (b3count) BETWEEN 3 AND 10 THEN
(b3count) * 5 - 5 ELSE 0 END) + (CASE WHEN (b4count) IN (1, 2) THEN 10
WHEN (b4count) BETWEEN 3 AND 10 THEN (b4count)
* 5 - 5 ELSE 0 END)))
+ ((DFORM_QTY *
(SELECT v14 FROM TBLSMVVars) + TEF_QTY *
(SELECT v14 FROM TBLSMVVars) + FLARE_QTY *
(SELECT v15 FROM TBLSMVVars) +
(SELECT v16 FROM TBLSMVVars) * (BRKT1_OPS_QTY + BRKT2_OPS_QTY + BRKT3_OPS_QTY + BRKT4_OPS_QTY) + HS1_OPS_QTY *
(SELECT v13 FROM TBLSMVVars) + FLAT_PIPE_OPS_QTY * 1 + DRILL_LAMBDA_OPS_QTY *
(SELECT v17 FROM TBLSMVVars))) +
((SELECT v05 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL) + sign(CUT_FERRULE_QTY) + sign(TRIM_MITRE_QTY))) + ((((CASE WHEN (TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars)) > 0 THEN
(SELECT v12 FROM TBLSMVVars) ELSE 0 END))))
AS batchsettotal,

(((((CASE WHEN (b1count) IN (1, 2) THEN 10
WHEN (b1count) BETWEEN 3 AND 10 THEN
(b1count) * 5 - 5 ELSE 0 END) + (CASE WHEN (b2count) IN (1, 2) THEN 10
WHEN (b2count) BETWEEN 3 AND 10 THEN
(b2count) * 5 - 5 ELSE 0 END) + (CASE WHEN (b3count) IN (1, 2) THEN 10
WHEN (b3count) BETWEEN 3 AND 10 THEN (b3count) * 5 - 5 ELSE 0 END)
+ (CASE WHEN (b4count) IN (1, 2) THEN 10
WHEN (b4count) BETWEEN 3 AND 10 THEN
(b4count) * 5 - 5 ELSE 0 END)))
+ ((DFORM_QTY *
(SELECT v14 FROM TBLSMVVars) + TEF_QTY *
(SELECT v14 FROM TBLSMVVars) + FLARE_QTY *
(SELECT v15 FROM TBLSMVVars) +
(SELECT v16 FROM TBLSMVVars) * (BRKT1_OPS_QTY + BRKT2_OPS_QTY + BRKT3_OPS_QTY + BRKT4_OPS_QTY) + HS1_OPS_QTY *
(SELECT v13 FROM TBLSMVVars) + FLAT_PIPE_OPS_QTY * 1 + DRILL_LAMBDA_OPS_QTY *
(SELECT v17 FROM TBLSMVVars))) +
((SELECT v05 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL) + sign(CUT_FERRULE_QTY) + sign(TRIM_MITRE_QTY))) + ((((CASE WHEN (TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars)) > 0 THEN
(SELECT v12 FROM TBLSMVVars) ELSE 0 END)))))
/ AVG_BATCH) *
(SELECT v19 FROM TBLSMVVars)
AS OHsettotal,

(((((CASE WHEN (b1count) IN (1, 2) THEN 10
WHEN (b1count) BETWEEN 3 AND 10 THEN
(b1count) * 5 - 5 ELSE 0 END) + (CASE WHEN (b2count) IN (1, 2) THEN 10
WHEN (b2count) BETWEEN 3 AND 10 THEN
(b2count) * 5 - 5 ELSE 0 END) + (CASE WHEN (b3count) IN (1, 2) THEN 10
WHEN (b3count) BETWEEN 3 AND 10 THEN (b3count) * 5 - 5 ELSE 0 END)
+ (CASE WHEN (b4count) IN (1, 2) THEN 10 WHEN (b4count) BETWEEN 3 AND 10
THEN (b4count) * 5 - 5 ELSE 0 END)))
+ ((DFORM_QTY *
(SELECT v14 FROM TBLSMVVars) + TEF_QTY *
(SELECT v14 FROM TBLSMVVars) + FLARE_QTY *
(SELECT v15 FROM TBLSMVVars) +
(SELECT v16 FROM TBLSMVVars) * (BRKT1_OPS_QTY + BRKT2_OPS_QTY + BRKT3_OPS_QTY + BRKT4_OPS_QTY) + HS1_OPS_QTY *
(SELECT v13 FROM TBLSMVVars) + FLAT_PIPE_OPS_QTY * 1 + DRILL_LAMBDA_OPS_QTY *
(SELECT v17 FROM TBLSMVVars))) +
((SELECT v05 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL) + sign(CUT_FERRULE_QTY) + sign(TRIM_MITRE_QTY))) + ((((CASE WHEN (TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars)) > 0 THEN
(SELECT v12 FROM TBLSMVVars) ELSE 0 END)))))
/ AVG_BATCH) *
(SELECT v20 FROM TBLSMVVars)
AS labsettotal,

((((CASE WHEN (b1count) IN (1, 2) THEN 10
WHEN (b1count) BETWEEN 3 AND 10 THEN
(b1count) * 5 - 5 ELSE 0 END) + (CASE WHEN (b2count) IN (1, 2) THEN 10
WHEN (b2count) BETWEEN 3 AND 10 THEN
(b2count) * 5 - 5 ELSE 0 END) + (CASE WHEN (b3count) IN (1, 2) THEN 10
WHEN (b3count) BETWEEN 3 AND 10 THEN (b3count) * 5 - 5 ELSE 0 END)
+ (CASE WHEN (b4count) IN (1, 2) THEN 10
WHEN (b4count) BETWEEN 3 AND 10 THEN
(b4count) * 5 - 5 ELSE 0 END))) + ((DFORM_QTY *
(SELECT v14 FROM TBLSMVVars) + TEF_QTY *
(SELECT v14 FROM TBLSMVVars) + FLARE_QTY *
(SELECT v15 FROM TBLSMVVars) +
(SELECT v16 FROM TBLSMVVars) * (BRKT1_OPS_QTY + BRKT2_OPS_QTY + BRKT3_OPS_QTY + BRKT4_OPS_QTY) + HS1_OPS_QTY *
(SELECT v13 FROM TBLSMVVars) + FLAT_PIPE_OPS_QTY * 1 + DRILL_LAMBDA_OPS_QTY *
(SELECT v17 FROM TBLSMVVars))) +
((SELECT v05 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL) + sign(CUT_FERRULE_QTY) + sign(TRIM_MITRE_QTY))) + ((((CASE WHEN (TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars)) > 0 THEN
(SELECT v12 FROM TBLSMVVars) ELSE 0 END)))))
/ AVG_BATCH
AS indsettotal,

((SELECT v20 FROM TBLSMVVars) *
((SELECT v04 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL)) +
(SELECT v06 FROM TBLSMVVars) * TRIM_MITRE_QTY +
(SELECT v04 FROM TBLSMVVars) * CUT_FERRULE_QTY) +
(SELECT v20 FROM TBLSMVVars) *
((SELECT v21 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL)) + COALESCE (B1TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B2TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B3TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B4TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0)) +
(SELECT v20 FROM TBLSMVVars) * (DFORM_QTY *
(SELECT v01 FROM TBLSMVVars) + TEF_QTY *
(SELECT v11 FROM TBLSMVVars) + FLARE_QTY *
(SELECT v07 FROM TBLSMVVars) +
(SELECT v02 FROM TBLSMVVars) * (BRKT1_OPS_QTY + BRKT2_OPS_QTY + BRKT3_OPS_QTY + BRKT4_OPS_QTY) + HS1_OPS_QTY *
(SELECT v03 FROM TBLSMVVars) + FLAT_PIPE_OPS_QTY * 1 + DRILL_LAMBDA_OPS_QTY *
(SELECT v10 FROM TBLSMVVars)) +
((SELECT v20 FROM TBLSMVVars) *
(SELECT v09 FROM TBLSMVVars)) * (TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars))) +
((((((CASE WHEN (b1count) IN (1, 2) THEN 10
WHEN (b1count) BETWEEN 3 AND 10 THEN
(b1count) * 5 - 5 ELSE 0 END) + (CASE WHEN (b2count) IN (1, 2) THEN 10
WHEN (b2count) BETWEEN 3 AND 10 THEN
(b2count) * 5 - 5 ELSE 0 END) + (CASE WHEN (b3count) IN (1, 2) THEN 10
WHEN (b3count) BETWEEN 3 AND 10 THEN
(b3count) * 5 - 5 ELSE 0 END) + (CASE WHEN (b4count) IN (1, 2) THEN 10
WHEN (b4count) BETWEEN 3 AND 10 THEN
(b4count) * 5 - 5 ELSE 0 END)))
+ ((DFORM_QTY *
(SELECT v14 FROM TBLSMVVars) + TEF_QTY *
(SELECT v14 FROM TBLSMVVars) + FLARE_QTY *
(SELECT v15 FROM TBLSMVVars) +
(SELECT v16 FROM TBLSMVVars) * (BRKT1_OPS_QTY + BRKT2_OPS_QTY + BRKT3_OPS_QTY + BRKT4_OPS_QTY) + HS1_OPS_QTY *
(SELECT v13 FROM TBLSMVVars) + FLAT_PIPE_OPS_QTY * 1 + DRILL_LAMBDA_OPS_QTY *
(SELECT v17 FROM TBLSMVVars))) +
((SELECT v05 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL) + sign(CUT_FERRULE_QTY) + sign(TRIM_MITRE_QTY))) + ((((CASE WHEN (TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars)) > 0 THEN
(SELECT v12 FROM TBLSMVVars) ELSE 0 END)))))
/ AVG_BATCH) *
(SELECT v20 FROM TBLSMVVars)) +
(((SELECT v04 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL)) +
(SELECT v06 FROM TBLSMVVars) * TRIM_MITRE_QTY +
(SELECT v04 FROM TBLSMVVars) * CUT_FERRULE_QTY +
(SELECT v21 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL)) + COALESCE (B1TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B2TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B3TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + COALESCE (B4TOTAL *
(SELECT v18 FROM TBLSMVVars), 0.0) + DFORM_QTY *
(SELECT v01 FROM TBLSMVVars) + TEF_QTY *
(SELECT v11 FROM TBLSMVVars) + FLARE_QTY *
(SELECT v07 FROM TBLSMVVars) +
(SELECT v02 FROM TBLSMVVars) * (BRKT1_OPS_QTY + BRKT2_OPS_QTY + BRKT3_OPS_QTY + BRKT4_OPS_QTY) + HS1_OPS_QTY *
(SELECT v03 FROM TBLSMVVars) + FLAT_PIPE_OPS_QTY * 1 + DRILL_LAMBDA_OPS_QTY *
(SELECT v10 FROM TBLSMVVars) + TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars)) *
(SELECT v19 FROM TBLSMVVars)) +
((((((CASE WHEN (b1count) IN (1, 2) THEN 10
WHEN (b1count) BETWEEN 3 AND 10 THEN
(b1count) * 5 - 5 ELSE 0 END) + (CASE WHEN (b2count) IN (1, 2) THEN 10
WHEN (b2count) BETWEEN 3 AND 10 THEN
(b2count) * 5 - 5 ELSE 0 END) + (CASE WHEN (b3count) IN (1, 2) THEN 10
WHEN (b3count) BETWEEN 3 AND 10 THEN
(b3count) * 5 - 5 ELSE 0 END) + (CASE WHEN (b4count) IN (1, 2) THEN 10
WHEN (b4count) BETWEEN 3 AND 10 THEN
(b4count) * 5 - 5 ELSE 0 END)))
+ ((DFORM_QTY *
(SELECT v14 FROM TBLSMVVars) + TEF_QTY *
(SELECT v14 FROM TBLSMVVars) + FLARE_QTY *
(SELECT v15 FROM TBLSMVVars) +
(SELECT v16 FROM TBLSMVVars) * (BRKT1_OPS_QTY + BRKT2_OPS_QTY + BRKT3_OPS_QTY + BRKT4_OPS_QTY) + HS1_OPS_QTY *
(SELECT v13 FROM TBLSMVVars) + FLAT_PIPE_OPS_QTY * 1 + DRILL_LAMBDA_OPS_QTY *
(SELECT v17 FROM TBLSMVVars))) +
((SELECT v05 FROM TBLSMVVars) * (sign(B1TOTAL) + sign(B2TOTAL) + sign(B3TOTAL) + sign(B4TOTAL) + sign(CUT_FERRULE_QTY) + sign(TRIM_MITRE_QTY))) + ((((CASE WHEN (TW_TOTAL *
(SELECT v09 FROM TBLSMVVars) + (OTHERWELD_TOTAL * 1 * 0.01 +
(SELECT v08 FROM TBLSMVVars)) *
(SELECT v09 FROM TBLSMVVars)) > 0 THEN
(SELECT v12 FROM TBLSMVVars) ELSE 0 END)))))
/ AVG_BATCH) *
(SELECT v19 FROM TBLSMVVars))
AS LabValTotal,

(SELECT SUM(dbo.STK_STOCK_2.STK_COSTPRICE1 * dbo.STK_ASSEMBLIES.AS_QTY)
AS BOMTotal

FROM dbo.STK_ASSEMBLIES INNER JOIN
dbo.STK_STOCK_2 ON dbo.STK_ASSEMBLIES.AS_PART_CODE = dbo.STK_STOCK_2.STKCODE2
WHERE (dbo.STK_ASSEMBLIES.AS_TYPE = 'S') AND (dbo.STK_ASSEMBLIES.AS_MAIN_STKCODE = assy_stkcode)
GROUP BY dbo.STK_ASSEMBLIES.AS_MAIN_STKCODE)
AS rawmaterialcost,

((SELECT SUM(dbo.STK_STOCK_2.STK_COSTPRICE1 * dbo.STK_ASSEMBLIES.AS_QTY)
AS BOMTotal

FROM dbo.STK_ASSEMBLIES INNER JOIN
dbo.STK_STOCK_2 ON dbo.STK_ASSEMBLIES.AS_PART_CODE = dbo.STK_STOCK_2.STKCODE2
WHERE (dbo.STK_ASSEMBLIES.AS_TYPE = 'S') AND (dbo.STK_ASSEMBLIES.AS_MAIN_STKCODE = assy_stkcode)
GROUP BY dbo.STK_ASSEMBLIES.AS_MAIN_STKCODE) *
(SELECT v23 FROM TBLSMVVars))
AS rawmaterialscrap,

(SELECT v22 FROM TBLSMVVars)
AS delivery

FROM dbo.TBL_Assembly_Data

======================================================

 

[gmmastros]

I see alot of this stuff going on...

SELECT v09 FROM TBLSMVVars

It appears as though this table has 1 row, but many columns. That data in the columns are used repeatedly within calculations. My suggestion is to create local variables, setting them just once at the beginning of the SP, and then use the local variables for the calculations. Something like this...

Declare @v09 Integer
Declare @v10 Decimal(10,2)

Select @v09 = v09,
@v10 = v10
From TBLSMVVars

Do this for all variables.

Then Replace:
SELECT v09 FROM TBLSMVVars
With:
@v09




-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrliam69]

Thats Fantastic mate.....

I knew there would be something like that....

BUT.....

I have it set as a view not a stored procedure and the MSACCESS ADP has its datasource set to the view .... Tried it as a straight qery and works great but when I transplant it into a view it fails on the declare.....

Never been down the Stored Procedure Route where would I start ?

 

[vongrunt]

Variables will eliminate possibility to use this monster in a view.

Alternative is: instead of [!]SELECT v01 FROM TBLSMVVars[/!] use only [!]TSV.v01[/!] and then add [!], TBLSMVVars TSV[/!] (cross join) into FROM table list. But BE SURE that table always has only one row.

Some expressions can be simplified/reused as well... but considering the fact column names look like Excel spreadsheet cells I'm afraid no wonders can be expected.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[mrliam69]

Being cheeky could you show me how to add the cross join in

SELECT v01 FROM TBLSMVVars use only TSV.v01 and then add , TBLSMVVars TSV (cross join) into FROM table list.

 

[gmmastros]

Views cannot have any Declares, that's true.

You can change this to a stored procedure, but you need to be aware of how the view is used. The way you interact with a view is different than the way you interact with a stored procedure.

With a view, you can 'toss on' where clauses to filter the data, and 'order by' to sort the data. You can do the same thing in stored procedures, but you do it differently.

For example, if you have a view named vwGetMyData you can issue commands like...

Select Field1, Field2
From vwGetMyData
Where Field3 = 'Some Value'
Order By Field4

With a stored procedure, you create parameters. When calling the stored procedure, you pass the parameter values in.

For example:

Create Procedure GetMyData
  @Field3FilterValue
As
SET NOCOUNT ON
Select Field1, Field2
From   SomeTable
Where  Field3 = @Field3FilterValue
Order By Field4

I'm not sure of the best way to call a stored procedure from an Access ADP, but there must be a way. A little research will probably provide the answer. I suspect that the changes I suggest will speed up the data retrieval process a lot.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrliam69]

Figured it out thanks Vongrunt....

once again indebted to you

 

[mrliam69]

And it has dropped the process time from 13 secs to 5 secs

Cheers once again