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

[mvk]

On Invantive Data Hub Multi-Platform (26.0.168-PROD+5986) I get this error with this 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

the error message:

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)

How do I solve this?

[forums]

The hint result_set_name uses double quotation marks. Up to and including release 25.0, it was possible to use double quotation marks where a single quotation mark was also allowed.

From release 26.0, this will no longer be possible as part of standardisation.

The advice is to replace the double quotation marks with single ones.

[mvk]

Still not working. It goes through but doesn’t return the result set in SQL. To be very specific, what I have changed each time is:

From:

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

to:

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

So: replaced double quotes with single ones.

But it still doesn’t write the result set in SQL.

[forums]

Advice is to check that the user has sufficient permissions. This can be done by looking at SessionIOs@DataDictionary after this query. Insufficient permissions result in no rows, but also an error message in the log.

If that doesn’t help, the advice is to send a trace file of this process as described 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]

He is 99.9% sure that the user has sufficient permissions. Why?

• The client assures that nothing has been changed in the permissions;
• I can access the tables through the query editor without any issue.

He goes through it smoothly but doesn’t write away the result set.

[mvk]

It still doesn’t work.

I expect tables like INVOICETERMS in the SQL database based on this type of queries that are in the SQL:

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]

When executed via Invantive Cloud, 38 InvoiceTerms are returned on 92 projects, which are neatly stored. The on-premise situation should be reviewed at a later time.

[forums]

The connection appeared to be working well but was writing to a different SQL Server database than expected.