CodeCharge Studio
search Register Login  

Visual Web Reporting

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

YesSoftware Forums -> CodeCharge Studio -> PHP

 custom ranking mysql query not playing nice in CCS [solved]

Print topic Send  topic

Author Message
THX1138

Posts: 37
Posted: 09/29/2012, 5:38 PM

CCS 4.3.0.7676 , MySQL, PhP


Hello all,
Having a relapse of the stupids out here...i have a simple mysql query
to select and rank values prior to display in a grid.
the mysql works on the database, but i get grumpy syntax error messages
from codecharge generated pages when i try to use the updated sql

Here is a simplified version of the query, already codecharge spits at this one

set @rownum:=0;
select @rownum := @rownum+1 as rank, location, datum from snf_data


CCS pages complain of a syntax error at line 1 near ; select,
it looks like i cant get the syntax accepted/acceptable to CCS to allow me
to set the variable in mysql



Been trying changing ' " ` s and commas and semicolons, but to no avail

The ccs generated code shows it is adding the preselect conditions but then blammo.
//Open Method @32-D1298D25
function Open()
{
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "BeforeBuildSelect", $this->Parent);
$this->SQL = "set @rownum:=0; SELECT meta, location, yrstart, datum \n" .
"FROM snf_data ";
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "BeforeExecuteSelect", $this->Parent);
if ($this->CountSQL)
$this->RecordsCount = CCGetDBValue(CCBuildSQL($this->CountSQL, $this->Where, ""), $this);
else
$this->RecordsCount = "CCS not counted";
$this->query($this->OptimizeSQL(CCBuildSQL($this->SQL, $this->Where, $this->Order)));
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "AfterExecuteSelect", $this->Parent);
}
//End Open Method



And no, views arent possible b/c of the @variables and subqueries, and stored procedures seem to be forbidden on host server




Here is the full sql query that works with mysql, for those who like more ..

set @rownum=0;
set @GDPyear=(select max(yrstart) from snf_data where meta = 'GDP');
select rank, yrstart, location, datum, bilyons, meta
from (
SELECT @rownum := @rownum +1 as rank, yrstart, location, datum, format((datum/1000),1) as bilyons, meta
FROM snf_data
where meta = 'GDPabbr' and yrstart = @GDPyear
ORDER BY datum DESC
) as rankedGDP
View profile  Send private message
THX1138

Posts: 37
Posted: 09/29/2012, 6:46 PM

SOLVED IT

results table query now starts with select and is happy

moved the mysql variable definition to the before select event for the results table,
custom code (well, i retasked a Dlookup as i can never get my db connection right)

  
    global $DBcbdbconn;  
    $Page = CCGetParentPage($sender);      
    $db = new clsDBcbdbconn();  
    $db->query("SET @rownum = 0");  
    $db->close();  


Put the subselect of year of interest into the table lookup select statement, so custom sql
for select is
  
SELECT @rownum:=@rownum+1 as rank,location, yrstart, datum, format((datum/1000),1) as bilyons   
FROM snf_data   
WHERE meta= 'GDPabbr' and yrstart = (SELECT max(yrstart) from snf_data where meta='GDP')  



View profile  Send private message

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.

MS Access to Web

Convert MS Access to Web.
Join thousands of Web developers who build Web applications with minimal coding.

CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


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