Limit grid row number

Scriptcase 9 General
#1

Hello,

I need to create a Grid where the number of rows shown will be set dinamically by parameter. It sometimes should show 5 rows, others 2, etc.

I cannot set it on the SQL statement, using “select top [n_rows]” the [n_rows], seems not to be allowed.

I also tried the same on the LIMIT field… but same result.

Does anybody know how to set it up?

Thanks in advance.

#2

That’s because TOP and LIMIT are used by SC to paginate.

A workaround is to number the rows using ROW_NUMBER and select only rows minor or equal to a certain parameter

in SQL Server SELECT ROW_NUMBER() OVER( order by <field>) AS num_row, <field1>, <field..n> FROM <table>;

#3

First, thanks for the quick response. Anyway, I also tried this solution but I’m not able to use where for this new field…

where num_row < x is not allowed… what am I missing?

#4

Can you show me the Sql query ?

#5

Solved!!

select *
from (
Select
Row_Number() Over (Order By SGA_ubicaciones.ubicacion) as orden,
SGA_ubicaciones.ubicacion,
SGA_palets.material,
SGA_palets.lote
From
SGA_palets Inner Join
SGA_ubicaciones On SGA_palets.id_palet = SGA_ubicaciones.id_palet
Where
SGA_palets.material = ‘[ref]’)
t1
where
orden <= [n_palets]

[ref] and [n_palets] are global variables.

Thanks for you time!!!

#6

Ok

Just define a new variable ( i.e. VAR_NUMROW ) for the number of rows to show and insert this code in Script Init event

if (!empty([VAR_NUMROW])) {
if (empty({sc_where_current})) {
sc_select_where(add) = “where num_row < [VAR_NUMROW]”;
}
else {
sc_select_where(add) = “AND num_row < [VAR_NUMROW]”;
}
}

1 Like