Grid SQL Settings

Applications Grid / Reports
,
#1

Hi.

I tried searching for “SQL” problems, but couldn’t find them in the Grid forum.

My DB is MySQL.

Below is the Select query I have on my Grid Application. [mDateTo] is a global variable.

My problem is that on WHERE clause, [mDateTo] recognizes the date variable that is being passed. But on the FIELDS section like on the line ‘[mDateTo]’ AS SelDate, the value of the global variable is not being passed to the SQL.

This made the case statement to always return the ELSE part.

Thank you.

SELECT

tblcusttrans.CustId,
tblcusttrans.Amt,
tblcusttrans.AmtCurr,
tblcusttrans.GRP,
tblcusttranstotal.AppliedAmt,
tblcusttranstotal.AppliedAmtC,
tblcusttrans.TrnTxt,
DATEDIFF(’[mDateTo]’, tblcusttrans.DueDt) AS AgeDays,
‘[mDateTo]’ AS SelDate,

CASE
WHEN DATEDIFF(’[mDateTo]’, DueDt) > 180 THEN ‘Over180Days’
WHEN DATEDIFF(’[mDateTo]’, DueDt) > 90 THEN ‘180Days’
WHEN DATEDIFF(’[mDateTo]’, DueDt) > 60 THEN ’ 90Days’
WHEN DATEDIFF(’[mDateTo]’, DueDt) > 30 THEN ’ 60Days’
WHEN DATEDIFF(’[mDateTo]’, DueDt) > 0 THEN ’ 30Days’
ELSE ’ CURRENT’
END AS Aging,
(Amt + IFNULL(AppliedAmt, 0)) AS AgeAmt

FROM
tblcusttrans LEFT OUTER JOIN tblcusttranstotal ON tblcusttrans.ID = tblcusttranstotal.ID
AND tblcusttranstotal.SelectedDate = ‘[mDateTo]’
WHERE tblcusttrans.DueDt <= ‘[mDateTo]’
AND NOT (Amt + IFNULL(AppliedAmt, 0)) = 0

sqlsettings.png

#2

That’s ok, because it can not work …

Simple solution: delete that in your SELECT and create a new field (Fields / New Field) “SelDate” and in event onRecord call simply {SelDate} = [mDateTo] …

#3

Thank you, I tried your solution, but can’t keep it running because the “case” part of the SQL kept on raising issues once scriptcase recreates it’s own version of the query. (count, group by, etc)

What worked for me is that the entire resultset, I saved everything on a new table as memory heap (for faster IO), then made the SQL to read from it.

I used the “SQL Preparation” to build the resultset I needed. I used stored procedure so I can check first if there is data already so I don’t regenerate the needed resultset everytime the grid runs.

Thank you.