Correcting Syntax errors in Progress 9.1D09 SQL exploder

Posted by Admin on 14-Jul-2009 13:08

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?


P.S. here is the code I'm trying to run.

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"
WHERE = pub.sbl.Batch AND =  AND"Item-code" = pub.sbl."Item-code" AND = pub.sol.Branch AND"Item-code" = pub.sol."Item-code" AND  = pub.iib.Branch AND"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 ===
[JDBC Progress Driver]:Syntax error (7587)

I updated the statement to have " " around field names.

Message was edited by: Bob Bolduc

All Replies

Posted by Thomas Mercer-Hursh on 15-Jul-2009 11:19

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.

Posted by Admin on 15-Jul-2009 11:38

Thanks Thomas.. I was looking for some sort of debugging function, or possibly commnets on another tool other than SQL Explorer.

Posted by Thomas Mercer-Hursh on 15-Jul-2009 12:01

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 ...

Posted by Admin on 16-Jul-2009 01:33

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.

Posted by Thomas Mercer-Hursh on 16-Jul-2009 12:19

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.

This thread is closed