Itgenpse009: Een select ... into-instructie in PSQL moet precies een rij ophalen, maar er zijn meerdere rijen opgehaald

[fbt]

De onderstaande code op Exact Online Salaris geeft een foutmelding:

An error occured - 15e43c8a-589b-4ab1-8351-6893e482cc04
itgenpse009: Een select … into-instructie in PSQL moet precies een rij ophalen, maar er zijn meerdere rijen opgehaald.
Pas s.v.p. de logica van uw select-statement in PSQL aan om altijd precies een rij terug te geven in plaats van 29 rijen.

declare
  l_divisions     constant varchar2 := cloud_http.get_request_query_parameter_value('divisions');
  --
  -- Table function specific parameters.
  --
  l_payroll_year  constant varchar2 := cloud_http.get_request_query_parameter_value('year');
  --
  l_json          clob;
begin
  use all@eol;
  --
  -- Return output as JSON.
  --
  cloud_http.set_response_content_type('application/json');
  --
  -- Append JSON form of table contents to HTTP output.
  --
  select *
  into   l_json
  from   ( select ptn.Division
           ,      ptn.EmployeeHID
           ,      ptn.PayrollComponentCode
           ,      ptn.payrollYear
           ,      ptn.Period
           ,      ptn.CalculatedAmount
           from   systemdivisions@eol sdn
           join   PayrollTransactionsByPayrollYear(sdn.code, l_payroll_year) ptn
           on     ptn.Status != 7
           where  ',' || replace(l_divisions, ' ', '') || ',' like '%,' || sdn.code || ',%'
           for json auto
         )
  ;
  cloud_http.append_to_response_body_text(l_json);
end;

Deze foutmelding treedt ook op als ik de inner-view samenvoeg met de buitenste query.

Als de tabelfunctie PayrollTransactionsByPayrollYear maar enkele honderden rijen teruggeeft, dan treedt de foutmelding niet op.

De foutmelding lijkt op Itgenpse009: Differences when running a query in the SQL Editor compared to a stored procedure.

[forums]

Het blijkt dat er sprake is van een punt dat ergens op grensvlak zweeft tussen “documentation bug”, “bug” en “feature”. Er kunnen meerdere rijen komen uit de for json auto, namelijk één per 1.000 rijen en dan nog afhankelijk van aantal randvoorwaarden. Dit is niet beschreven en niet instelbaar.

Nieuw: for json output per <GETAL> rows

Een aanpassing wordt verwerkt in de Invantive SQL-grammatica bij de for json clause. Voortaan kan er naast de root node, wel/niet opnemen null waarden en wel/niet opnemen JSON array-buitenlaag ook opgegeven worden hoeveel rijen per regel terug moeten komen via:

, output per <GETAL> rows

Indien niet opgegeven, dan wordt de eerder gebruikte standaardwaarde van 1.000 rijen gebruikt.

De eerste regel zal - tenzij onderdrukt - het array-karakter [ bevatten, de laatste regel eindigt met ].

Een voorbeeld van een Invantive SQL-query die de getallen tot/met 100 per drie gegroepeerd teruggeeft is:

select rge.value
from   range@datadictionary(100) rge
for    json auto
,      output per 3 rows

Waarom losse rijen met JSON?

Het gebruik van de for json-clause in Invantive SQL heeft een grote vlucht genomen. Inmiddels worden soms honderden gigabytes aan data per dag verwerkt hiermee. Echter, deze verwerking is sterk batchmatig. Dit leidt tot een grote belasting van de apparatuur. Door meer mogelijkheden te bieden voor het opknippen van de JSON-berichten wordt het eenvoudiger om tussenresultaten alvast op te sturen naar de ontvanger. Hierdoor daalt het maximale geheugenbeslag en kunnen ook resultaten van meer dan 2 GB verwerkt worden.

In de toekomst is het streven om ook teksten te kunnen toevoegen aan de HTTP stream van Invantive Cloud als het totale volume meer dan 2 GB is.

Toepassing: alles in één rij

Om alle data in één rij te plaatsen gebruik je een bijzonder hoge waarde voor het getal.

[fbt]

Helaas krijg ik een syntax error *** output *** bij deze oplossing

[forums]

De nieuwe functionaliteit is nog niet in productie genomen. Zodra de tag “issue-waiting-release” op dit topic verwijderd wordt, zal het in productie genomen zijn. Dit kan enkele dagen tot weken duren.

[fbt]

Duidelijk, wist ik niet.

[forums]

Ja, helaas kost het enige doorlooptijd om voldoende zekerheid te krijgen dat nieuwe versie niet leidt tot grote problemen.

[forums]

