Announcement

Collapse
No announcement yet.

SQL mangling

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

  • SQL mangling

    I have a grid app. I want only the chart from a summary (the only documented way to get a chart).

    Input SQL:
    SELECT
    ROUND(AVG(Order_Total), 2) AS Avg_Order,
    Customer_ID
    FROM
    t_orders
    WHERE Order_Date > (NOW() - INTERVAL 6 MONTH)
    GROUP BY Customer_ID
    ORDER BY Avg_Order DESC
    LIMIT 20
    When I execute the app, the SQL has been changed to:
    select
    count(*),
    avg(ROUND(AVG(Oder_Total),2)) as avg_order
    from t_orders
    where Order_Date > (NOW() - INTERVAL 6 MONTH)
    group by Customer_ID
    The field "Customer_ID" (and LIMIT 20, and a random avg() thrown in) have been dropped, making the SELECT fail due to an invalid GROUP BY.

  • #2
    Bug #2: changing the SQL by editing the Grid SQL doesn't change the output SQL.

    Example, for when you decide that SC can't handle SQL:

    Change "ROUND(AVG(Order_Total), 2) AS Avg_Order" to "AVG(Order_Total) AS Avg_Order" (eliminating ROUND())

    Build/run.

    The output still errors with .....ROUND(AVG(Order_Total), 2) AS Avg_Order......


    Questions:
    Can the SQL for a Grid application contain actual SQL? Which standard?
    If I need to change the SQL, is delete-app/create-new the only way to do that?

    Comment


    • #3
      If you want to create charts then you need to group-by from the menu as SC will generate these itself. So in the SQL section you can only enter the basic select without the group-by. Then scroll down to find the group-by section and apply your rules there.
      Albert Drent
      aducom software netherlands
      scriptcase partner, reseller, support and (turn-key) development
      www.scriptcase.eu / www.scriptcase.nl

      Comment


      • #4
        Thank you, Albert!

        SC seems to ignore any SQL that is not SELECT, FROM, WHERE or ORDER BY.

        That simplifies coding, but severely limits what can be done. Imagine owning a Ferrari with no tires.

        Is there any way to limit the result set for only the charts? SC tries to use 30 Customers (ignoring SQL LIMIT) and the charts become worthless noise. Does anyone know where that "30" is defined?

        Comment


        • #5
          Originally posted by Giblet535 View Post
          Thank you, Albert!

          SC seems to ignore any SQL that is not SELECT, FROM, WHERE or ORDER BY.

          That simplifies coding, but severely limits what can be done. Imagine owning a Ferrari with no tires.

          Is there any way to limit the result set for only the charts? SC tries to use 30 Customers (ignoring SQL LIMIT) and the charts become worthless noise. Does anyone know where that "30" is defined?
          Who's wanting a Ferrari (lol).

          The best approach might be to create a temporary table or view.
          Albert Drent
          aducom software netherlands
          scriptcase partner, reseller, support and (turn-key) development
          www.scriptcase.eu / www.scriptcase.nl

          Comment


          • #6
            Originally posted by aducom View Post
            Who's wanting a Ferrari (lol).

            The best approach might be to create a temporary table or view.
            A Ferrari with tires is the same as a Ferrari without tires, but your horse doesn't get as tired towing it to the shop.

            Hopefully, NetMake will spend an hour or two this year adding technical documentation, reading up on why checkboxes can represent boolean values on webs created w/ SC just like they do on the Buy Now web and every other website, why treeviews use DOWN-arrows to indicate "expanded state", etc.

            Comment


            • #7
              Hello,

              Issue reported to our bugs team.

              A palliative measure could be create a view with that SQL statement.

              regards,
              Bernhard Bernsmann

              Comment

              Working...
              X