Order

Applications Grid / Reports
#1

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?


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

#3

[limit] is a global var, $limit is a local var … You can’t use a local var in the select string.

#4

You didn’t understand, scriptcase will fail if after that last ‘where’ condition i will add a global/session/whatever var

#5

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.

#6

Sir, the scriptcase will fail generating any fields so i can’t run/debug the code

#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’

#8

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

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

#10

See post #5 and #6 …

#11

This is definitely a bug, reported here http://www.scriptcase.net/forum/showthread.php?5536-Subquery-var-bug