I have read-only access to a Progress database through ODBC. I have little choice but to make custom views where database views would be much better. This forces me to write queries with subqueries that seem to perform really poorly on my published page but perform really quickly in RazorSQL.
I also have some queries that give an error in ASP.Net Maker but execute fine in RazorSQL.
Same PC, same ODBC connection.
Can this be explained?
Error:
[DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Error in row
Here’s my problematic query. (Go easy on me. I’m a rookie)
SELECT DISTINCT ShopRtg.ShopOrder,
ShopRtg.Operation,
(SELECT DISTINCT TOP 1 ShopOrder.Product
FROM ShopOrder
WHERE ShopOrder.ShopOrder = ShopRtg.ShopOrder
) AS PartNumber,
(SELECT DISTINCT TOP 1 Products.Description
FROM Products
WHERE Products.Product = ShopOrder.Product
) AS Description,
ShopRtg.RtgQty AS SchedQty,
ShopRtg.QtyProduced AS Produced,
ShopRtg.QtyScrapped AS Scrapped,
ShopRtg.ScheduledMachine AS SchedHrs,
ShopRtg.RemainingMachine AS RemainHrs,
ShopRtg.StartDate,
ShopRtg.Rtgseq,
MIN(a.Rtgseq) AS NextSeq,
b.StartDate AS DueDate,
b.Operation AS NextOP
FROM ShopRtg
JOIN ShopOrder
ON ShopRtg.ShopOrder = ShopOrder.ShopOrder
LEFT OUTER JOIN ShopRtg a
ON a.ShopOrder = ShopRtg.ShopOrder
AND a.Rtgseq > ShopRtg.Rtgseq
LEFT OUTER JOIN ShopRtg b
ON b.ShopOrder = ShopRtg.ShopOrder
AND b.Rtgseq = a.Rtgseq
WHERE ShopRtg.CompanyID = ‘SOME COMPANY’
AND ShopRtg.Complete != 1
GROUP BY ShopRtg.ShopOrder,
ShopRtg.Operation,
ShopRtg.RtgQty,
ShopRtg.QtyProduced,
ShopRtg.QtyScrapped,
ShopRtg.ScheduledMachine,
ShopRtg.RemainingMachine,
ShopRtg.StartDate,
ShopRtg.Rtgseq
ORDER BY NextOP,
ShopRtg.ShopOrder
Thanks for your help.