Itgensql056 Syntax error op result_set_name(***"ProjectTimeTransactions"***)

[mvk]

Op Invantive Data Hub Multi-Platform (26.0.168-PROD+5986) krijg ik bij dit SQL-statement:

create or replace table pjttime@inmemorystorage
as
select /*+ result_set_name("ProjectTimeTransactions") */
       pjt.DivisionLabel
,      pjt.Code ProjectCode
,      ptn.EntryNumber
,      pjt.ClassificationDescription 
       label '{res:itgen_eol_project_classification}'
,      pjt.TypeDescription 
       label '{res:itgen_project_type}'
,      ptn.date
,      to_char(ptn.StartTime, 'HH24:MI') 
       StartTime
       label '{res:itgen_eol_start_time}'
,      to_char(ptn.EndTime, 'HH24:MI')
       EndTime
       label '{res:itgen_eol_end_time}'
,      to_char(ptn.date, 'YYYY-IW') 
       iso_week
       label '{res:itgen_iso_week_number}'
,      to_char(ptn.date, 'D') 
       day_of_iso_week
       label '{res:itgen_day_number_iso_week}'
,		emp.id
,      emp.EmployeeHID
,      emp.FullName
       EmployeeFullName
       label '{res:itgen_eol_employee}'
,      aet.JobTitleDescription
,      aet.departmentcode
,      aet.InternalRate
,      ptn.Quantity
,      ptn.Currency
,      ptn.AmountFC
,      ptn.PriceFC
,      act.Code
       AccountCode
       label '{res:itgen_eol_account_code}'
,      act.Name 
       AccountName
       label '{res:itgen_eol_account_name}'
,      wbs.Description
       ActivityDescription
       label '{res:itgen_eol_activity_description}'
,      ssn.Number
       SubscriptionNumber
       label '{res:itgen_eol_subscription_number}'
,      ssn.Description
       SubscriptionDescription
       label '{res:itgen_eol_subscription_description}'
,      pjt.ManagerFullName
,      pts.Description 
       HourStatusDescription
       label '{res:itgen_eol_hour_status}'
,      itm.Code
       ItemCode
       label '{res:itgen_eol_item_code}'
,      itm.Description
       ItemDescription
       label '{res:itgen_eol_item_description}'
,      igp.Code
       ItemGroupCode
       label '{res:itgen_eol_item_group_code}'
,      igp.Description
       ItemGroupDescription
       label '{res:itgen_eol_item_group_description}'
,      itm.Unit
,      ptn.Notes
from   pjt@inmemorystorage pjt
join   PjtTimeTransactionsIncremental@eol ptn
on     ptn.Division = pjt.Division
and    ptn.Project  = pjt.id
join   ProjectTimeTransactionHourStatuses@eol pts
on     pts.Code = to_char(ptn.HourStatus)
join   ExactOnlineREST..Employees@eol emp
on     emp.id       = ptn.Employee
left
outer
join   ExactOnlineREST..ActiveEmployments@eol aet
on     aet.employee = emp.id
left
outer
join   ItemsIncremental@eol itm
on     itm.Division = ptn.Division
and    itm.id       = ptn.item
left
outer
join   ItemGroups@eol igp
on     igp.Division = ptn.Division
and    igp.id       = itm.ItemGroup
left
outer
join   AccountsIncremental@eol act
on     act.Division = ptn.Division
and    act.id       = ptn.account
left
outer
join   SubscriptionsIncremental@eol ssn
on     ssn.Division      = ptn.Division
and    ssn.EntryId       = ptn.Subscription
left
outer
join   pjtwbs@inmemorystorage wbs
on     wbs.Division   = ptn.Division
and    wbs.ID         = ptn.WBS

de foutmelding:

itgensql056
Syntax error between the two '’ on line 3, column 27.
create or replace tabl…ct /+ result_set_name(“ProjectTimeTransactions”**) */ pjt.DivisionLabel,…

