Querying Progress OpenEdge with SQL

I've recently started in a company that uses Progress OpenEdge 10.1B as a way to store all of our order data. We use it in conjunction with Crystal Reports XI to create a selection of different reports for different parts of the company. It's not a system I'm familiar with, but my boss has asked me to try and run a SQL query on one of the reports he generates in order to add together two seperate rows under certain conditions.

Now, when he calls all the relevant data from OpenEdge it creates this SQL query-

SELECT "esthead1"."k-est-code", "estseqqty1"."k-seq",
"estseqqty1"."estsq-qty", "estroute1"."kdeptsn",
"estroute1"."estr-run-cost-or", "estroute1"."estr-mr-cost-or",
"estrcoat1"."estrc-cost-or" FROM {oj (("VISION"."PUB"."esthead"
"esthead1" LEFT OUTER JOIN "VISION"."PUB"."estseqqty" "estseqqty1" ON
("esthead1"."kco"="estseqqty1"."kco") AND
("esthead1"."k-est-code"="estseqqty1"."k-est-code")) LEFT OUTER JOIN
"VISION"."PUB"."estroute" "estroute1" ON
(("estseqqty1"."kco"="estroute1"."kco") AND
("estseqqty1"."k-est-code"="estroute1"."k-est-code")) AND
("estseqqty1"."k-seq"="estroute1"."k-seq")) LEFT OUTER JOIN
"VISION"."PUB"."estrcoat" "estrcoat1" ON
(("estseqqty1"."kco"="estrcoat1"."kco") AND
("estseqqty1"."k-est-code"="estrcoat1"."k-est-code")) AND
("estseqqty1"."k-seq"="estrcoat1"."k-seq")} WHERE
"esthead1"."k-est-code"='S45160/002'


The field kdeptsn has two instances of each order, but both have a different cost attached, which is shown under estrc-co-or. I'd like to pull up all of this data, but with a the two instances totaled, as one row.

The problem I'm having is that, in the past, I'd always had a SQL table to look at and reference from directly. With OpenEdge, I don't, and Crystal Reports suggests that the above SQL formula is generated through Crystal Reports from whatever data it receives from OpenEdge.

I'd appreciate any suggestions or advice you could give me! Thanks!

Continue reading...