CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> PHP

 Custom SQL with Group By causing problem with grid

Print topic Send  topic

Author Message
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
View profile  Send private message
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
View profile  Send private message
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

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.