Error: no viable alternative at input ‘create or replace table pjttime@inmemorystorage\r\nas\r\nselect /*+ result_set_name(“ProjectTimeTransactions”’.
at Invantive.Basics.ValidationException..ctor(GlobalState owner, ExecutionOptions executionOptions, String messageCode, String errorMessage, String kindRequest, String localStackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable1 uid, Nullable1 isRecoverable, String poolIdentityId, String url, Nullable1 partyUid, String partitionCode, String tableName, String parameter1, String parameter2, String parameter3, String parameter4, String parameter5, String clientId) at Invantive.Basics.InvantiveSqlException..ctor(GlobalState owner, ExecutionOptions executionOptions, String validationCode, String errorMessage, String kindRequest, String stackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable1 uid, Boolean isRecoverable, String poolIdentityId, String partitionCode, String tableName, String parameter1, String parameter2, String parameter3, String parameter4, String parameter5, String clientId)
at Invantive.Data.InvantiveParserErrorListener.SyntaxError(IRecognizer recognizer, IToken offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException triggeringException)

Hoe los ik dit op?

[forums]

De hint result_set_name gebruikt dubbele aanhalingstekens. Tot/met release 25.0 was het mogelijk om dubbele aanhalingstekens te gebruiken waar een enkel aanhalingsteken ook mocht.

Dit is vanaf release 26.0 niet meer mogelijk in het kader van standaardisatie.

Advies is de dubbele aanhalingstekens door enkele te vervangen.

[mvk]

Werkt nog niet. Hij komt er wel doorheen maar geeft de result set niet terug in SQL. Even heel specifiek, wat ik telkens heb aangepast is:

Van:

select /*+ result_set_name("Projects") */

naar:

select /*+ result_set_name('Projects') */

Dus: dubbele quote vervangen door enkele.

Maar schrijft nog steeds geen result set weg in SQL.

[forums]

Advies is om te controleren dat de gebruiker voldoende rechten heeft. Dat kan door na afloop van deze query te kijken in SessionIOs@DataDictionary. Onvoldoende rechten leidt tot geen rijen, maar ook een foutmelding in de registratie.

Als dat niet helpt, dan is advies om een tracebestand op te sturen van deze verwerking zoals beschreven in:

Ondersteuning bieden bij Data Hub en andere on-premises producten

Go to English version Voor Data Replicator: raadpleeg Ondersteuning bieden bij Data Replicator repository en logging. Voor Invantive Cloud en Invantive Bridge Online, volg deze instructies. U kunt worden gevraagd om gedetailleerde logging te verstrekken gedurende de periode waarin een incident heeft plaatsgevonden. Dit artikel beschrijft hoe u deze informatie kunt verstrekken. Instructies De call stack en andere context kan meestal worden gevonden in het scherm Systeemberichten op Invantive …

[mvk]

Ben er 99,9 % zeker van dat gebruiker voldoende rechten heeft. Waarom?

• Klant garandeert niks te hebben veranderd in rechten;
• Via query editor kan ik de tabellen gewoon benaderen.

Hij loopt er ook goed doorheen maar schrijft de result set niet weg.

[mvk]

Het werkt nog steeds niet.

Ik verwacht tabellen zoals INVOICETERMS in de SQL-database op basis van dit soort queries die in die SQL staan:

use
select spn.code
,      'eol'
from   systempartitions@DataDictionary spn
where  spn.provider_data_container_alias = 'eol'

declare
  l_incl_pjt_type_campaign      boolean  := true;
  l_incl_pjt_type_fixed_price   boolean  := true;
  l_incl_pjt_type_time_material boolean  := true;
  l_incl_pjt_type_non_billable  boolean  := true;
  l_incl_pjt_type_prepaid       boolean  := true;
  l_incl_pjt_historic           boolean  := true;
  l_incl_pjt_active             boolean  := true;
  l_incl_pjt_future             boolean  := true;
  l_pjt_code                    varchar2 := null;
  l_pjt_classification          varchar2 := null;
  l_pjt_account_name_pattern    varchar2 := null;
  l_pjt_description_pattern     varchar2 := null;
  l_pjt_manager_pattern         varchar2 := null;
