Skip to main content

This content is closed to future replies and is no longer being maintained or updated.
Links may no longer function. If you have a similar request, please write a new post.

Question

 Pavani r (Pavanir6)

IN
Pavanir6 Member since 2016 113 posts
Posted: Nov 17, 2017
Last activity: Nov 20, 2017
Posted: 17 Nov 2017 6:57 EST
Last activity: 20 Nov 2017 2:18 EST
Closed

How to process a result set that is returned from postgresql functions in Pega

We have a stored from proc in Oracle as below

all PAYROLL.IDENTIFY_EMPLOYEES_Generic({.PayrollCountryISO3 in},{.PayrollRegRegion in},{.FormattedPayCycleStartDate in},{.FormattedPayCycleEndDate in},{.MinPrsnlInfoUnprcsdID out},{.MaxPrsnlInfoUnprcsdID out},{.MinNamesUnprcsdID out},{.MaxNamesUnprcsdID out},{.MinNationUnprcsdID out},{.MaxNationUnprcsdID out},{.MinPersDtUnprcsdID out},{.MaxPersDtUnprcsdID out},{.MinEmpAdrsUnprcsdID out},{.MaxEmpAdrsUnprcsdID out},{.MinEmailAdrsUnprcsdID out},{.MaxEmailAdrsUnprcsdID out},{.MinJobInfoUnprcsdID},{.MaxJobInfoUnprcsdID},{.MinJobUnprcsdID out},{.MaxJobUnprcsdID out},{.MinJobPayrollUnprcsdID out},{.MaxJobPayrollUnprcsdID out},{.MinJobCrntUnprcsdID out},{.MaxJobCrntUnprcsdID out},{.MinAddPayUnprcsdID out},{.MaxAddPayUnprcsdID out},{.Message out},{resultset})

Now same thing we are doing in postgresql as below

Show More

We have a stored from proc in Oracle as below

all PAYROLL.IDENTIFY_EMPLOYEES_Generic({.PayrollCountryISO3 in},{.PayrollRegRegion in},{.FormattedPayCycleStartDate in},{.FormattedPayCycleEndDate in},{.MinPrsnlInfoUnprcsdID out},{.MaxPrsnlInfoUnprcsdID out},{.MinNamesUnprcsdID out},{.MaxNamesUnprcsdID out},{.MinNationUnprcsdID out},{.MaxNationUnprcsdID out},{.MinPersDtUnprcsdID out},{.MaxPersDtUnprcsdID out},{.MinEmpAdrsUnprcsdID out},{.MaxEmpAdrsUnprcsdID out},{.MinEmailAdrsUnprcsdID out},{.MaxEmailAdrsUnprcsdID out},{.MinJobInfoUnprcsdID},{.MaxJobInfoUnprcsdID},{.MinJobUnprcsdID out},{.MaxJobUnprcsdID out},{.MinJobPayrollUnprcsdID out},{.MaxJobPayrollUnprcsdID out},{.MinJobCrntUnprcsdID out},{.MaxJobCrntUnprcsdID out},{.MinAddPayUnprcsdID out},{.MaxAddPayUnprcsdID out},{.Message out},{resultset})

Now same thing we are doing in postgresql as below

call PAYROLL.IDENTIFY_EMPLOYEES_Generic({.PayrollCountryISO3 in},{.PayrollRegRegion in},{.FormattedPayCycleStartDate in},{.FormattedPayCycleEndDate in},{.MinPrsnlInfoUnprcsdID out},{.MaxPrsnlInfoUnprcsdID out},{.MinNamesUnprcsdID out},{.MaxNamesUnprcsdID out},{.MinNationUnprcsdID out},{.MaxNationUnprcsdID out},{.MinPersDtUnprcsdID out},{.MaxPersDtUnprcsdID out},{.MinEmpAdrsUnprcsdID out},{.MaxEmpAdrsUnprcsdID out},{.MinEmailAdrsUnprcsdID out},{.MaxEmailAdrsUnprcsdID out},{.MinJobInfoUnprcsdID},{.MaxJobInfoUnprcsdID},{.MinJobUnprcsdID out},{.MaxJobUnprcsdID out},{.MinJobPayrollUnprcsdID out},{.MaxJobPayrollUnprcsdID out},{.MinJobCrntUnprcsdID out},{.MaxJobCrntUnprcsdID out},{.MinAddPayUnprcsdID out},{.MaxAddPayUnprcsdID out},{.Message out},{resultset})

