on 08-04-2015 9:20 AM
Greetings All,
I have a function that does something simple:
Argument: sp_id (string)
Return: Integer
integer staged_rates
sp_id = trim(sp_id)
select count(*) into :staged_rates where sp_id = :sp_id;
commit;
return staged_rates
When I run the SQL in the database painter, I get 115,820 rows.
When I run the SQL in a datawindow, I get 115,820 rows.
But when I call the function from a datawindow either in a computed field as uf_get_rowcount_by_sp_id(sp_id) or in the retrieve_end script to set the values in a datawindow, I get mixed results.
If there are less than 20,000 rows, I generally get the correct count. But I have some instances where there are 50,000 to 115,000 rows and I see '0' rows.
I tried changing the datatype of staged_rates from integer to long and now I see rows where before it was 0, but oddly, they are negative (e.g. -15252 rows).
If someone can point me in the right direction, I would certainly appreciate it.
Thanks all!!
Paul
Have you changed the datatype of variable and also the function return type to long?
What about retrieve the count direkt as sub-select in the datawindow sql? It should be much more faster then retrieval in the retrieveend-event.
A call of an external function in a computed field is not good for performance!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks all.
I would be interested in knowing how to do a sub-selected in a datawindow if you have a moment, René.
To all, I thought there might be a problem with using integer, but long wasn't working either. Even if it is resource intensive, it should still work.
I ended up putting the SQL in datawindows, creating datastores and populating the datawindow in the retrieve_end event and now it works fine.
Long it is!!
Thanks all!!
Paul
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sub-Selects are a database feature.
e.g. for Oracle you can do a select like in this example
SELECT COL1,
COL2,
(SELECT count (*)
FROM TAB2
WHERE COL3 = TAB1.COL1 ) AS CNT
FROM TAB1
The Sub-Select must not return more than 1 row!
You should give it a name because otherwise powerbuilder may crash.
If what you want is to show in each row the total count by sp_id then you'd be better off using the group by clause in the select statement. For example,
SELECT sp_id, COUNT(*)
FROM ...
GROUP BY sp_id
This will return the total count for each sp_id in each row.
Your solution of using an additional datastore to populate the datawindow in the retrieveend event is not bad, performance wise, because it is done only once. But then, you are performing an additional query to the database. This could easily be solved using the group by clause.
Also, calling a global function from a computed field that performs database queries is not good practice. Remember that the function gets called each time you scroll the datawindow.
A PowerBuilder integer is (from the help file): 16-bit signed integers, from -32768 to +32767
A PowerBuilder long is: 32-bit signed integers, from -2147483648 to +2147483647.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This is not a Function Timeout.
Looks like a mismatch between the return type and your local stage_rates variable type.
Better use:
Argument: sp_id (string)
Return: Long
Long staged_rates
sp_id = trim(sp_id)
select count(*) into :staged_rates where sp_id = :sp_id;
commit;
return staged_rates
hth
Arnd
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.