begin
  --
  -- Selected project.
  --
  create or replace table pjt@inmemorystorage
  as
  select sdn.Label
         DivisionLabel
         label '{res:itgen_eol_division_label}'
  ,      pjt.division
  ,      act.Code
         AccountCode
  ,      act.Name
         AccountName
  ,      pjt.id
  ,      pjt.code
  ,      pjt.description
  ,      pjt.BudgetedCosts
  ,      pjt.BudgetedRevenue
  ,      pjt.BudgetedAmount
  ,      pjt.BudgetedRevenue - pjt.BudgetedCosts BudgetedMargin
  --
  -- Additional.
  --
  ,      pjt.startdate
  ,      pjt.enddate
  ,      pjt.type
  ,      pcn.Description 
         ClassificationDescription
         label '{res:itgen_eol_classification}'
  ,      pjt.InternalNotes
  ,      pte.Description TypeDescription
  ,      usr.FullName
         ManagerFullName
         label '{res:itgen_eol_manager_full_name}'
  ,      act.country
         AccountCountry
  from   ProjectsIncremental@eol pjt
  left
  outer
  join   projectclassifications@eol pcn
  on     pcn.Division = pjt.Division
  and    pcn.id       = pjt.Classification
  --
  -- Filter on classification.
  --
  and    ( l_pjt_classification is null
           or
           pcn.Description = l_pjt_classification
         )
  left
  outer
  join   ExactOnlineREST..Users@eol usr
  on     usr.Division = pjt.Division
  and    usr.UserId   = pjt.Manager
  --
  -- Filter on project manager.
  --
  and    usr.Fullname like coalesce(l_pjt_manager_pattern, '%')
  join   ProjectTypes@eol pte
  on     pte.Code = to_char(pjt.Type)
  join   AccountsIncremental@eol act
  on     act.Division = pjt.Division
  and    act.id       = pjt.account
  --
  -- Filter on account name.
  --
  and    act.name like coalesce(l_pjt_account_name_pattern, '%')
  join   SystemDivisions@eol sdn
  on     sdn.Code = pjt.Division
  where  true
  --
  -- Fixed price / T&M.
  --
  and    ( ( pjt.type = 1 and l_incl_pjt_type_campaign = true )
           or
           ( pjt.type = 2 and l_incl_pjt_type_fixed_price = true )
           or
           ( pjt.type = 3 and l_incl_pjt_type_time_material = true )
           or
           ( pjt.type = 4 and l_incl_pjt_type_non_billable = true )
           or
           ( pjt.type = 5 and l_incl_pjt_type_prepaid = true )
         )
  --
  -- Date selection.
  --
  and    ( ( l_incl_pjt_historic = true and coalesce(pjt.enddate, trunc(sysdate)) < trunc(sysdate) )
           or
           ( l_incl_pjt_active = true and pjt.startdate <= trunc(sysdate) and coalesce(pjt.enddate, trunc(sysdate)) >= trunc(sysdate) )
           or
           ( l_incl_pjt_future = true and pjt.startdate > trunc(sysdate) )
         )
  --
  -- Filter on code.
  --
  and    pjt.code = coalesce(l_pjt_code, pjt.code)
  --
  -- Filter on description.
  --
  and    pjt.description like coalesce(l_pjt_description_pattern, '%')
  ;
end;


create or replace table pjtitm@inmemorystorage
as
select pjt.DivisionLabel
,      pjt.code 
       projectcode 
       label '{res:itgen_eol_project_code}'
,      pjt.description
       ProjectDescription
       label '{res:itgen_eol_project_description}'
,      itm.deliverable
,      itm.description
,      itm.amount
,      itm.percentage
--
-- Planned or actual invoice date, depending on whether it has been invoiced.
--
,      itm.invoicedate 
,      itm.ExecutionFromDate
,      itm.ExecutionToDate
,      itm.itemdescription
from   pjt@inmemorystorage pjt
join   invoiceterms@eol itm
on     itm.Division = pjt.Division
and    itm.project  = pjt.id

create or replace table InvoiceTerms@Master
as
select /*+ result_set_name('InvoiceTerms') */
       pim.DivisionLabel
,      pim.ProjectCode
,      pim.projectdescription
,      pim.deliverable
,      pim.description
,      pim.amount
,      pim.percentage
,      pim.invoicedate 
,      pim.ExecutionFromDate
,      pim.ExecutionToDate
,      pim.itemdescription
from   pjtitm@inmemorystorage pim
order
by     pim.DivisionLabel
,      pim.ProjectCode
,      pim.Description

[forums]

Bij uitvoering via Invantive Cloud komen er 38 InvoiceTerms terug op 92 projecten, die netjes worden opgeslagen. Op een later moment moet de on-premise situatie bekeken worden.

[forums]

De verbinding bleek goed te werken maar te schrijven naar een andere SQL Server-database dan gedacht.