De verbeterde for json implementatie is in productie genomen op Invantive Cloud.

Gebruik bij voorkeur een for...loop... end loop; om alle data te verwerken of gebruik een hoge waarde voor output per ... rows zoals beschreven in de SQL grammatica op:

Invantive SQL Grammar v22.0

[fbt]

Hogere waarde werk niet helaas, zie onderstaande foutmelding:

itgenpse009: Een select … into-instructie in PSQL moet precies één rij ophalen, maar er zijn meerdere rijen opgehaald. Pas s.v.p. de logica van uw select-statement in PSQL aan om altijd precies één rij terug te geven in plaats van 979 rijen.

Onderstaand is het script wat ik gebruik, ook als ik het precies aantal rijen opgeef dan krijg ik de melding.

declare
  l_divisions     constant varchar2 := cloud_http.get_request_query_parameter_value('divisions');
  l_payroll_year  constant varchar2 := cloud_http.get_request_query_parameter_value('year');
  --
  l_json clob;

begin
  use all@eol;
  --
  -- Return output as JSON.
  --
  cloud_http.set_response_content_type('application/json');
  --
  -- Append JSON form of table contents to HTTP output.
  --
  select *
  into   l_json
  from   ( select ptn.Division
           ,      ptn.EmployeeHID
           ,      ptn.PayrollComponentCode
           ,      ptn.payrollYear
           ,      ptn.Period
           ,      ptn.CalculatedAmount
           from   systemdivisions@eol sdn
           join   PayrollTransactionsByPayrollYear(sdn.code, l_payroll_year) ptn
           on     ptn.Status != 7
           where  ',' || replace(l_divisions, ' ', '') || ',' like '%,' || sdn.code || ',%'
           for    json auto
           ,      output per 1000  rows
     ) 
;
  cloud_http.append_to_response_body_text(l_json);
end;

[forums]

Eerste mogelijkheid is om een hogere waarde dan 1000 te gebruiken, bijvoorbeeld een miljoen. Dit kost wel veel werkgeheugen. Een andere poging zou zijn om het om te schrijven naar een loop:

declare
  l_divisions     constant varchar2 := cloud_http.get_request_query_parameter_value('divisions');
  l_payroll_year  constant varchar2 := cloud_http.get_request_query_parameter_value('year');
  --
  l_json clob;
begin
  use all@eol;
  --
  -- Return output as JSON.
  --
  cloud_http.set_response_content_type('application/json');
  --
  -- Append JSON form of table contents to HTTP output.
  --
  for r
  in
  ( select *
    from   ( select ptn.Division
             ,      ptn.EmployeeHID
             ,      ptn.PayrollComponentCode
             ,      ptn.payrollYear
             ,      ptn.Period
             ,      ptn.CalculatedAmount
             from   systemdivisions@eol sdn
             join   PayrollTransactionsByPayrollYear(sdn.code, l_payroll_year) ptn
             on     ptn.Status != 7
             where  ',' || replace(l_divisions, ' ', '') || ',' like '%,' || sdn.code || ',%'
             for    json auto
             ,      output per 1000  rows
       ) 
  )
  loop
    cloud_http.append_to_response_body_text(r.json);
  end loop;
end;

Op dit moment geeft deze code nog een foutmelding door PSQL scoping. De oorzaak wordt nog onderzocht en weggenomen.

De for-loop zorgt er voor dat de data uit meerdere brokken mag bestaan en uiteindelijk achter elkaar op de HTTP-uitvoer gezet wordt.

Op dit moment is het nog niet mogelijk om meer dan 2 GB aan HTTP-verkeer terug te geven. De for-loop halveert het maximum in het geheugengebruik van dit proces.

Wens is om ook het toevoegen aan de HTTP-uitvoer te veranderen zodat de tussenresultaten al teruggegeven worden. Door deze streaming stijgen de prestaties omdat het teruggeven en ophalen gelijktijdig gebeurt en kan meer dan de huidige limiet van 2 GB aan HTTP-uitvoer teruggegeven worden. De OData-engine Invantive Bridge Online gebruikt ook een dergelijke techniek.

[fbt]

An error occurred - d77f74a4-9426-4966-bdd7-99891fcfef4b
itgensql151: Onbekend veld of parameter ‘r.json’

Inderdaad, ik wacht af.

[forums]

Deze vraag is automatisch gesloten na 2 weken inactiviteit. Het laatste gegeven antwoord is gemarkeerd als oplossing.

Gelieve een nieuwe vraag te stellen via een apart topic als het probleem opnieuw optreedt. Gelieve in de nieuwe vraag een link naar dit topic op te nemen door de URL er van in de tekst te plakken.