|
 |
activex_data_objects thread: Batch Updates on Recordsets with aggregates
Message #1 by "Wayne Bloss" <g_byte@y...> on Mon, 11 Mar 2002 14:13:28 -0500
|
|
Hello all,
Does anyone know if you can do a batch update on a recordset that holds
aggregate data?
For instance say this was my query:
select S.ID, S.Client_ID, 'Client_Name'=LTRIM(RTRIM(C.Name)),
S.Title, S.Description, S.CallStartDate, S.CallEndDate,
S.SetAdv, S.SetAdvUnit, S.ZipOriented, S.Enabled,
'ApptStartDate'=Min(A.ApptStart),
'ApptEndDate'=Max(A.ApptStart),
'AvailabeApp'=Sum(CASE WHEN A.SetDate Is Not Null Then 1 Else 0 END),
'FilledApp'=Sum(CASE WHEN A.ID Is Not Null And A.SetDate Is Null Then 1 Else
0 END)
from tbl_ApptSchedules As S INNER JOIN Client As C
ON S.Client_ID = C.Client_ID
LEFT OUTER JOIN tbl_ApptSchedAppointments As A
ON S.ID = A.ScheduleID
Group By S.ID, S.Client_ID, C.Name, S.Title, S.Description, S.CallStartDate,
S.CallEndDate,
S.SetAdv, S.SetAdvUnit, S.ZipOriented, S.Enabled
I want to update just the S.Enabled field. Is this possible, or do I have
to get the aggregates in a seperate recordset?
Thanks,
Wayne Bloss
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Message #2 by "Guy Harwood" <guy@h...> on Tue, 12 Mar 2002 09:25:38 -0000
|
|
Hi Wayne,
If you run the query using some sort of graphical query tool, you will
probably find that the
Results set will be read only as the data is aggregated. Your query
results (judging by what
You've written) will return aggregated / summarized data and therefore
does not show individual
Row values that could be edited. These rules still apply to your ADO
recordset; how could
Your recordset possibly update aggregated data when it would have no
idea which rows in the
Source table to update? So I guess the answer to your question would be
no.
I hope this helps
Guy Harwood
Research & Development Consultant
www.hcs-ltd.co.uk
-----Original Message-----
From: ActiveX_Data_Objects digest
[mailto:activex_data_objects@p...]
Sent: 12 March 2002 00:00
To: activex_data_objects digest recipients
Subject: activex_data_objects digest: March 11, 2002
-----------------------------------------------
When replying to the digest, please quote only
relevant material, and edit the subject line to
reflect the message you are replying to.
-----------------------------------------------
The URL for this list is:
http://p2p.wrox.com/list.asp?list=activex_data_objects
ACTIVEX_DATA_OBJECTS Digest for Monday, March 11, 2002.
1. Batch Updates on Recordsets with aggregates
----------------------------------------------------------------------
Subject: Batch Updates on Recordsets with aggregates
From: "Wayne Bloss" <g_byte@y...>
Date: Mon, 11 Mar 2002 14:13:28 -0500
X-Message-Number: 1
Hello all,
Does anyone know if you can do a batch update on a recordset
that holds aggregate data?
For instance say this was my query:
select S.ID, S.Client_ID, 'Client_Name'=LTRIM(RTRIM(C.Name)),
S.Title, S.Description, S.CallStartDate, S.CallEndDate, S.SetAdv,
S.SetAdvUnit, S.ZipOriented, S.Enabled,
'ApptStartDate'=Min(A.ApptStart), 'ApptEndDate'=Max(A.ApptStart),
'AvailabeApp'=Sum(CASE WHEN A.SetDate Is Not Null Then 1 Else 0 END),
'FilledApp'=Sum(CASE WHEN A.ID Is Not Null And A.SetDate Is Null Then 1
Else 0 END)
from tbl_ApptSchedules As S INNER JOIN Client As C
ON S.Client_ID = C.Client_ID
LEFT OUTER JOIN tbl_ApptSchedAppointments As A
ON S.ID = A.ScheduleID
Group By S.ID, S.Client_ID, C.Name, S.Title, S.Description,
S.CallStartDate, S.CallEndDate, S.SetAdv, S.SetAdvUnit, S.ZipOriented,
S.Enabled
I want to update just the S.Enabled field. Is this possible, or
do I have to get the aggregates in a seperate recordset?
Thanks,
Wayne Bloss
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---
END OF DIGEST
$subst('Email.Unsub').
Message #3 by "Wayne Bloss" <g_byte@y...> on Tue, 12 Mar 2002 10:17:28 -0500
|
|
Thanks Guy,
I guess somehow I figured I could just edit the fields that aren't
aggregates, but now that you point out the example of the graphical query
tool like Access or Ent. Mgr. it makes sense.
Thanks a bunch,
Wayne Bloss
-----Original Message-----
From: Guy Harwood [mailto:guy@h...]
Sent: Tuesday, March 12, 2002 4:26 AM
To: ActiveX_Data_Objects
Subject: [activex_data_objects] RE: Batch Updates on Recordsets with
aggregates
Hi Wayne,
If you run the query using some sort of graphical query tool, you will
probably find that the
Results set will be read only as the data is aggregated. Your query
results (judging by what
You've written) will return aggregated / summarized data and therefore
does not show individual
Row values that could be edited. These rules still apply to your ADO
recordset; how could
Your recordset possibly update aggregated data when it would have no
idea which rows in the
Source table to update? So I guess the answer to your question would be
no.
I hope this helps
Guy Harwood
Research & Development Consultant
www.hcs-ltd.co.uk
-----Original Message-----
From: ActiveX_Data_Objects digest
[mailto:activex_data_objects@p...]
Sent: 12 March 2002 00:00
To: activex_data_objects digest recipients
Subject: activex_data_objects digest: March 11, 2002
-----------------------------------------------
When replying to the digest, please quote only
relevant material, and edit the subject line to
reflect the message you are replying to.
-----------------------------------------------
The URL for this list is:
http://p2p.wrox.com/list.asp?list=activex_data_objects
ACTIVEX_DATA_OBJECTS Digest for Monday, March 11, 2002.
1. Batch Updates on Recordsets with aggregates
----------------------------------------------------------------------
Subject: Batch Updates on Recordsets with aggregates
From: "Wayne Bloss" <g_byte@y...>
Date: Mon, 11 Mar 2002 14:13:28 -0500
X-Message-Number: 1
Hello all,
Does anyone know if you can do a batch update on a recordset
that holds aggregate data?
For instance say this was my query:
select S.ID, S.Client_ID, 'Client_Name'=LTRIM(RTRIM(C.Name)),
S.Title, S.Description, S.CallStartDate, S.CallEndDate, S.SetAdv,
S.SetAdvUnit, S.ZipOriented, S.Enabled,
'ApptStartDate'=Min(A.ApptStart), 'ApptEndDate'=Max(A.ApptStart),
'AvailabeApp'=Sum(CASE WHEN A.SetDate Is Not Null Then 1 Else 0 END),
'FilledApp'=Sum(CASE WHEN A.ID Is Not Null And A.SetDate Is Null Then 1
Else 0 END)
from tbl_ApptSchedules As S INNER JOIN Client As C
ON S.Client_ID = C.Client_ID
LEFT OUTER JOIN tbl_ApptSchedAppointments As A
ON S.ID = A.ScheduleID
Group By S.ID, S.Client_ID, C.Name, S.Title, S.Description,
S.CallStartDate, S.CallEndDate, S.SetAdv, S.SetAdvUnit, S.ZipOriented,
S.Enabled
I want to update just the S.Enabled field. Is this possible, or
do I have to get the aggregates in a seperate recordset?
Thanks,
Wayne Bloss
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---
END OF DIGEST
$subst('Email.Unsub').
$subst('Email.Unsub').
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
|
|
|
View Cart
