RE: [sqr-users] Select statement returns far fewer rows thandeletestatement using the same criteria

["the dragon" <ceprn@hotmail.com>]

Don't know if this is the issue, because what you're describing should be 
impossible, but change the * in your subselect to a 1.

   AND EXISTS (SELECT 1 FROM PS_OPEN_TRANS OT

Oh, wait, I have it............  Your code is *NOT* the same.  You are not 
using the outside alias in your subselect.  You *MUST* reference outside 
variables in a subselect with an alias, or it assumes you're talking about 
the variables inside the subselect.

this is wrong:

DELETE FROM PS_COST
WHERE ASSET_ID      = '000000000315'
   AND BUSINESS_UNIT = '00010'
   AND EXISTS (SELECT 1 FROM PS_OPEN_TRANS OT
                WHERE OT.CALC_DEPR_STATUS = 'P'
                  AND OT.DTTM_STAMP    = DTTM_STAMP
                  AND OT.TRANS_TYPE    = TRANS_TYPE
                  AND OT.ASSET_ID      = ASSET_ID
                  AND OT.BUSINESS_UNIT = BUSINESS_UNIT
                  AND OT.BOOK          = BOOK)

But this would be OK

DELETE FROM PS_COST A
WHERE A.ASSET_ID      = '000000000315'
   AND A.BUSINESS_UNIT = '00010'
   AND EXISTS (SELECT 1 FROM PS_OPEN_TRANS
                WHERE CALC_DEPR_STATUS = 'P'
                  AND DTTM_STAMP    = A.DTTM_STAMP
                  AND TRANS_TYPE    = A.TRANS_TYPE
                  AND ASSET_ID      = A.ASSET_ID
                  AND BUSINESS_UNIT = A.BUSINESS_UNIT
                  AND BOOK          = A.BOOK)

peace,
clark 'the dragon' willis


PSA: Salary <> Slavery. If you earn a salary, your employer is renting your 
services for 40 hours a week, not purchasing your soul. Your time is the 
only real finite asset that you have, and once used it can never be 
recovered, so don't waste it by giving it away.

I work to live; I don't live to work.

"Time is the coin of your life. It is the only coin you have, and only you 
can determine how it will be spent. Be careful lest you let other people 
spend it for you." -- Carl Sandburg (1878 - 1967)

It is impossible to defeat an ignorant man in argument. -- William G. McAdoo

Religion is regarded by the common people as true, by the wise as false, and 
by the rulers as useful. -- Seneca




----Original Message Follows----

I have an SQL question, but this group has proven to be such a goldmine of 
help for SQR, I wanted to post this here as well as in a SQL Server group.

I have a select statement that returns 36 rows.  I changed it to a delete 
statement by simply replacing 'Select * from' with 'Delete from.'  The 
delete statement deletes 191 rows.

I would think that, regardless of whether or not the criteria is correct, 
both the 'select' and the 'delete' would hit the same number of rows.

Here is my select:

SELECT *
FROM PS_COST C
WHERE C.ASSET_ID      = '000000000315'
   AND C.BUSINESS_UNIT = '00010'
   AND EXISTS (SELECT * FROM PS_OPEN_TRANS OT
                WHERE OT.CALC_DEPR_STATUS = 'P'
                  AND OT.DTTM_STAMP    = C.DTTM_STAMP
                  AND OT.TRANS_TYPE    = C.TRANS_TYPE
                  AND OT.ASSET_ID      = C.ASSET_ID
                  AND OT.BUSINESS_UNIT = C.BUSINESS_UNIT
                  AND OT.BOOK          = C.BOOK)

And here is my delete:

DELETE FROM PS_COST
WHERE ASSET_ID      = '000000000315'
   AND BUSINESS_UNIT = '00010'
   AND EXISTS (SELECT * FROM PS_OPEN_TRANS OT
                WHERE OT.CALC_DEPR_STATUS = 'P'
                  AND OT.DTTM_STAMP    = DTTM_STAMP
                  AND OT.TRANS_TYPE    = TRANS_TYPE
                  AND OT.ASSET_ID      = ASSET_ID
                  AND OT.BUSINESS_UNIT = BUSINESS_UNIT
                  AND OT.BOOK          = BOOK)

Any help would be appreciated.

- Joe

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_________________________________________________________________
Laugh, share and connect with Windows Live Messenger 
http://clk.atdmt.com/MSN/go/msnnkwme0020000001msn/direct/01/?href=http://imagine-msn.com/messenger/launch80/default.aspx?locale=en-us&source=hmtagline


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users