Grid Custom SQL Question

[emhill]

Anyone know why this SQL statement would not work in the custom SQL property for a grid:

with sn as (select sncomp, sncust, snedat, snfdat, case when snusrp <> ' ' then snusrp else snuser end as suser, row_number() over (partition by sncomp, sncust order by snedat desc) as rownbr from synot6) select a.accust, a.acname, a.aca01, a.aca02, a.aca03, a.aca04, a.aca06, a.aca05, a.acsr, a.accrcd, a.accrlm, coalesce(ics.tomdy(b.snedat), ' '), coalesce(ics.tomdy(b.snfdat), ' '), coalesce(b.suser, ' ') from armst as a left join sn b on a.accomp = b.sncomp and a.accust = b.sncust and b.rownbr = 1 where a.aca05 <> 0


Copy and paste that into STRSQL or "Run SQL Scripts" in Navigator and it works perfectly.

Any ideas?

Thanks!

[Scott Klement]

In order for the grid to work properly, it needs to know the total number of rows in your SQL query. However, SQL-driven grids do not run the whole SQL statement before loading the grid. Instead, they use a cursor to load only one page of the grid at a time. (If you've ever noticed that the grid is blank while scrolling it, that's why -- because it only loads the page of data from IBM i after you've finished scrolling, so it's blank during the scroll.)

In order to get the total row count, it does this:

Code: Select all

SELECT COUNT(*) from ( your-custom-sql-statement )

The problem, and the reason you can't use Common Table Exressions (CTEs) -- i.e. statements that start with WITH X AS is because they don't work with that method of getting the number of rows.

You say it "works in Run SQL Scripts". Try this: do SELECT COUNT(*) FROM ( your whole statement here) in Run SQL scripts. I bet you get the same error there that you're getting in the grid.

[Scott Klement]

FYI: Starting with the next release of Profound UI (which will likely be 5.8.0, but not 100% sure) I've added a new grid property "allow any select statement". If you enable this, it will allow any select statement that works in Run SQL scripts or STRSQL.

When this is disabled (which is the default) it will continue to use the COUNT(*) method explained above, with the same limitations. But, if you enable this property, it will instead run your statement the "normal" way, and loop through all rows to count them. I added buffering and multi-row fetch to make this as fast as possible, but it still takes about 4x as long as the COUNT(*) method. However, it will allow any/all SQL statements to work.

For a small number of rows you probably won't notice the performance difference, and it will allow all statements to work.

For large numbers of rows, you might, in which case you'd want to keep the feature turned off. For 1 million rows, it added about 3 seconds, which isn't too bad, but still, no point in enabling it unless you need it.

[emhill]

Thanks Scott. We are just in the "let's see how this works" phase on the custom SQL grids but so far we like it for the loading of more that 9,999 rows so the user can export an entire customer master (in our case no more than 99,999) to EXCEL. That is what started all this. The new feature would be great and waiting for the next release is no problem.

On a related note when I scale that statement down so it will load data, I have the onrowclick event set to populate a hidden textbox with the customer number and click a button that tells my RPG to call an inquiry program using the passed customer number. That works great. Upon return to the grid from the inquiry I want to position the row that was selected to the top of the grid. I use the "Position at top" = true to try to accomplish this. Again in the onrowclick I have it populating another hidden textbox with the row number: changeElementValue("SELRRN", row). In my RPG in the logic condition for the external call I set my subfile record number with the SELRRN value. This works ok unless the row selected is greater than 82/83 then it will always position back to row 82/83. I can see it flash to the row I selected but then always back to row 82/83. I have submitted a support ticket for this but have not heard anything back. Was just wondering since this was a different type of grid load if accomplishing that might be a little different.

Thanks for all your help!!!!

P.S. That wonderful SQL statement I'm using was developed with a bunch of help from your friend and mine, Mr. Holt!!! :-)

[SeanTyree]

Hi Scott,

Will this new property (allow any select statement) also be added to SQL driven drop-downs?

Thanks,
Sean

[Scott Klement]

No, SQL driven drop downs don't do the COUNT(*), so adding an alternative way to get the count wouldn't make sense.