Can I use JOIN in a query?

[JamesWilliams]

I am not new to databases, but am new to Progress. We use an app called Chempax on a Progress db, and I am trying to connect directly from Excel to get some data. It doesn't like this query, but doesn't say why. I suspect I have the JOIN formatted incorrectly, but not sure. I am reasonably sure I have the connection string correct. It connects, but the error says there's a problem with the query:

SELECT "BATCH-REC-HDR"."Batch-Num", "BATCH-REC-HDR"."Batch-Warehouse", "BATCH-REC-HDR"."Prod-Pkg-Code", "BATCH-REC-HDR"."Quantity-Manufactured",
"BATCH-REC-LABOR"."Type-1", "BATCH-REC-LABOR"."Labor-Code", SUM("BATCH-REC-LABOR"."Used-Time") AS SUMUSEDTIME, "BATCH-REC-LABOR"."Used-Time-UM",
"BATCH-REC-LABOR"."Cost-UM", SUM("BATCH-REC-LABOR".Cost) AS SUMCOST, SUM("BATCH-REC-LABOR"."Extended-Cost") AS SUMEXCOST,
"BATCH-REC-LABOR"."Created-date"
FROM "BATCH-REC-HDR" RIGHT OUTER JOIN
"BATCH-REC-LABOR" ON ("BATCH-REC-HDR"."Batch-Num" = "BATCH-REC-LABOR"."Batch-Num"
AND "BATCH-REC-HDR"."Batch-Warehouse" = "BATCH-REC-LABOR"."Batch-Warehouse")
WHERE ("BATCH-REC-HDR"."Batch-Warehouse" = '02-01') AND ("BATCH-REC-LABOR"."Created-date" > '1/1/2009')
GROUP BY "BATCH-REC-HDR"."Batch-Num", "BATCH-REC-HDR"."Batch-Warehouse", "BATCH-REC-HDR"."Prod-Pkg-Code", "BATCH-REC-HDR"."Quantity-Manufactured",
"BATCH-REC-LABOR"."Type-1", "BATCH-REC-LABOR"."Labor-Code", "BATCH-REC-LABOR"."Used-Time", "BATCH-REC-LABOR"."Used-Time-UM",
"BATCH-REC-LABOR"."Cost-UM", "BATCH-REC-LABOR"."Created-date"

 

[4GLNewbie]

I notice that you missed two double-quotes on "BATCH-REC-LABOR".Cost, but i dont know if this is the problem.

Did you have any other tool to connect to the db and try the query there?
Maybe if executing your sql from there you can see where the error is..

 

[JamesWilliams]

I think that quote around cost isn't necessary because it doesn't have a hyphen.

 

[Hutch619]

To answer your question about whether or not you can use "JOIN in a query", the answer is yes. Since you're new to Progress, I would suggest going over the sql92 documentation for your particular version (http://communities.progress.com/pcom/docs/DOC-16074). I'd also recommend using a tool like DaFT for testing your queries. If you're still having problems then post the query (in its simplest form) as well as the error that DaFT gives you. Hope this helps!