Question
Pegasystems Inc.
NL
Last activity: 4 Oct 2018 13:54 EDT
Custom SQL
Hi,
I need to randomly select a sample from Customer table. To do this, I want to run a CUSTOM sql in a report definition.
What is the best practice and steps to create an SQL that can be used inside a Report Definition?
SELECT MUSTERI_NO FROM
( SELECT MUSTERI_NO FROM V_MUSTERI_BILGI
ORDER BY dbms_random.value )
WHERE rownum < (select count(MUSTERI_NO)/10 FROM V_MUSTERI_BILGI)
***Updated by moderator: Lochan to add Categories***
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Accepted Solution
Pegasystems
IN
- Create a SQL function (let's say random) which returns the SQL snippet dbms_random.value
- Create a report definition which can return this query SELECT MUSTERI_NO, dbms_random.value FROM V_MUSTERI_BILGI
ORDER BY dbms_random.value. This is feasible by putting both MUSTERI_NO and random SQL function in the select fields and making sure we do sort on the random SQL function field - Create a second report which can return this query SELECT COUNT(MUSTERI_NO) FROM V_MUSTERI_BILGI
- Refer the report created in (3) above as a sub report in (2) above.
- Create a SQL function (let's say rownum) which returns the SQL snipped rownum
- Add a filter condition in the report created in (2) above where the "source" is rownum function and the value is pxDivide function which takes one argument as the subreport column and the other being literal value 10
Pegasystems Inc.
GB
I don't know what best practice is here - but I have two suggestions you might want to consider:
1. Use a PRPC CONNECT-SQL rule - and you can basically put your SQL into this Rule.
2. Create Database VIEW using your SQL: then treat it as an 'external' table from PRPC.
See Designer Studio Help for 'CONNECT-SQL' and 'EXTERNAL TABLE':
Reference > Rule types > Connect SQL rules
Reference > Tools, accelerators, and wizards > Connector and Metadata accelerator > About generating connector rules with the Connector and Metadata accelerator
Pegasystems
IN
The above SQL provided by you can be done through sub reports and SQL functions. Note that for the random value SQL function, you will need to include it in the select fields so that you can order by it.
Pegasystems Inc.
NL
Hi,
Could you please be more specific? How can this be done via sub reports and SQL functions?
Accepted Solution
Pegasystems
IN
- Create a SQL function (let's say random) which returns the SQL snippet dbms_random.value
- Create a report definition which can return this query SELECT MUSTERI_NO, dbms_random.value FROM V_MUSTERI_BILGI
ORDER BY dbms_random.value. This is feasible by putting both MUSTERI_NO and random SQL function in the select fields and making sure we do sort on the random SQL function field - Create a second report which can return this query SELECT COUNT(MUSTERI_NO) FROM V_MUSTERI_BILGI
- Refer the report created in (3) above as a sub report in (2) above.
- Create a SQL function (let's say rownum) which returns the SQL snipped rownum
- Add a filter condition in the report created in (2) above where the "source" is rownum function and the value is pxDivide function which takes one argument as the subreport column and the other being literal value 10
EY
US
Hello:
Can someone please post the screenshot of the Random function that you have used? I tried writing the function, but I am unable to use it in Report Definition.
Thanks.