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

["Joe" <jej1216@hotmail.com>]

That was exactly the problem.

Thanks, folks - as usual, this group comes through!

Joe

----- Original Message -----
From: "Larry Roux" <LRoux@syr.edu>
To: <sqr-users@sqrug.org>
Sent: Thursday, January 18, 2007 2:49 PM
Subject: Re: [sqr-users] Select statement returns far fewer 
rowsthandeletestatement using the same criteria

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

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