select minprsnlinfounprcsdid as ".MinPrsnlInfoUnprcsdID", maxprsnlinfounprcsdid as ".MaxPrsnlInfoUnprcsdID", minnamesunprcsdid as ".MinNamesUnprcsdID", maxnamesunprcsdid as ".MaxNamesUnprcsdID", minnationunprcsdid as ".MinNationUnprcsdID", maxnationunprcsdid as ".MaxNationUnprcsdID", minpersdtunprcsdid as ".MinPersDtUnprcsdID", maxpersdtunprcsdid as ".MaxPersDtUnprcsdID", minempadrsunprcsdid as ".MinEmpAdrsUnprcsdID", maxempadrsunprcsdid as ".MaxEmpAdrsUnprcsdID", minemailadrsunprcsdid as ".MinEmailAdrsUnprcsdID", maxemailadrsunprcsdid as ".MaxEmailAdrsUnprcsdID", minjobinfounprcsdid as ".MinJobInfoUnprcsdID", maxjobinfounprcsdid as ".MaxJobInfoUnprcsdID", minjobunprcsdid as ".MinJobUnprcsdID", maxjobunprcsdid as ".MaxJobUnprcsdID", minjobpayrollunprcsdid as ".MinJobPayrollUnprcsdID", maxjobpayrollunprcsdid as ".MaxJobPayrollUnprcsdID", minjobcrntunprcsdid as ".MinJobCrntUnprcsdID", maxjobcrntunprcsdid as ".MaxJobCrntUnprcsdID", minaddpayunprcsdid as ".MinAddPayUnprcsdID", maxaddpayunprcsdid as ".MaxAddPayUnprcsdID", err_msg as ".Message", emps as resultset from PAYROLL.IDENTIFY_EMPLOYEES_Generic({.PayrollCountryISO3 in},{.PayrollRegRegion in},{.FormattedPayCycleStartDate in}::Date,{.FormattedPayCycleEndDate in}::Date)

But i am not gettingresult set. Can some one please suggest me how to proceed.


Show Less
To see attachments, please log in.
Data Integration

Posted: 7 years ago
Posted: 20 Nov 2017 1:04 EST
Pavani r (Pavanir6)

IN

Hi Sudhakar,

 

All marked as out in below query are my out params.

 

all PAYROLL.IDENTIFY_EMPLOYEES_Generic({.PayrollCountryISO3 in},{.PayrollRegRegion in},{.FormattedPayCycleStartDate in},{.FormattedPayCycleEndDate in},{.MinPrsnlInfoUnprcsdID out},{.MaxPrsnlInfoUnprcsdID out},{.MinNamesUnprcsdID out},{.MaxNamesUnprcsdID out},{.MinNationUnprcsdID out},{.MaxNationUnprcsdID out},{.MinPersDtUnprcsdID out},{.MaxPersDtUnprcsdID out},{.MinEmpAdrsUnprcsdID out},{.MaxEmpAdrsUnprcsdID out},{.MinEmailAdrsUnprcsdID out},{.MaxEmailAdrsUnprcsdID out},{.MinJobInfoUnprcsdID},{.MaxJobInfoUnprcsdID},{.MinJobUnprcsdID out},{.MaxJobUnprcsdID out},{.MinJobPayrollUnprcsdID out},{.MaxJobPayrollUnprcsdID out},{.MinJobCrntUnprcsdID out},{.MaxJobCrntUnprcsdID out},{.MinAddPayUnprcsdID out},{.MaxAddPayUnprcsdID out},{.Message out},{resultset})

To see attachments, please log in.
Posted: 7 years ago
Posted: 20 Nov 2017 2:18 EST
Sudhakar Reddy Yaparla (YSudhakarReddy)
JPMorgan Chase & Company

JPMorgan Chase & Company
US

Hi,

You can call the storedproc as below to get ruleset

Show More

Hi,

You can call the storedproc as below to get ruleset

\{{resultset} = call browseFunc(
        {dataPage.inkey1},
        {dataPage.inkey2},
        {dataPage.inkey3},
        {dataPage.OutProperty1 out},
        {dataPage.OutProperty2 out},
        {resultset},
        {resultset})\}


Show Less
To see attachments, please log in.

Related content:

Question

Return results from activity

Question Solved

How to call a function from data transform which returns void.

Question Solved

FormatDateTime function is not returning correct result in CST timezone

Question

Obj Methods return properties in lower case - PostgreSQL

Question

I want to map the result set that is being returned from the DB2 stored procedure in PEGA 6.2 SP1 version. Could you please let me know the code for it? (be it JAVA code or in the connect-sql, for both single result set and multiple result sets)

Question

I want to map the result set that is being returned from the DB2 stored procedure in PEGA 6.2 SP1 version. Could you please let me know the code for it? (be it JAVA code or in the connect-sql, for both single result set and multiple result sets)

Question Solved

Function pr_read_from_stream return invalid of alphabet position .

Question Solved

Report Definition is not returning results from Implementation classes

Discussion

How to set up a SQLJ schema for UDF on PostgreSQL 11 with Windows

Question

PEGA returns search results from another PEGA instance on the same server

We'd prefer it if you saw us at our best.

Pega Collaboration Center has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice