Announcement

Collapse
No announcement yet.

Order

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

  • Order

    How can we limit the query/num of rows to 1 using an order by?

  • #2
    Okay this was a question for oracle, using a subquery is the solution but now i have a issue due to scriptcase, i can;t(well i can but all fields will disappear) add a var into the grid sql query right after the where clause, is this a bug?
    Code:
    SELECT 
        "id",
        f1,
        f2,
    FROM (
        SELECT 
        "id",
        f1,
        f2
        from
        table_f
        WHERE e_id = '[e_id]'
        order by f1 desc)
    WHERE 1=1 $limit // $limit is a global var = 'and rownum<2'
    replacing $limit with the string directly 'and rownum<2' it;s ok so where is the problem
    Last edited by Clauu; 01-21-2014, 12:50 PM.

    Comment


    • #3
      [limit] is a global var, $limit is a local var ... You can't use a local var in the select string.
      Best regards: - Reinhard -

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

      Comment


      • #4
        You didn't understand, scriptcase will fail if after that last 'where' condition i will add a global/session/whatever var

        Comment


        • #5
          Originally posted by Clauu View Post
          You didn't understand [...]
          No, i'am use this generally ... but you can try this:

          [limit] = "2=2";

          SELECT ...

          WHERE 1=1 AND [limit]

          This should work.

          [limit] = "rownum < 2";

          SELECT ...

          WHERE 1=1 AND [limit]

          This too.

          Activate the debug modus and look at the generated sql string.
          Best regards: - Reinhard -

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

          Comment


          • #6
            Sir, the scriptcase will fail generating any fields so i can't run/debug the code

            Comment


            • #7
              SELECT
              "id",
              f1,
              f2,
              FROM (
              SELECT
              "id",
              f1,
              f2
              from
              table_f
              WHERE e_id = '[e_id]'
              order by f1 desc) <<<<<<<<<<<<<< this order by is probably not allowed.... Some databases only allow outer oder and not inner as you did.
              WHERE 1=1 $limit // $limit is a global var = 'and rownum<2'

              Comment


              • #8
                Sir, i can easily run this query in my oracle client so most probably this is a scriptcase issue/bug.

                Comment


                • #9
                  I advise you to turn on debugging mode and show the actual sql statement being executed. There you van see exactly what is being generated and executed.

                  Comment


                  • #10
                    Originally posted by rr View Post
                    I advise you to turn on debugging mode [...]
                    See post #5 and #6 ...
                    Best regards: - Reinhard -

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

                    Comment


                    • #11
                      This is definitely a bug, reported here http://www.scriptcase.net/forum/show...bquery-var-bug

                      Comment

                      Working...
                      X