Re: [sqr-users] Select statement returns far fewer rowsthandeletestatement using the same criteria

["Larry Roux" <LRoux@syr.edu>]

Yeah, you can't qualify fields and tables with aliases on deletes. 
However you can fully qualify them with the TABLE.FIELD notation.



Larry Roux
Information Systems
Syracuse University
lroux@syr.edu


>>> jej1216@hotmail.com 1/18/2007 4:40 PM >>>
Thanks - I'll try fully qualifying it.  I had dropped the 'C' synonym 
because it error-ed out with it there.
- Joe

----- Original Message -----
From: "Bob Stone" <bstone@fastenal.com>
To: "This list is for discussion about the SQR database reporting 
languagefrom Hyperion Solutions." <sqr-users@sqrug.org>
Sent: Thursday, January 18, 2007 2:33 PM
Subject: RE: [sqr-users] Select statement returns far fewer rows 
thandeletestatement using the same criteria

I concur.  You dropped off the 'C' synonym when you turned it into a
delete, which DOES change the statement (or at least could change it).

The line AND OT.ASSET_ID = C.ASSET_ID is explicit about which columns
it
should compare: ASSET_ID on PS_OPEN_TRANS to ASSET_ID on PS_COST.  The
line AND OT.ASSET_ID = ASSET_ID hopes that the implicit table
definition
will correctly assign it.  But since ASSET_ID is on both PS_OPEN_TRANS
and PS_COST, it defaults to first table in the join (which in this
exists would be PS_OPEN_TRANS) - so it thinks you mean AND OT.ASSET_ID
=
OT.ASSET_ID (which is a pretty silly statement to have in a join).

You can have a synonym in the delete statement, same as the 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)

turns into

DELETE
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)

-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org 
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org] On Behalf Of
Larry Roux
Sent: Thursday, January 18, 2007 3:20 PM
To: sqr-users@sqrug.org 
Subject: Re: [sqr-users] Select statement returns far fewer rows
thandelete statement using the same criteria

Try fully qualifying the exists statement?  ie :


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

also,  PS_OPEN_TRANS wouldn't happen to be a view, would it?  I was
thinking if it was the data beneath it could be changing as you do the
deletes.





Larry Roux
Information Systems
Syracuse University
lroux@syr.edu 


>>> jej1216@hotmail.com 1/18/2007 4:14 PM >>>
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 

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

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


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

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