Itgenpse009: Differences when running a query in the SQL Editor compared to a stored procedure

Questions
, , ,
1

I’m having some trouble when running a code snippet as a stored procedure, where I get an error :

An error occured - 2fea40e7-87b3-4488-a225-c24a80e4224f
itgenpse009:
A select … into statement in PSQL must retrieve exactly one row, but multiple rows were retrieved.
Please adapt the logic of your select statement in PSQL to always return exactly one row instead of 2 rows.

When I´m running it in the SQL Editor I have no issues.

I think the error might be due to the LoketNlRest.PayrollRun.PayrollRunsByPayrollAdministrationId returns two PayrollRunIds, since it works fine when only one is returned.

Query:

select distinct runs.id
from   LoketNlRest.PayrollAdministration.PayrollAdministrationsByEmployerId('XXX') payrollAdministration
join   LoketNlRest.PayrollRun.PayrollRunsByPayrollAdministrationId(payrollAdministration.id) runs
on     runs.payrollRunStatus_key = 9 and TRUNC(runs.approvalTime) = TO_DATE('11/01/2018','DD/MM/YYYY')
join   LoketNlRest.PayrollRunResults.PayrollRunPayslipTypeComponentsByPayrollRunId(runs.id) runSpecifications;
2

Is it possible to add the stored procedure code?

4

Thx.

Can you add also the stored procedure code (begin…end or create or replace procedure…end)?

Please note that:

TRUNC(runs.approvalTime) = TO_DATE('11/01/2018','DD/MM/YYYY')

effectively disables the use of indexes and server-side filtering. The following might run faster:

    runs.approvalTime >= TO_DATE('11/01/2018','DD/MM/YYYY')
and runs.approvalTime <  TO_DATE('11/01/2018','DD/MM/YYYY') + 1
5

Yes, here’s the rest of the stored procedure. I do think however that it’s the snippet with a query in
‘LoketNlRest.PayrollRunResults.PayrollRunPayslipTypeComponentsByPayrollRunId(runs.id) runSpecifications;’ which causes the problem.

declare
    l_output      varchar2;
    in_employerId guid;
    batchDate     varchar2;
    periodNumber  varchar2;
    periodYear    varchar2;

begin
  in_employerId    := cast(cloud_http.get_request_query_parameter_value('employerId') as guid);
  batchDate        := cast(cloud_http.get_request_query_parameter_value('batchDate') as varchar2);

  create or replace table payrollRuns@inmemorystorage
  as
  select runs.payrollRunStatus_key
  ,      runs.payrollRunStatus_value
  ,      runs.approvalTime
  ,      runs.sequenceNumber
  ,      runSpecifications.payrollComponent_key
  ,      runSpecifications.payrollComponent_value
  ,      runSpecifications.payrollComponent_category_key
  ,      runSpecifications.payrollComponent_category_value
  ,      runSpecifications.payrollComponent_costsEmployer_value
  ,      runSpecifications.value
  ,      runSpecifications.valueSpecialTariff
  ,      runSpecifications.payrollPeriod_year
  ,      runSpecifications.payrollPeriod_periodNumber
  ,      runSpecifications.employmentId
  from   LoketNlRest.PayrollAdministration.PayrollAdministrationsByEmployerId('XXX') payrollAdministration
  join   LoketNlRest.PayrollRun.PayrollRunsByPayrollAdministrationId(payrollAdministration.id) runs
  on     runs.payrollRunStatus_key = 9 
  and    TRUNC(runs.approvalTime) = TO_DATE('11/01/2018','DD/MM/YYYY')
  join   LoketNlRest.PayrollRunResults.PayrollRunPayslipTypeComponentsByPayrollRunId(runs.id) runSpecifications;
  on     runs.payrollPeriod_year = runspecifications.payrollPeriod_year
  ;

  create or replace table payrollPeriodData@inmemorystorage
  as
  select employmentId
  ,      payrollPeriodData.distributionUnit_code
  ,      payrollPeriodData.distributionUnit_description
  ,      payrollPeriodData.payrollPeriod_year
  ,      payrollPeriodData.payrollPeriod_periodNumber
  from  ( select distinct employmentId 
          from payrollRuns@inMemoryStorage) employmentIds
          join LoketNlRest.PayrollPeriodData.PayrollPeriodDataByEmploymentId(employmentIds.employmentId
        ) payrollPeriodData
  ;

  select pr.payrollRunStatus_key
  ,      pr.payrollRunStatus_value
  ,      pr.approvalTime
  ,      pr.sequenceNumber
  ,      pr.payrollComponent_key
  ,      pr.payrollComponent_value
  ,      pr.payrollComponent_category_key
  ,      pr.payrollComponent_category_value
  ,      pr.payrollComponent_costsEmployer_value
  ,      pr.value
  ,      pr.valueSpecialTariff
  ,      pr.payrollPeriod_year
  ,      pr.payrollPeriod_periodNumber
  ,      pr.employmentId
  ,      pd.distributionUnit_code
  ,      pd.distributionUnit_description 
  into   l_output
  from   payrollRuns@inMemoryStorage pr
  join   payrollPeriodData@inMemoryStorage pd
  on     pr.employmentId = pd.employmentId 
  and    pr.payrollPeriod_periodnumber = pd.payrollPeriod_periodnumber 
  and    pr.payrollPeriod_year = pd.payrollPeriod_year
  for    json auto
  ;

  cloud_http.set_response_content_type('application/json');
  cloud_http.set_response_body_text(l_output);
end;
6

The error itgenpse009 can not be reproduced using a statement such as:

declare
  l_output varchar2;
begin
  create or replace table t1@inmemorystorage
  as
  select 1 c, dummy_int16 from dual@datadictionary union all select 2 c, dummy_int16 from dual@datadictionary
  ;
  create or replace table t2@inmemorystorage
  as
  select 1 c, dummy_string from dual@datadictionary union all select 2 c, dummy_string from dual@datadictionary
  ;
  select *
  into   l_output
  from   t1@inmemorystorage x
  join   t2@inmemorystorage y
  on     x.c = y.c
  for    json auto
  ;
  dbms_output.put_line(l_output);
end;

Can you simplify the PSQL code to the absolute minimum which still throws an itgenpse009?

8

Pleas refer to Itgenpse009: Een select ... into-instructie in PSQL moet precies een rij ophalen, maar er zijn meerdere rijen opgehaald - 2 van forums (Dutch) for more information.

The for json has some undocumented limitations for which the next release includes an improvement using:

, output per <NUMBER> rows

Please set NUMBER to a high default value.

1 like
9

The for json syntax has been extended in production. Best is to use a for...loop ... end loop; to process all data.

1 like
10

This question was automatically closed after 2 weeks of inactivity. The last answer given has been marked as a solution.

Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.