taha
Posts: 8
|
| Posted: 03/23/2007, 11:54 AM |
|
Introduction to topic: Recently I required only Distinct records to show up in a query and I implemented that by replacing the keyword Select with Select Distinct in the BeforeExecuteSelect Event.
However, setting the CountSQL and therefor the RecordCount was a challenge until I came across the forum post by marcwolf (screenname...thanks marcwolf!) which gave me the basic idea below but I have added a few finishing touches to it which made it work out.
In the AfterExecuteSelect Event add the following code:
Dim iloop
Dim rs
Dim mvSQL
Dim mvWhere
mvWhere = iif(Len(Trim(CourseAudience_CourseCamp1.DataSource.Where))=0, "", " Where ")
mvSQL = "SELECT DISTINCT Courses.CourseID "&_
"FROM (((((Courses LEFT JOIN CourseAudience ON "&_
"Courses.CourseID = CourseAudience.CourseID) LEFT JOIN CourseCampaign ON "&_
"Courses.CourseID = CourseCampaign.CourseID) LEFT JOIN CourseInstructor ON "&_
"Courses.CourseID = CourseInstructor.CourseID) LEFT JOIN LUDuration ON "&_
"Courses.DurationID = LUDuration.DurationID) LEFT JOIN CourseRegistrant ON "&_
"Courses.CourseID = CourseRegistrant.CourseID) LEFT JOIN LULocation ON "&_
"Courses.LocationID = LULocation.LocationID "&mvWhere&CourseAudience_CourseCamp1.DataSource.Where
iLoop=0
set rs = DBeCR.Execute(mvSQL)
rs.MoveFirst
do while not rs.eof
iloop = iloop + 1
rs.movenext
loop
CourseAudience_CourseCamp1.DataSource.Recordset.RecordCount = iloop
set rs = nothing
The only difference between marcwolf's post and the above cod3e is that the 'Select ' part of the query has to be spelt out instead of using the
grid.datasource.sql
variable because that variable still has a lot of CodeCharge specific syntax instead of plain ASP.
Also the Where keyword needs to be conditionally placeed only if the where clause has any content.
Hope this helps..enjoy
Regards,
Taha
_________________
Taha Shipchandler
Ph: 281 398 2111
http://datamatix.com
taha.shipchandler@datamatix.com |
 |
 |
DonB
|
| Posted: 05/06/2007, 12:20 PM |
|
You don't say what what the challenge was, but it ought to be much simpler
than this. I think you could have altered the 'SELECT' clause to just:
SELECT COUNT(DISTINCT CourseID)
and avoided the loop through the result set because this returns the number
of records as the query result.
--
DonB
http://www.gotodon.com/ccbth
"taha" <taha@forum.codecharge> wrote in message
news:2546043093415e7@news.codecharge.com...
> Introduction to topic: Recently I required only Distinct records to show
up in a
> query and I implemented that by replacing the keyword Select with Select
> Distinct in the BeforeExecuteSelect Event.
>
> However, setting the CountSQL and therefor the RecordCount was a challenge
> until I came across the forum post by marcwolf (screenname...thanks
marcwolf!)
> which gave me the basic idea below but I have added a few finishing
touches to
> it which made it work out.
>
> In the AfterExecuteSelect Event add the following code:
>
> Dim iloop
> Dim rs
> Dim mvSQL
> Dim mvWhere
> mvWhere = iif(Len(Trim(CourseAudience_CourseCamp1.DataSource.Where))=0,
"", "
> Where ")
> mvSQL = "SELECT DISTINCT Courses.CourseID "&_
> "FROM (((((Courses LEFT JOIN CourseAudience ON "&_
> "Courses.CourseID = CourseAudience.CourseID) LEFT JOIN
CourseCampaign
> ON "&_
> "Courses.CourseID = CourseCampaign.CourseID) LEFT JOIN
CourseInstructor
> ON "&_
> "Courses.CourseID = CourseInstructor.CourseID) LEFT JOIN
LUDuration ON
> "&_
> "Courses.DurationID = LUDuration.DurationID) LEFT JOIN
CourseRegistrant
> ON "&_
> "Courses.CourseID = CourseRegistrant.CourseID) LEFT JOIN
LULocation ON
> "&_
> "Courses.LocationID = LULocation.LocationID
> "&mvWhere&CourseAudience_CourseCamp1.DataSource.Where
> iLoop=0
> set rs = DBeCR.Execute(mvSQL)
> rs.MoveFirst
> do while not rs.eof
> iloop = iloop + 1
> rs.movenext
> loop
> CourseAudience_CourseCamp1.DataSource.Recordset.RecordCount = iloop
> set rs = nothing
>
> The only difference between marcwolf's post and the above cod3e is that
the
> 'Select ' part of the query has to be spelt out instead of using the
>
> grid.datasource.sql
>
> variable because that variable still has a lot of CodeCharge specific
syntax
> instead of plain ASP.
>
> Also the Where keyword needs to be conditionally placeed only if the where
> clause has any content.
>
> Hope this helps..enjoy
>
> Regards,
>
> Taha
> _________________
> Taha Shipchandler
> Ph: 281 398 2111
> http://datamatix.com
>taha.shipchandler@datamatix.com
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Frosty555
Posts: 7
|
| Posted: 05/07/2007, 8:51 AM |
|
Agreed with above.
Assuming you know the name of the primary key (or some other unique field) of your query, it isn't necessary to re-execute the query and step through all the records again. I could imagine this is terribly slow for very large recordsets.
Easier is simply to alter the SELECT COUNT(*) query to return the right value!
I was writing a grid in PHP that needed to have a DISTINCT in the select query, and I put the following code into my BeforeExecuteSelect() event of the grid:
(my grid was named grdActiveProject, and the primary key of my table was named "projectno")
// Make the SELECT query distinct
$SQL = $grdActiveProject->DataSource->SQL;
$SQL = str_replace("SELECT", "SELECT DISTINCT", $SQL, 1);
// When the select query is distinct, the query to get the count of the records must also
// use the same distinct records, otherwise codecharge thinks too many records are present.
$CountSQL = $grdActiveProject->DataSource->CountSQL;
$CountSQL = str_replace("SELECT COUNT(*)", "SELECT COUNT(DISTINCT projectno)", $CountSQL, 1);
$grdActiveProject->DataSource->SQL = $SQL;
$grdActiveProject->DataSource->CountSQL = $CountSQL;
Seems to work rather well. 
|
|
|
|
|
