Hi All,
I have to create a SQL view, with many calculations, and I'm getting Syntax errors but it's not telling me where or what is incorrect.
Is there a better way to debug?
Thanks,
P.S. here is the code I'm trying to run.
SELECT
CASE WHEN (sbl.location = 'qchold' sum(sbl."qty-apply") - (sbs."qty-mfg" - sbs."qty-rej")) ELSE sbs."qty-mfg" - sbs."qty-rej" AS "tot_good_prod",
sbs."rej-qty-base" AS "tot_scrap",
CASE WHEN (sbl.location = 'qchold' sum(sbl."qty-apply")) AS "shift_hold",
sbs.station AS press,
MHR_SBSSOL."retail-price" AS "Selling_price",
sol."seq-pre" AS "seq-pre",
sol."seq-num" AS "seq-num",
sol."so-line" AS "so-line",
iib."item-cost-flex" AS "Std_cost",
sbs."cost-lev-mtr" + sbs."cost-lev-lbr" + sbs."cost-lev-sta" + sbs."cost-lev-set" + sbs."cost-lev-ovr" + sbs."cost-lev-tol" + sbs."cost-rol-mtr" + sbs."cost-rol-lbr" + sbs."cost-rol-sta" + sbs."cost-rol-sta" + sbs."cost-rol-set" + sbs."cost-rol-ovr" + sbs."cost-rol-tol") / (sbs."qty-mfg" - sbs."qty-rej") AS Act_mfg_cst,
sbs."set-hrs-up" AS "setup_hrs",
sbs."dwn-hours" AS "down_time" ,
sbs."actual-cost" * ( "tot_good_prod" + "tot_in_hold") AS "daily_mfg_cst",
sbs."actual-cost" AS "Act_cost_matl",
iib."std-cost" AS "Std_matl_cost",
"tot_good_prod" * "std_cost_matl",
"selling_price" - "act_cost_matl" AS "tot_std_cost_matl",
(sbs."mfg-qty-base" - sbs."rej-qty-base") / (sbs."run-hours" - sbs."run-hours-nc") AS "Selling_Less_raw",
bmq."part-hour" AS "Act_pph",
"act_pph" / "std_pph" AS "Std_pph",
"Selling_Less_raw" * "Act_pph" AS "Act_MH_return_contrib",
"Std_pph" * ("Selling_price" - "Std_cost_matl") AS "std_pph_std_mh_return_contrib",
"Act_MH_return_contrib" - "std_pph_std_mh_return_contrib "AS "net_diff",
sbs."run-hours" - sbs."run-hours-nc" AS "tot_act_hrs",
"Act_MH_return_contrib" * "tot_act_hrs" AS "std_cost_2",
"net_diff" * "tot_act_hrs" AS "Tot_MHR_gn_ls",
"tot_good_prod" * "Std_cost" AS "tot_left_on_table",
"tot_good_prod" * "Selling_price" AS "SLI_Hours",
"Inv_val" AS "Inv_val",
"sales_value" AS "Sales_value",
"tot_sales_value_BMP" + "tot_sales_val_SLI" AS "BMP_in_val",
"tot_act_hrs" AS "tot_sales_value_value_BMP",
"BMP_Hours" + "SLI_Hours" AS "tot_sales_val_SLI"
FROM "MHR_SBSSBL", "MHR_SBSSOL", "MHR_SBSIIX", "MHR_SBSBMQ"
WHERE pub.sbs.Batch = pub.sbl.Batch AND pub.sbs.shell = pub.sbl.shell AND pub.sbs."Item-code" = pub.sbl."Item-code" AND pub.sbs.Branch = pub.sol.Branch AND pub.sbs."Item-code" = pub.sol."Item-code" AND pub.sbs.Branch = pub.iib.Branch AND pub.sbs."Item-code" = pub.iib."Item-code" AND pub.iib."Item-code" = pub.iix."Item-code"
AND sbs.Branch = bmq.Branch AND sbs."Item-code" = bmq."Item-code";
P.S.S. I have alraedy created the MHR_SBSSBL, MHR_SBSSOL, MHR_SBSIIX, MHR_SBSBMQ views.and for now I'm just trying to get the select to work once that's working I can add the create view statements etc.
=== SQL Exception 1 ===
SQLState=42000
ErrorCode=-20003
[JDBC Progress Driver]:Syntax error (7587)
I updated the statement to have " " around field names.
Message was edited by: Bob Bolduc
For me, the only real trick for debugging SQL is to peel it back to something that does work and then add back pieces one at a time until it breaks and try to figure out why. Not infrequently, it doesn't break during reassembly because the problem was some missing comma or whatever that got corrected doing it in pieces.
FWIW, SQL is more fun the later the version of Progress, so you have an intrinsic handicap.
Thanks Thomas.. I was looking for some sort of debugging function, or possibly commnets on another tool other than SQL Explorer.
I've never found any form of SQL debugger in any environment, nor any tool which provided any particular help. Sometimes the error message points to the area of the problem (sometimes lying!) and sometimes it doesn't. Seems to be the nature of SQL ...
Are 10.1 or 10.2 an option (can be installed side by side with 9.1D)?
For querying purposes it should be possible to define a 9.1.D JDBC driver connection in OpenEdge Architect and use the DB Navigator utility.
Yeah, but in the end, DB Navigator is just SQL, no? With a query of this size, the issue is usually getting all the i's dotted and t's crossed and figuring out the right place to put things. E.g., I've had a lot more success putting field matches in the join statements than in the where clause.
Which said, trying to use SQL on 9.1D is starting out with one had tied behind your back.