Don Safar
|
Posted: 02/06/2004, 7:16 AM |
|
I am using PHP/MySQL. I have a custom sql statement as follows: "select date_format(dateposted,'%Y-%m-%d'), count(distinct remote_host)as cnt, sum(hit_total) as cnt2 from cool_referer group by 1". this is used to populate a grid with a navigator control. CCS generates sql to count the records as follows: "select count(*) fro cool-referer group by 1". This of course generates an error. This is one of 10-12 grids I have on a statistics page. I have to manually go in and remove the group by statements in the count sql from the generated code. Is there a way to do this so ccs doesn't generate the bad sql.
|
|
|
peterr
Posts: 5971
|
Posted: 02/06/2004, 2:33 PM |
|
There are situations where CCS doesn't know what should be the correct Count that will work with your query. This is the reason that we added the "CountSQL" property, so that you can use your own count method.
Please refer to: http://docs.codecharge.com/studio/html/Components/RTPro...P/CountSQL.html
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
Don Safar
|
Posted: 02/07/2004, 11:07 AM |
|
What event should I use to modify the CountSQL property? Should I be aware
of any implications of modifying this (i.e. will if affect the navigation
control)?
"peterr" <peterr@forum.codecharge> wrote in message
news:540241626ecee3@news.codecharge.com...
> There are situations where CCS doesn't know what should be the correct
Count that will work with your query. This is the reason that we added the
"CountSQL" property, so that you can use your own count method.
> Please refer to:
> http://docs.codecharge.com/studio/html/Components/RTPro...P/CountSQL.html
>
> _________________
> Peter R.
> YesSoftware Support Representative
> http://support.codecharge.com
> Warning: I use Google and CCS Docs/Examples in most of my answers
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
Nicole
Posts: 586
|
Posted: 02/09/2004, 5:27 AM |
|
Don,
Use Before Build Select event to change countSQL. The code is similar to modifying WHERE clause as described at http://docs.codecharge.com/studio/html/ProgrammingTechn...HEREClause.html
But in your case you’d better overwrite generated countSQL totally.
_________________
Regards,
Nicole |
|
|
Mauricio
|
Posted: 02/25/2004, 8:52 AM |
|
Well I don´t know if it is the right way to solve it.
I pun and after execute select event to modify the RecordsCount attribute leaving the code untouch and copyng and pasting the variable SqlCount replacing the values as needed to access the grid object, and executing custom sql to set the value with sql variables.
here is the example :
//Custom Code @89-31BCAA12
// -------------------------
global $llamados_cat_del01;
//global $DBpgjdf_asis;
// Write your own code here.
$db2 = new clsDBpgjdf_asis();
$misql_1 = "set @renglon=0";
$misql_0 = "SELECT @renglon:=@renglon+1 as renglon FROM llamados USE INDEX (ind_delito_fecha_col_hech) INNER JOIN cat_del01 USE INDEX (ind_clave_delito1) ON llamados.DELITO01 = cat_del01.CLAVE " .
"WHERE (llamados.FECH_ING between '" . $llamados_cat_del01->ds->SQLValue($llamados_cat_del01->ds->wp->GetDBValue("2"), ccsDate) . "' AND '" . $llamados_cat_del01->ds->SQLValue($llamados_cat_del01->ds->wp->GetDBValue("3"), ccsDate) . "') and DELITO1 LIKE '%" . $llamados_cat_del01->ds->SQLValue($llamados_cat_del01->ds->wp->GetDBValue("1"), ccsText) . "%' and llamados.col_hech like '%" . $llamados_cat_del01->ds->SQLValue($llamados_cat_del01->ds->wp->GetDBValue("4"), ccsText) . "%' " .
"GROUP BY llamados.col_hech, cat_del01.CLAVE " .
"";
$misql = "SELECT @renglon:=@renglon+1 as renglon FROM llamados INNER JOIN cat_del01 ON llamados.delito01 = cat_del01.clave group by col_hech, DELITO1 ";
$misql_2 = "SELECT @renglon as renglon";
//echo $misql_0;
$db2->query($misql_1);
$db2->query($misql_0);
$db2->query($misql_2);
$resultado = $db2->next_record();
if ($resultado) {
$renglon = $db2->f("renglon");
//$resultado = $db->next_record();
}
//echo "<br>";
//echo "renglones: ".$renglon;
//unset($db);
//echo "<br>";
$llamados_cat_del01->ds->RecordsCount=$renglon;
//echo "RecordsCount: ".$llamados_cat_del01->ds->RecordsCount;
// -------------------------
//End Custom Code
I hope it will be helpful.
Regards
Mauricio
|
|
|
|