We are testing the latest DBF JAR (our previous version was from over 10 years ago!) and using the trial jar we notice that some queries are not removing the table alias prefix. For example, we have this query:
SELECT r."DATE" AS PoDate,
r.PONO AS PoNumber,
r.PO_TIME As PoTime,
r.SHIP_VIA AS ShipVia,
r.CO_NAME AS Vendor,
r.SALESMAN AS BuyerInit,
r.invno,
d.QTY,
0 AS QTY_RCV,
d.QTY AS QTY_BO,
d.COST AS Price,
d.QTY*d.COST AS ExtPrice,
IIF( ISDATE( d.REQ_DATE ),
IIF( TIMESTAMP( d.REQ_DATE ) > r.due_dat,
TIMESTAMP( d.REQ_DATE ),
r.due_dat
),
r.due_dat
) AS RequiredDate,
d."DESC" AS PartSKU,
i.descp AS Description,
false AS wasEmailed
FROM ( ( "DBF\po-rec" AS r
INNER JOIN "DBF\po-dtl" AS d
ON r.REF = d.REF )
LEFT JOIN "DBF\invent" AS i
ON d."desc" = i.itm )
WHERE d.QTY <> 0 AND r.RECEIVED <> 1 ORDER BY PoDate DESC, PoNumber DESC
The resultset keeps "d.QTY" as the returned column name instead of just "QTY" (as it did in previousl versions). I notice this happens on several quesries that have the column name "tbl_alias.QTY" so it seems there is an issue with the "QTY" as a column name?
|
|
When there's a QTY column in po-rec too, HXTT DBF will reserve the table alias so that we can know that QTY is from table A or table B.
|
|
Thank you for your reply. In these cases, there is not also a QTY column in po-rec. The QTY column only exists in po-dtl. I note that these same queries did not exhibit this behavior in the older JDBC 3.0 drivers we worked on years ago.
|
Passed test with the following SQL sample:
CREATE TABLE "po-rec" ( "DATE" DATE, invno INT, REF VARCHAR(16), RECEIVED INT);
CREATE TABLE "po-dtl" ( "QTY" INT, COST DECIMAL(8,2), REF VARCHAR(16), desc VARCHAR(8));
CREATE TABLE "invent" (ITEM VARCHAR(8), descp VARCHAR(16));
INSERT INTO "po-rec" VALUES(NOW(), 123, 'TOM', 2);
INSERT INTO "po-dtl" VALUES(1,65.8, 'TOM', 'Cat');
INSERT INTO "invent" VALUES('Cat', 'Tomcat');
SELECT r."DATE" AS PoDate, r.invno, d.QTY, 0 AS QTY_RCV, d.QTY AS QTY_BO, d.COST AS Price, d.QTY*d.COST AS ExtPrice, i.descp AS Description, false AS wasEmailed FROM ( ( "po-rec" AS r INNER JOIN "po-dtl" AS d
ON r.REF = d.REF ) LEFT JOIN "invent" AS i ON d."desc" = i.itm ) WHERE d.QTY <> 0 AND r.RECEIVED <> 1 ORDER BY PoDate DESC;
So maybe HXTT DBF thought that's a possible column alias confliction in your sample?
|
Main
webmaster@hxtt.com