Announcement

Collapse
No announcement yet.

Grid SQL Settings

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Grid SQL Settings

    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. sqlsettings.png

    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

  • #2
    Originally posted by vcgonzales View Post
    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.
    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] ...
    Best regards: - Reinhard -

    I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

    Comment


    • #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.

      Comment

      Working...
      X