Subquery's

[vbjohn]

This problem is very strange. When I run a query in the Query Analyzer the query works. BUT, when I run it in a VB application "VB Datareporter" the query does not work at all and it tells me I have an error. The Subquery is where the problem is.

Error Message: DELETE statement conflicted with COLUMN REFERENCE constraint. A conflict occurred while your query attempted to execute. The QueryDesigner has tried to locate the direct source of the conflict. Please check the syntax of your query to attempt to resolve the conflict.

Here is my query....

SELECT RTRIM(UPR00100.LASTNAME) + ', ' + RTRIM(UPR00100.FRSTNAME) + ' ' + RTRIM(UPR00100.MIDLNAME) AS NAME, UPR00100.SOCSCNUM, UPR00100.EMPLOYID, UPR00100.EMPLCLAS, UPR00500.DEDUCTON, UPR30100.GRWGPRN, UPR30100.CHEKDATE, UPR30300.PAYROLCD, UPR30300.UPRTRXAM, UPR30100.CHEKNMBR, UPR30300.TRXSORCE, (SELECT UPR00500.DEDCAMNT_1 FROM UPR00500 WHERE UPR00500.EMPLOYID = UPR00100.EMPLOYID AND UPR00100.EMPLOYID = UPR30100.EMPLOYID AND UPR00500.DEDUCTON = '002') AS TotalIns FROM UPR00100 INNER JOIN UPR00500 ON UPR00100.EMPLOYID = UPR00500.EMPLOYID INNER JOIN UPR30100 ON UPR00100.EMPLOYID = UPR30100.EMPLOYID INNER JOIN UPR30300 ON UPR00100.EMPLOYID = UPR30300.EMPLOYID AND UPR30100.AUCTRLCD = UPR30300.AUCTRLCD WHERE (UPR00500.DEDUCTON = '020') AND (UPR30300.TRXSORCE = ?) AND (UPR30300.PAYROLCD = '020') AND (UPR30100.CHEKNMBR BETWEEN ? AND ?) AND (UPR30100.CHEKDATE = ?) ORDER BY UPR00100.EMPLOYID, UPR00100.LASTNAME

Any help would be great.

 

[tlbroadbent]

I recommend removing the subquery from the select statement. Add it in the FROM clause. You can JOIN to the query which becomes a derived table when used this way.

SELECT
RTRIM(UPR00100.LASTNAME) + ', ' +
RTRIM(UPR00100.FRSTNAME) + ' ' +
RTRIM(UPR00100.MIDLNAME) AS NAME,
UPR00100.SOCSCNUM,
UPR00100.EMPLOYID,
UPR00100.EMPLCLAS,
UPR00500.DEDUCTON,
UPR30100.GRWGPRN,
UPR30100.CHEKDATE,
UPR30300.PAYROLCD,
UPR30300.UPRTRXAM,
UPR30100.CHEKNMBR,
UPR30300.TRXSORCE,
qUPR00500.TotalIns

FROM UPR00100
INNER JOIN UPR00500
ON UPR00100.EMPLOYID = UPR00500.EMPLOYID
INNER JOIN UPR30100
ON UPR00100.EMPLOYID = UPR30100.EMPLOYID
INNER JOIN UPR30300
ON UPR00100.EMPLOYID = UPR30300.EMPLOYID
AND UPR30100.AUCTRLCD = UPR30300.AUCTRLCD
INNER JOIN (SELECT DEDCAMNT_1 AS TotalIns FROM UPR00500 WHERE DEDUCTON = '002') As qUPR00500
ON qUPR00500.EMPLOYID = UPR00100.EMPLOYID

WHERE (UPR00500.DEDUCTON = '020')
AND (UPR30300.TRXSORCE = ?)
AND (UPR30300.PAYROLCD = '020')
AND (UPR30100.CHEKNMBR BETWEEN ? AND ?)
AND (UPR30100.CHEKDATE = ?)

ORDER BY UPR00100.EMPLOYID, UPR00100.LASTNAME
Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous