Complicated SP - DISTINCT issue

Complicated SP - DISTINCT issue

Post by Gerar » Sat, 27 Dec 2003 17:14:20



I have a need for the DISTINCT keyword(Of course), but you
cannot update a recordset that was returned using
DISTINCT.  So I thought I came up with a great solution.
Using the EXISTS keyword, I have a subquery identical to
the main query, except for the DISTINCT keyword is used in
an effort to try and filter the main query, but my result
set is exactly the same as if I had simply taken the
DISTINCT keyword off of the main query.  Below is the
original query, and the modified query that is trying to
filter out the records that do not appear in the sub-
query.  I absolutely need DISTINCT rows but also need to
be able to update them.  Does anyone see what is wrong
with my fix or have an alternate way of getting DISTINCT
rows without the DISTINCT keyword.  I have included the
SP's, and DDL for both tables.  Thanks in advance, and
hope everyone had a happy holiday.
                         Gerard

-------------------------------------------
----Original SP w/DISTINCT-----------------
-------------------------------------------
CREATE PROCEDURE spBasRunACHBatch
AS
SELECT DISTINCT tblACH_Batch.AB_ABA_NUM,
tblACH_Batch.AB_ACCTNUM,
tblACH_Batch.AB_AMOUNT, tblACH_Batch.AB_TRACK,
tblACH_Batch.AB_NAME, tblACH_Batch.AB_C_S_IND,
tblACH_Batch.AB_BEG_DTE, tblACH_Batch.AB_END_DTE,
tblACH_Batch.AB_SCHED, tblACH_Batch.AB_COMP_ID,
tblACH_Batch.AB_ONCE_SW, tblACH_Batch.AB_RETRY,
tblACH_Batch.INDICATORS,
tblACH_Company.RETRY_AMOUNT, tblACH_Company.[KEY]
FROM tblACH_Batch LEFT JOIN tblACH_Company ON
tblACH_Batch.AB_COMP_ID = tblACH_Company.[KEY]
WHERE (SUBSTRING(tblACH_BATCH.INDICATORS,1,1) Like ' ' OR
tblACH_Batch.AB_ONCE_SW <> 0)
ORDER BY tblACH_Batch.AB_COMP_ID,tblACH_Batch.AB_NAME
GO

--------------------------------------------------
----Modified SP, Trying to get DISTINCT rows
----Without using DISTINCT keyword in main query--
--------------------------------------------------
CREATE PROCEDURE spBasRunACHBatch
AS
SELECT tblACH_Batch.AB_ABA_NUM, tblACH_Batch.AB_ACCTNUM,
tblACH_Batch.AB_AMOUNT, tblACH_Batch.AB_TRACK,
tblACH_Batch.AB_NAME, tblACH_Batch.AB_C_S_IND,
tblACH_Batch.AB_BEG_DTE, tblACH_Batch.AB_END_DTE,
tblACH_Batch.AB_SCHED, tblACH_Batch.AB_COMP_ID,
tblACH_Batch.AB_ONCE_SW, tblACH_Batch.AB_RETRY,
tblACH_Batch.INDICATORS,
tblACH_Company.RETRY_AMOUNT, tblACH_Company.[KEY]
FROM tblACH_Batch LEFT JOIN tblACH_Company ON
tblACH_Batch.AB_COMP_ID = tblACH_Company.[KEY]
WHERE (SUBSTRING(tblACH_BATCH.INDICATORS,1,1) Like ' ' OR
tblACH_Batch.AB_ONCE_SW <> 0) AND EXISTS
        (SELECT DISTINCT tblACH_Batch.AB_ABA_NUM,
tblACH_Batch.AB_ACCTNUM,
        tblACH_Batch.AB_AMOUNT, tblACH_Batch.AB_TRACK,
        tblACH_Batch.AB_NAME, tblACH_Batch.AB_C_S_IND,
        tblACH_Batch.AB_BEG_DTE, tblACH_Batch.AB_END_DTE,
        tblACH_Batch.AB_SCHED, tblACH_Batch.AB_COMP_ID,
        tblACH_Batch.AB_ONCE_SW, tblACH_Batch.AB_RETRY,
        tblACH_Batch.INDICATORS,
        tblACH_Company.RETRY_AMOUNT, tblACH_Company.[KEY]
        FROM tblACH_Batch LEFT JOIN tblACH_Company ON
tblACH_Batch.AB_COMP_ID = tblACH_Company.[KEY]
        WHERE (SUBSTRING(tblACH_BATCH.INDICATORS,1,1)
Like ' ' OR tblACH_Batch.AB_ONCE_SW <> 0))
ORDER BY tblACH_Batch.AB_COMP_ID,tblACH_Batch.AB_NAME
GO

---------------------------------------------------
----tblACH_Batch DDL-------------------------------
---------------------------------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblACH_Batch]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblACH_Batch]
GO

CREATE TABLE [dbo].[tblACH_Batch] (
        [ID] [int] IDENTITY (1, 1) NOT NULL ,
        [AB_ABA_NUM] [nvarchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [AB_ACCTNUM] [nvarchar] (17) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [AB_AMOUNT] [float] NULL ,
        [AB_TRACK] [nvarchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [AB_NAME] [nvarchar] (22) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [AB_C_S_IND] [nvarchar] (1) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [AB_BEG_DTE] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [AB_END_DTE] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [AB_REMARKS] [ntext] COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [AB_SCHED] [nvarchar] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [AB_COMP_ID] [nvarchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [AB_ONCE_SW] [bit] NULL ,
        [INDICATORS] [nvarchar] (36) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [AB_PERIODS] [float] NULL ,
        [AB_RETRY] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

------------------------------------------------------
----tblACH_Company DDL--------------------------------
------------------------------------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblACH_Company]') and OBJECTPROPERTY
(id, N'IsUserTable') = 1)
drop table [dbo].[tblACH_Company]
GO

CREATE TABLE [dbo].[tblACH_Company] (
        [KEY] [nvarchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [COMP_NAME] [nvarchar] (16) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [COMP_FEDID] [nvarchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [COMP_DESC] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [COMP_ABA] [nvarchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [COMP_DDATA] [nvarchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [COMP_ACC] [nvarchar] (17) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [CH_SA_IND] [nvarchar] (1) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [DE_CR_IND] [nvarchar] (1) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [COMP_ICD] [nvarchar] (1) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [COMP_SECC] [nvarchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [COMP_SDOFF] [float] NULL ,
        [COMP_FTYPE] [nvarchar] (1) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [COMP_AF_ID] [nvarchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [DETAIL_IND] [bit] NOT NULL ,
        [RTNDOCTYPE] [nvarchar] (1) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [COMP_PTLNK] [nvarchar] (7) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [COMP_PTYPE] [nvarchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [RETRY_AMOUNT] [float] NULL ,
        [RATE_ACH] [float] NULL ,
        [RATE_ACHPercent] [float] NULL ,
        [RATE_BAT] [float] NULL ,
        [RATE_CHK] [float] NULL ,
        [RATE_CKV] [float] NULL ,
        [RATE_COL] [float] NULL ,
        [RATE_CRD] [float] NULL ,
        [RATE_ENT] [float] NULL ,
        [RATE_FIX] [float] NULL ,
        [RATE_FND] [float] NULL ,
        [RATE_MIN] [float] NULL ,
        [RATE_RTN] [float] NULL ,
        [RATE_WIR] [float] NULL ,
        [FEE_PMTIND] [nvarchar] (1) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [RATE_SDATE] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [PROC_CTRLS] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [INDICATORS] [nvarchar] (36) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [COMP_BILL] [nvarchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [RESERVE] [float] NULL
) ON [PRIMARY]
GO

 
 
 

Complicated SP - DISTINCT issue

Post by Steve Kas » Sat, 27 Dec 2003 17:25:00


Gerard,

  What does it mean to update only distinct rows?  If you have a table
with duplicate rows and only want to update one of the duplicates, how
can the query processor know which information to update?  For an
update query to be legal, it must specify unambiguously what row(s) to
update.

  Can you give an example of what you want the before and after pictures
to look like?

SK


>I have a need for the DISTINCT keyword(Of course), but you
>cannot update a recordset that was returned using
>DISTINCT.  So I thought I came up with a great solution.
>Using the EXISTS keyword, I have a subquery identical to
>the main query, except for the DISTINCT keyword is used in
>an effort to try and filter the main query, but my result
>set is exactly the same as if I had simply taken the
>DISTINCT keyword off of the main query.  Below is the
>original query, and the modified query that is trying to
>filter out the records that do not appear in the sub-
>query.  I absolutely need DISTINCT rows but also need to
>be able to update them.  Does anyone see what is wrong
>with my fix or have an alternate way of getting DISTINCT
>rows without the DISTINCT keyword.  I have included the
>SP's, and DDL for both tables.  Thanks in advance, and
>hope everyone had a happy holiday.
>                         Gerard

>-------------------------------------------
>----Original SP w/DISTINCT-----------------
>-------------------------------------------
>CREATE PROCEDURE spBasRunACHBatch
>AS
>SELECT DISTINCT tblACH_Batch.AB_ABA_NUM,
>tblACH_Batch.AB_ACCTNUM,
>tblACH_Batch.AB_AMOUNT, tblACH_Batch.AB_TRACK,
>tblACH_Batch.AB_NAME, tblACH_Batch.AB_C_S_IND,
>tblACH_Batch.AB_BEG_DTE, tblACH_Batch.AB_END_DTE,
>tblACH_Batch.AB_SCHED, tblACH_Batch.AB_COMP_ID,
>tblACH_Batch.AB_ONCE_SW, tblACH_Batch.AB_RETRY,
>tblACH_Batch.INDICATORS,
>tblACH_Company.RETRY_AMOUNT, tblACH_Company.[KEY]
>FROM tblACH_Batch LEFT JOIN tblACH_Company ON
>tblACH_Batch.AB_COMP_ID = tblACH_Company.[KEY]
>WHERE (SUBSTRING(tblACH_BATCH.INDICATORS,1,1) Like ' ' OR
>tblACH_Batch.AB_ONCE_SW <> 0)
>ORDER BY tblACH_Batch.AB_COMP_ID,tblACH_Batch.AB_NAME
>GO

>--------------------------------------------------
>----Modified SP, Trying to get DISTINCT rows
>----Without using DISTINCT keyword in main query--
>--------------------------------------------------
>CREATE PROCEDURE spBasRunACHBatch
>AS
>SELECT tblACH_Batch.AB_ABA_NUM, tblACH_Batch.AB_ACCTNUM,
>tblACH_Batch.AB_AMOUNT, tblACH_Batch.AB_TRACK,
>tblACH_Batch.AB_NAME, tblACH_Batch.AB_C_S_IND,
>tblACH_Batch.AB_BEG_DTE, tblACH_Batch.AB_END_DTE,
>tblACH_Batch.AB_SCHED, tblACH_Batch.AB_COMP_ID,
>tblACH_Batch.AB_ONCE_SW, tblACH_Batch.AB_RETRY,
>tblACH_Batch.INDICATORS,
>tblACH_Company.RETRY_AMOUNT, tblACH_Company.[KEY]
>FROM tblACH_Batch LEFT JOIN tblACH_Company ON
>tblACH_Batch.AB_COMP_ID = tblACH_Company.[KEY]
>WHERE (SUBSTRING(tblACH_BATCH.INDICATORS,1,1) Like ' ' OR
>tblACH_Batch.AB_ONCE_SW <> 0) AND EXISTS
>    (SELECT DISTINCT tblACH_Batch.AB_ABA_NUM,
>tblACH_Batch.AB_ACCTNUM,
>    tblACH_Batch.AB_AMOUNT, tblACH_Batch.AB_TRACK,
>    tblACH_Batch.AB_NAME, tblACH_Batch.AB_C_S_IND,
>    tblACH_Batch.AB_BEG_DTE, tblACH_Batch.AB_END_DTE,
>    tblACH_Batch.AB_SCHED, tblACH_Batch.AB_COMP_ID,
>    tblACH_Batch.AB_ONCE_SW, tblACH_Batch.AB_RETRY,
>    tblACH_Batch.INDICATORS,
>    tblACH_Company.RETRY_AMOUNT, tblACH_Company.[KEY]
>    FROM tblACH_Batch LEFT JOIN tblACH_Company ON
>tblACH_Batch.AB_COMP_ID = tblACH_Company.[KEY]
>    WHERE (SUBSTRING(tblACH_BATCH.INDICATORS,1,1)
>Like ' ' OR tblACH_Batch.AB_ONCE_SW <> 0))
>ORDER BY tblACH_Batch.AB_COMP_ID,tblACH_Batch.AB_NAME
>GO

>---------------------------------------------------
>----tblACH_Batch DDL-------------------------------
>---------------------------------------------------
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[tblACH_Batch]') and OBJECTPROPERTY(id,
>N'IsUserTable') = 1)
>drop table [dbo].[tblACH_Batch]
>GO

>CREATE TABLE [dbo].[tblACH_Batch] (
>    [ID] [int] IDENTITY (1, 1) NOT NULL ,
>    [AB_ABA_NUM] [nvarchar] (9) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [AB_ACCTNUM] [nvarchar] (17) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [AB_AMOUNT] [float] NULL ,
>    [AB_TRACK] [nvarchar] (8) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [AB_NAME] [nvarchar] (22) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [AB_C_S_IND] [nvarchar] (1) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [AB_BEG_DTE] [nvarchar] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [AB_END_DTE] [nvarchar] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [AB_REMARKS] [ntext] COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [AB_SCHED] [nvarchar] (4) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [AB_COMP_ID] [nvarchar] (3) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [AB_ONCE_SW] [bit] NULL ,
>    [INDICATORS] [nvarchar] (36) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [AB_PERIODS] [float] NULL ,
>    [AB_RETRY] [bit] NULL
>) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO

>------------------------------------------------------
>----tblACH_Company DDL--------------------------------
>------------------------------------------------------
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[tblACH_Company]') and OBJECTPROPERTY
>(id, N'IsUserTable') = 1)
>drop table [dbo].[tblACH_Company]
>GO

>CREATE TABLE [dbo].[tblACH_Company] (
>    [KEY] [nvarchar] (3) COLLATE
>SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>    [COMP_NAME] [nvarchar] (16) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [COMP_FEDID] [nvarchar] (9) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [COMP_DESC] [nvarchar] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [COMP_ABA] [nvarchar] (9) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [COMP_DDATA] [nvarchar] (20) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [COMP_ACC] [nvarchar] (17) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [CH_SA_IND] [nvarchar] (1) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [DE_CR_IND] [nvarchar] (1) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [COMP_ICD] [nvarchar] (1) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [COMP_SECC] [nvarchar] (3) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [COMP_SDOFF] [float] NULL ,
>    [COMP_FTYPE] [nvarchar] (1) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [COMP_AF_ID] [nvarchar] (3) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [DETAIL_IND] [bit] NOT NULL ,
>    [RTNDOCTYPE] [nvarchar] (1) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [COMP_PTLNK] [nvarchar] (7) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [COMP_PTYPE] [nvarchar] (2) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [RETRY_AMOUNT] [float] NULL ,
>    [RATE_ACH] [float] NULL ,
>    [RATE_ACHPercent] [float] NULL ,
>    [RATE_BAT] [float] NULL ,
>    [RATE_CHK] [float] NULL ,
>    [RATE_CKV] [float] NULL ,
>    [RATE_COL] [float] NULL ,
>    [RATE_CRD] [float] NULL ,
>    [RATE_ENT] [float] NULL ,
>    [RATE_FIX] [float] NULL ,
>    [RATE_FND] [float] NULL ,
>    [RATE_MIN] [float] NULL ,
>    [RATE_RTN] [float] NULL ,
>    [RATE_WIR] [float] NULL ,
>    [FEE_PMTIND] [nvarchar] (1) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [RATE_SDATE] [nvarchar] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [PROC_CTRLS] [nvarchar] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [INDICATORS] [nvarchar] (36) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [COMP_BILL] [nvarchar] (3) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>    [RESERVE] [float] NULL
>) ON [PRIMARY]
>GO


 
 
 

Complicated SP - DISTINCT issue

Post by Foo Man Che » Sat, 27 Dec 2003 17:27:46


Quote:> I have a need for the DISTINCT keyword(Of course), but you
> cannot update a recordset that was returned using
> DISTINCT.

Why are you trying to update a recordset?  Have you considered using an
UPDATE table statement?

If you obtained the values using distinct, it sounds like you don't have a
primary key.  If you don't have a primary key, how do you expect the
database to know which row you want to update?

Why don't you have a primary key?

 
 
 

Complicated SP - DISTINCT issue

Post by <anonym.. » Sat, 27 Dec 2003 17:41:45


I do have primary keys for both tables, even though it is
not shown in the DDL.  I thought UPDATE table is only used
on a singular table, I have a recordset containing two
different tables.
Quote:>-----Original Message-----
>> I have a need for the DISTINCT keyword(Of course), but
you
>> cannot update a recordset that was returned using
>> DISTINCT.

>Why are you trying to update a recordset?  Have you
considered using an
>UPDATE table statement?

>If you obtained the values using distinct, it sounds like
you don't have a
>primary key.  If you don't have a primary key, how do you
expect the
>database to know which row you want to update?

>Why don't you have a primary key?

>.

 
 
 

Complicated SP - DISTINCT issue

Post by Erland Sommarsko » Sat, 27 Dec 2003 17:47:54



> I have a need for the DISTINCT keyword(Of course), but you
> cannot update a recordset that was returned using
> DISTINCT.  So I thought I came up with a great solution.
> Using the EXISTS keyword, I have a subquery identical to
> the main query, except for the DISTINCT keyword is used in
> an effort to try and filter the main query, but my result
> set is exactly the same as if I had simply taken the
> DISTINCT keyword off of the main query.  Below is the
> original query, and the modified query that is trying to
> filter out the records that do not appear in the sub-
> query.  I absolutely need DISTINCT rows but also need to
> be able to update them.  Does anyone see what is wrong
> with my fix or have an alternate way of getting DISTINCT
> rows without the DISTINCT keyword.  I have included the
> SP's, and DDL for both tables.  

First, you don't say what client-side tool you are using. If you are
using ADO.Net, defined an Update procedure which updates the rows
you need to update.

If you are using ADO, ADO is trying from behind the scenes to figure
out which rows you want to update, but is obviously not very successful.
Rather than using the .Update method in ADO, define a stored
procedure which performs the update, and call this procedure.

--

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

Complicated SP - DISTINCT issue

Post by Gerar » Sat, 27 Dec 2003 17:46:43


I understand that.  That is why you cannot update a table
that used the DISTINCT keyword to return the recordset,
the DBE doesn't know what exactly to update.  That is why
I am trying to not use it.  The point I am trying to make
is how to get DISTINCT rows without using the DISTINCT
keyword, there must be an algorithm or an alternate way of
doing things.  With DISTINCT I get 9003 rows, which is
what I want.  Without DISTINCT, I get 9064 rows, 61 rows
too many.  But I NEED to be able to update this recordset
after a change, hense my need to not use DISTINCT.  Thanks
again,
                      Gerard
>-----Original Message-----
>Gerard,

>  What does it mean to update only distinct rows?  If you
have a table
>with duplicate rows and only want to update one of the
duplicates, how
>can the query processor know which information to
update?  For an
>update query to be legal, it must specify unambiguously
what row(s) to
>update.

>  Can you give an example of what you want the before and
after pictures
>to look like?

>SK

>Gerard wrote:

>>I have a need for the DISTINCT keyword(Of course), but
you
>>cannot update a recordset that was returned using
>>DISTINCT.  So I thought I came up with a great solution.
>>Using the EXISTS keyword, I have a subquery identical to
>>the main query, except for the DISTINCT keyword is used
in
>>an effort to try and filter the main query, but my
result
>>set is exactly the same as if I had simply taken the
>>DISTINCT keyword off of the main query.  Below is the
>>original query, and the modified query that is trying to
>>filter out the records that do not appear in the sub-
>>query.  I absolutely need DISTINCT rows but also need to
>>be able to update them.  Does anyone see what is wrong
>>with my fix or have an alternate way of getting DISTINCT
>>rows without the DISTINCT keyword.  I have included the
>>SP's, and DDL for both tables.  Thanks in advance, and
>>hope everyone had a happy holiday.
>>                         Gerard

>>-------------------------------------------
>>----Original SP w/DISTINCT-----------------
>>-------------------------------------------
>>CREATE PROCEDURE spBasRunACHBatch
>>AS
>>SELECT DISTINCT tblACH_Batch.AB_ABA_NUM,
>>tblACH_Batch.AB_ACCTNUM,
>>tblACH_Batch.AB_AMOUNT, tblACH_Batch.AB_TRACK,
>>tblACH_Batch.AB_NAME, tblACH_Batch.AB_C_S_IND,
>>tblACH_Batch.AB_BEG_DTE, tblACH_Batch.AB_END_DTE,
>>tblACH_Batch.AB_SCHED, tblACH_Batch.AB_COMP_ID,
>>tblACH_Batch.AB_ONCE_SW, tblACH_Batch.AB_RETRY,
>>tblACH_Batch.INDICATORS,
>>tblACH_Company.RETRY_AMOUNT, tblACH_Company.[KEY]
>>FROM tblACH_Batch LEFT JOIN tblACH_Company ON
>>tblACH_Batch.AB_COMP_ID = tblACH_Company.[KEY]
>>WHERE (SUBSTRING(tblACH_BATCH.INDICATORS,1,1) Like ' '
OR
>>tblACH_Batch.AB_ONCE_SW <> 0)
>>ORDER BY tblACH_Batch.AB_COMP_ID,tblACH_Batch.AB_NAME
>>GO

>>--------------------------------------------------
>>----Modified SP, Trying to get DISTINCT rows
>>----Without using DISTINCT keyword in main query--
>>--------------------------------------------------
>>CREATE PROCEDURE spBasRunACHBatch
>>AS
>>SELECT tblACH_Batch.AB_ABA_NUM, tblACH_Batch.AB_ACCTNUM,
>>tblACH_Batch.AB_AMOUNT, tblACH_Batch.AB_TRACK,
>>tblACH_Batch.AB_NAME, tblACH_Batch.AB_C_S_IND,
>>tblACH_Batch.AB_BEG_DTE, tblACH_Batch.AB_END_DTE,
>>tblACH_Batch.AB_SCHED, tblACH_Batch.AB_COMP_ID,
>>tblACH_Batch.AB_ONCE_SW, tblACH_Batch.AB_RETRY,
>>tblACH_Batch.INDICATORS,
>>tblACH_Company.RETRY_AMOUNT, tblACH_Company.[KEY]
>>FROM tblACH_Batch LEFT JOIN tblACH_Company ON
>>tblACH_Batch.AB_COMP_ID = tblACH_Company.[KEY]
>>WHERE (SUBSTRING(tblACH_BATCH.INDICATORS,1,1) Like ' '
OR
>>tblACH_Batch.AB_ONCE_SW <> 0) AND EXISTS
>>        (SELECT DISTINCT tblACH_Batch.AB_ABA_NUM,
>>tblACH_Batch.AB_ACCTNUM,
>>        tblACH_Batch.AB_AMOUNT, tblACH_Batch.AB_TRACK,
>>        tblACH_Batch.AB_NAME, tblACH_Batch.AB_C_S_IND,
>>        tblACH_Batch.AB_BEG_DTE, tblACH_Batch.AB_END_DTE,
>>        tblACH_Batch.AB_SCHED, tblACH_Batch.AB_COMP_ID,
>>        tblACH_Batch.AB_ONCE_SW, tblACH_Batch.AB_RETRY,
>>        tblACH_Batch.INDICATORS,
>>        tblACH_Company.RETRY_AMOUNT, tblACH_Company.[KEY]
>>        FROM tblACH_Batch LEFT JOIN tblACH_Company ON
>>tblACH_Batch.AB_COMP_ID = tblACH_Company.[KEY]
>>        WHERE (SUBSTRING(tblACH_BATCH.INDICATORS,1,1)
>>Like ' ' OR tblACH_Batch.AB_ONCE_SW <> 0))
>>ORDER BY tblACH_Batch.AB_COMP_ID,tblACH_Batch.AB_NAME
>>GO

>>---------------------------------------------------
>>----tblACH_Batch DDL-------------------------------
>>---------------------------------------------------
>>if exists (select * from dbo.sysobjects where id =
>>object_id(N'[dbo].[tblACH_Batch]') and OBJECTPROPERTY
(id,
>>N'IsUserTable') = 1)
>>drop table [dbo].[tblACH_Batch]
>>GO

>>CREATE TABLE [dbo].[tblACH_Batch] (
>>        [ID] [int] IDENTITY (1, 1) NOT NULL ,
>>        [AB_ABA_NUM] [nvarchar] (9) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [AB_ACCTNUM] [nvarchar] (17) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [AB_AMOUNT] [float] NULL ,
>>        [AB_TRACK] [nvarchar] (8) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [AB_NAME] [nvarchar] (22) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [AB_C_S_IND] [nvarchar] (1) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [AB_BEG_DTE] [nvarchar] (10) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [AB_END_DTE] [nvarchar] (10) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [AB_REMARKS] [ntext] COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [AB_SCHED] [nvarchar] (4) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [AB_COMP_ID] [nvarchar] (3) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [AB_ONCE_SW] [bit] NULL ,
>>        [INDICATORS] [nvarchar] (36) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [AB_PERIODS] [float] NULL ,
>>        [AB_RETRY] [bit] NULL
>>) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>>GO

>>------------------------------------------------------
>>----tblACH_Company DDL--------------------------------
>>------------------------------------------------------
>>if exists (select * from dbo.sysobjects where id =
>>object_id(N'[dbo].[tblACH_Company]') and OBJECTPROPERTY
>>(id, N'IsUserTable') = 1)
>>drop table [dbo].[tblACH_Company]
>>GO

>>CREATE TABLE [dbo].[tblACH_Company] (
>>        [KEY] [nvarchar] (3) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>>        [COMP_NAME] [nvarchar] (16) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [COMP_FEDID] [nvarchar] (9) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [COMP_DESC] [nvarchar] (10) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [COMP_ABA] [nvarchar] (9) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [COMP_DDATA] [nvarchar] (20) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [COMP_ACC] [nvarchar] (17) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [CH_SA_IND] [nvarchar] (1) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [DE_CR_IND] [nvarchar] (1) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [COMP_ICD] [nvarchar] (1) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [COMP_SECC] [nvarchar] (3) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [COMP_SDOFF] [float] NULL ,
>>        [COMP_FTYPE] [nvarchar] (1) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [COMP_AF_ID] [nvarchar] (3) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [DETAIL_IND] [bit] NOT NULL ,
>>        [RTNDOCTYPE] [nvarchar] (1) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [COMP_PTLNK] [nvarchar] (7) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [COMP_PTYPE] [nvarchar] (2) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [RETRY_AMOUNT] [float] NULL ,
>>        [RATE_ACH] [float] NULL ,
>>        [RATE_ACHPercent] [float] NULL ,
>>        [RATE_BAT] [float] NULL ,
>>        [RATE_CHK] [float] NULL ,
>>        [RATE_CKV] [float] NULL ,
>>        [RATE_COL] [float] NULL ,
>>        [RATE_CRD] [float] NULL ,
>>        [RATE_ENT] [float] NULL ,
>>        [RATE_FIX] [float] NULL ,
>>        [RATE_FND] [float] NULL ,
>>        [RATE_MIN] [float] NULL ,
>>        [RATE_RTN] [float] NULL ,
>>        [RATE_WIR] [float] NULL ,
>>        [FEE_PMTIND] [nvarchar] (1) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [RATE_SDATE] [nvarchar] (10) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [PROC_CTRLS] [nvarchar] (10) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [INDICATORS] [nvarchar] (36) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [COMP_BILL] [nvarchar] (3) COLLATE
>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>        [RESERVE] [float] NULL
>>) ON [PRIMARY]
>>GO

>.

 
 
 

Complicated SP - DISTINCT issue

Post by Steve Kas » Sat, 27 Dec 2003 18:04:35


If DISTINCT reduces the result set from 9064 to 9003, then you have 61
duplicates in your non-DISTINCT result set.  If you want to update 9003
DISTINCT rows, but you do *not* want to update the 61 additional rows
that appear without DISTINCT, there is a problem, because those 61
additional rows have no column values that distinguish them from the
identical row among the 9003 you do want to update.  It would help to
know what you want to do.

SK

Gerard wrote:
>I understand that.  That is why you cannot update a table
>that used the DISTINCT keyword to return the recordset,
>the DBE doesn't know what exactly to update.  That is why
>I am trying to not use it.  The point I am trying to make
>is how to get DISTINCT rows without using the DISTINCT
>keyword, there must be an algorithm or an alternate way of
>doing things.  With DISTINCT I get 9003 rows, which is
>what I want.  Without DISTINCT, I get 9064 rows, 61 rows
>too many.  But I NEED to be able to update this recordset
>after a change, hense my need to not use DISTINCT.  Thanks
>again,
>                      Gerard

>>-----Original Message-----
>>Gerard,

>> What does it mean to update only distinct rows?  If you

>have a table

>>with duplicate rows and only want to update one of the

>duplicates, how

>>can the query processor know which information to

>update?  For an

>>update query to be legal, it must specify unambiguously

>what row(s) to

>>update.

>> Can you give an example of what you want the before and

>after pictures

>>to look like?

>>SK

>>Gerard wrote:

>>>I have a need for the DISTINCT keyword(Of course), but

>you

>>>cannot update a recordset that was returned using
>>>DISTINCT.  So I thought I came up with a great solution.
>>>Using the EXISTS keyword, I have a subquery identical to
>>>the main query, except for the DISTINCT keyword is used

>in

>>>an effort to try and filter the main query, but my

>result

>>>set is exactly the same as if I had simply taken the
>>>DISTINCT keyword off of the main query.  Below is the
>>>original query, and the modified query that is trying to
>>>filter out the records that do not appear in the sub-
>>>query.  I absolutely need DISTINCT rows but also need to
>>>be able to update them.  Does anyone see what is wrong
>>>with my fix or have an alternate way of getting DISTINCT
>>>rows without the DISTINCT keyword.  I have included the
>>>SP's, and DDL for both tables.  Thanks in advance, and
>>>hope everyone had a happy holiday.
>>>                        Gerard

>>>-------------------------------------------
>>>----Original SP w/DISTINCT-----------------
>>>-------------------------------------------
>>>CREATE PROCEDURE spBasRunACHBatch
>>>AS
>>>SELECT DISTINCT tblACH_Batch.AB_ABA_NUM,
>>>tblACH_Batch.AB_ACCTNUM,
>>>tblACH_Batch.AB_AMOUNT, tblACH_Batch.AB_TRACK,
>>>tblACH_Batch.AB_NAME, tblACH_Batch.AB_C_S_IND,
>>>tblACH_Batch.AB_BEG_DTE, tblACH_Batch.AB_END_DTE,
>>>tblACH_Batch.AB_SCHED, tblACH_Batch.AB_COMP_ID,
>>>tblACH_Batch.AB_ONCE_SW, tblACH_Batch.AB_RETRY,
>>>tblACH_Batch.INDICATORS,
>>>tblACH_Company.RETRY_AMOUNT, tblACH_Company.[KEY]

>>>FROM tblACH_Batch LEFT JOIN tblACH_Company ON

>>>tblACH_Batch.AB_COMP_ID = tblACH_Company.[KEY]
>>>WHERE (SUBSTRING(tblACH_BATCH.INDICATORS,1,1) Like ' '

>OR

>>>tblACH_Batch.AB_ONCE_SW <> 0)
>>>ORDER BY tblACH_Batch.AB_COMP_ID,tblACH_Batch.AB_NAME
>>>GO

>>>--------------------------------------------------
>>>----Modified SP, Trying to get DISTINCT rows
>>>----Without using DISTINCT keyword in main query--
>>>--------------------------------------------------
>>>CREATE PROCEDURE spBasRunACHBatch
>>>AS
>>>SELECT tblACH_Batch.AB_ABA_NUM, tblACH_Batch.AB_ACCTNUM,
>>>tblACH_Batch.AB_AMOUNT, tblACH_Batch.AB_TRACK,
>>>tblACH_Batch.AB_NAME, tblACH_Batch.AB_C_S_IND,
>>>tblACH_Batch.AB_BEG_DTE, tblACH_Batch.AB_END_DTE,
>>>tblACH_Batch.AB_SCHED, tblACH_Batch.AB_COMP_ID,
>>>tblACH_Batch.AB_ONCE_SW, tblACH_Batch.AB_RETRY,
>>>tblACH_Batch.INDICATORS,
>>>tblACH_Company.RETRY_AMOUNT, tblACH_Company.[KEY]

>>>FROM tblACH_Batch LEFT JOIN tblACH_Company ON

>>>tblACH_Batch.AB_COMP_ID = tblACH_Company.[KEY]
>>>WHERE (SUBSTRING(tblACH_BATCH.INDICATORS,1,1) Like ' '

>OR

>>>tblACH_Batch.AB_ONCE_SW <> 0) AND EXISTS
>>>    (SELECT DISTINCT tblACH_Batch.AB_ABA_NUM,
>>>tblACH_Batch.AB_ACCTNUM,
>>>    tblACH_Batch.AB_AMOUNT, tblACH_Batch.AB_TRACK,
>>>    tblACH_Batch.AB_NAME, tblACH_Batch.AB_C_S_IND,
>>>    tblACH_Batch.AB_BEG_DTE, tblACH_Batch.AB_END_DTE,
>>>    tblACH_Batch.AB_SCHED, tblACH_Batch.AB_COMP_ID,
>>>    tblACH_Batch.AB_ONCE_SW, tblACH_Batch.AB_RETRY,
>>>    tblACH_Batch.INDICATORS,
>>>    tblACH_Company.RETRY_AMOUNT, tblACH_Company.[KEY]
>>>    FROM tblACH_Batch LEFT JOIN tblACH_Company ON
>>>tblACH_Batch.AB_COMP_ID = tblACH_Company.[KEY]
>>>    WHERE (SUBSTRING(tblACH_BATCH.INDICATORS,1,1)
>>>Like ' ' OR tblACH_Batch.AB_ONCE_SW <> 0))
>>>ORDER BY tblACH_Batch.AB_COMP_ID,tblACH_Batch.AB_NAME
>>>GO

>>>---------------------------------------------------
>>>----tblACH_Batch DDL-------------------------------
>>>---------------------------------------------------
>>>if exists (select * from dbo.sysobjects where id =
>>>object_id(N'[dbo].[tblACH_Batch]') and OBJECTPROPERTY

>(id,

>>>N'IsUserTable') = 1)
>>>drop table [dbo].[tblACH_Batch]
>>>GO

>>>CREATE TABLE [dbo].[tblACH_Batch] (
>>>    [ID] [int] IDENTITY (1, 1) NOT NULL ,
>>>    [AB_ABA_NUM] [nvarchar] (9) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [AB_ACCTNUM] [nvarchar] (17) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [AB_AMOUNT] [float] NULL ,
>>>    [AB_TRACK] [nvarchar] (8) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [AB_NAME] [nvarchar] (22) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [AB_C_S_IND] [nvarchar] (1) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [AB_BEG_DTE] [nvarchar] (10) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [AB_END_DTE] [nvarchar] (10) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [AB_REMARKS] [ntext] COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [AB_SCHED] [nvarchar] (4) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [AB_COMP_ID] [nvarchar] (3) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [AB_ONCE_SW] [bit] NULL ,
>>>    [INDICATORS] [nvarchar] (36) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [AB_PERIODS] [float] NULL ,
>>>    [AB_RETRY] [bit] NULL
>>>) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>>>GO

>>>------------------------------------------------------
>>>----tblACH_Company DDL--------------------------------
>>>------------------------------------------------------
>>>if exists (select * from dbo.sysobjects where id =
>>>object_id(N'[dbo].[tblACH_Company]') and OBJECTPROPERTY
>>>(id, N'IsUserTable') = 1)
>>>drop table [dbo].[tblACH_Company]
>>>GO

>>>CREATE TABLE [dbo].[tblACH_Company] (
>>>    [KEY] [nvarchar] (3) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>>>    [COMP_NAME] [nvarchar] (16) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [COMP_FEDID] [nvarchar] (9) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [COMP_DESC] [nvarchar] (10) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [COMP_ABA] [nvarchar] (9) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [COMP_DDATA] [nvarchar] (20) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [COMP_ACC] [nvarchar] (17) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [CH_SA_IND] [nvarchar] (1) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [DE_CR_IND] [nvarchar] (1) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [COMP_ICD] [nvarchar] (1) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [COMP_SECC] [nvarchar] (3) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [COMP_SDOFF] [float] NULL ,
>>>    [COMP_FTYPE] [nvarchar] (1) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [COMP_AF_ID] [nvarchar] (3) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [DETAIL_IND] [bit] NOT NULL ,
>>>    [RTNDOCTYPE] [nvarchar] (1) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [COMP_PTLNK] [nvarchar] (7) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [COMP_PTYPE] [nvarchar] (2) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [RETRY_AMOUNT] [float] NULL ,
>>>    [RATE_ACH] [float] NULL ,
>>>    [RATE_ACHPercent] [float] NULL ,
>>>    [RATE_BAT] [float] NULL ,
>>>    [RATE_CHK] [float] NULL ,
>>>    [RATE_CKV] [float] NULL ,
>>>    [RATE_COL] [float] NULL ,
>>>    [RATE_CRD] [float] NULL ,
>>>    [RATE_ENT] [float] NULL ,
>>>    [RATE_FIX] [float] NULL ,
>>>    [RATE_FND] [float] NULL ,
>>>    [RATE_MIN] [float] NULL ,
>>>    [RATE_RTN] [float] NULL ,
>>>    [RATE_WIR] [float] NULL ,
>>>    [FEE_PMTIND] [nvarchar] (1) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [RATE_SDATE] [nvarchar] (10) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [PROC_CTRLS] [nvarchar] (10) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [INDICATORS] [nvarchar] (36) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [COMP_BILL] [nvarchar] (3) COLLATE
>>>SQL_Latin1_General_CP1_CI_AS NULL ,
>>>    [RESERVE] [float] NULL
>>>) ON [PRIMARY]
>>>GO

>>.

 
 
 

Complicated SP - DISTINCT issue

Post by Gerar » Sat, 27 Dec 2003 18:08:15


I am using ADO, sorry.  That sounds like a viable
alternative, but can you give a quick example, I have
never done an update from a SP.  Thanks alot in advance,
            Gerard

>First, you don't say what client-side tool you are using.
If you are
>using ADO.Net, defined an Update procedure which updates
the rows
>you need to update.

>If you are using ADO, ADO is trying from behind the
scenes to figure
>out which rows you want to update, but is obviously not
very successful.
>Rather than using the .Update method in ADO, define a
stored
>procedure which performs the update, and call this
procedure.

>--

>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/book
s.asp
>.

 
 
 

Complicated SP - DISTINCT issue

Post by Gerar » Sat, 27 Dec 2003 18:43:50


Steve,
    We are a financial processor, so what I am trying to
do is a bit complicated, but, in short...
I am need the results to be distinct because this is
system level proccessing, very processor intensive, and I
only want each Company ID processed once even if there are
multiple entrys for that ID.  The recordset is processed
until EOF, for each record where the .ab_once_sw = 1,
certain fields are changed, and then the recordset
is .update, or .movenext is executed depending on the
conditions.  Both methods return the same error,
"insufficent key column info for updating".  This worked
when our company was smaller and used Access, but we have
grown and I migrated to SQL Server with a VB and ADO front
end, and ADO is more stringent on primary keys and what-
not. I NEED the distinct rows, and I can get them by
simply processing the whole table in VB, but that sucks,
and is not processor friendly.  Thanks again Steve, helps
is allways appreciated.
                  Gerard

Quote:>-----Original Message-----
>If DISTINCT reduces the result set from 9064 to 9003,
then you have 61
>duplicates in your non-DISTINCT result set.  If you want
to update 9003
>DISTINCT rows, but you do *not* want to update the 61
additional rows
>that appear without DISTINCT, there is a problem, because
those 61
>additional rows have no column values that distinguish
them from the
>identical row among the 9003 you do want to update.  It
would help to
>know what you want to do.

>SK

 
 
 

Complicated SP - DISTINCT issue

Post by freaky frida » Sat, 27 Dec 2003 18:49:02


Quote:> not shown in the DDL.  I thought UPDATE table is only used
> on a singular table, I have a recordset containing two
> different tables.

...then you need two separate update statements...
 
 
 

Complicated SP - DISTINCT issue

Post by Steve Kas » Sat, 27 Dec 2003 20:43:08


Sorry Gerard,  It still sounds like you want to update some rows but you
can't precisely specify which rows you want to update.  It's like you have

ID Data
-- ----
11 1000 -- need to update this row
11 1000 -- do not want to update this row
11 2000 -- need to update
12 2000 -- need to update
13 3000 -- need to update this row
13 3000 -- do not want to update this row

There is no condition that can distinguish the rows needing
to be updated from the rows not needing to be updated.  While it
looks like there is because I've typed them in a column (I want to update
the first of every distinct row?), the property "first" is not something
that
can be evaluated in the database, since it cannot be determined by the
values in the row.

If you want an update that will change this to

ID Data
-- ----
11 1001 -- updated
11 1000 -- was identical to previous, but not updated
11 2001 -- updated
12 2001 -- updated
13 3001 -- updated
13 3000 -- not updated, even though it was identical to previous row

you can't do it with a single update query.  An update query can only
update rows that satisfy a WHERE condition (or an equivalent condition
specified as a join), since an update query will update a row based on
whether it satisfies the condition or not.  There is no condition
satisfied by one copy of 11 1000 that is not satisfied by a different
row that is identical.

You can do this sort of thing with single-row processing.  For example,
if there is a column that indicates whether or not the update has taken
place, you can do this

set rowcount 1
while 1=1 begin
  update T set
    ...
  where ...
  and row_is_updated_column = 0
  and not exists (
    select * from T T2
    where T1.column1 = T2.column1
    and <other columns are equal except for:>
    and T2.row_is_updated_column = 1
  )

end
set rowcount 0

This is essentially what you are doing in VB, probably.

Another alternative is to add a column so that previously identical rows
are distinguished, and update only the rows with the smallest value of
the new column:

alter table has_duplicates add pk int identity(1,1)
go
update has_duplicates set
  ...
where <condition for update>
and pk = (
  select min(pk) from has_duplicates D
  where D.column1 = has_duplicates.column1
  and ... same for other columns
)

If you can't write a condition that is TRUE for the rows you want to
update and FALSE for the rows you do not want to update, you can't write
an update query to update only the rows you want to.  It's not enough
that you can write a query that returns a record set that appears to be
a list of rows to update - your DISTINCT query does that, but there is
no way to match each row in the result of the DISTINCT query with a
specific row in the original table.

SK


>Steve,
>    We are a financial processor, so what I am trying to
>do is a bit complicated, but, in short...
>I am need the results to be distinct because this is
>system level proccessing, very processor intensive, and I
>only want each Company ID processed once even if there are
>multiple entrys for that ID.  The recordset is processed
>until EOF, for each record where the .ab_once_sw = 1,
>certain fields are changed, and then the recordset
>is .update, or .movenext is executed depending on the
>conditions.  Both methods return the same error,
>"insufficent key column info for updating".  This worked
>when our company was smaller and used Access, but we have
>grown and I migrated to SQL Server with a VB and ADO front
>end, and ADO is more stringent on primary keys and what-
>not. I NEED the distinct rows, and I can get them by
>simply processing the whole table in VB, but that sucks,
>and is not processor friendly.  Thanks again Steve, helps
>is allways appreciated.
>                  Gerard

>>-----Original Message-----
>>If DISTINCT reduces the result set from 9064 to 9003,

>then you have 61

>>duplicates in your non-DISTINCT result set.  If you want

>to update 9003

>>DISTINCT rows, but you do *not* want to update the 61

>additional rows

>>that appear without DISTINCT, there is a problem, because

>those 61

>>additional rows have no column values that distinguish

>them from the

>>identical row among the 9003 you do want to update.  It

>would help to

>>know what you want to do.

>>SK

 
 
 

Complicated SP - DISTINCT issue

Post by Erland Sommarsko » Sun, 28 Dec 2003 01:54:05



> I am using ADO, sorry.  That sounds like a viable
> alternative, but can you give a quick example, I have
> never done an update from a SP.  Thanks alot in advance,




   UPDATE tbl



But your procedure is likely to be more complicated. Or rather, when you
set off to write your procedure you will be facing the same problem as
ADO is facing: which row is that you really want to update? It seems
quite clear to me from your discussion with Steve Kass that you don't
have full understanding of your business requirements. But the good thing
with writing the code yourself, rather than trusting ADO to something
magically for you, is where you have problems in defining on which data
you need to operate.

--

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

1. sum distinct records in complicated decode query

I have inherited the query below which works very well except in this
case where one of the
columns (tar_status_status) occasionally contains a duplicate 'job end'
code and this results in a inaccurate tally. Is there a work around for
this?

I know I can get an accurate count by 'select count(distinct
tar_status_status)' but I would really like to retain this format.

Any help appreciated.
John

select '5' "ORDER",
decode(t.tar_status_status,'02','Fixed02  ','') "Status",
sum(decode(a.fault_type,'S',1,0)) S,
sum(decode(a.fault_type,'F',1,0)) F,
sum(decode(a.fault_type,'H',1,0)) H,
sum(decode(a.fault_type,'U',1,0)) U,
sum(decode(a.fault_type,'DUP',1,0)) Dup,
sum(decode(a.fault_type,'N',1,0)) N,
sum(decode(a.fault_type,'DOC',1,0)) Doc
from tar a,
     tar_status t
WHERE  a.tar = t.TAR_STATUS_TAR
and t.tar_status_mod_date between '1-JUL-99' AND '31-JUL-99'
and a.status_code = '02'
and t.TAR_STATUS_status = '02'
and a.assignee_mcode = 'abc'
group by t.tar_status_status
/

0 status        S          F          H          U          DUP      
N          DOC      
-               --------- ---------- ---------- ---------- ---------- ----------
----------
5 Fixed02       12          0          7         21         15        
21          0

2. connecting 2 databases using RAS

3. complicated sp?

4. Oracle to Informix

5. Help with complicated sp

6. How to Link External SQL Server Database Tables in multiple databases

7. Complicated SP has me stumped

8. informixSTAR

9. A complicated issue...

10. Repost: Help with complicated sp

11. Complicated stored proc and parameters issue

12. Analysis Services - distinct count issue

13. SQL SP - DISTINCT pukes on me