Runtime Error

Hi,

I've this advance query to extract information:

SELECT {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[Id],  
       {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[CreatedAt],
       {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[LastUpdatedAt],
       {APPROVAL_TYPE}.[Label],
       {IDENTITY_CHANGE}.[IdentityId],
       {IDENTITY_CHANGE}.[Id],
       {IDENTITY_CHANGE}.[Name],
       {IDENTITY_CHANGE}.[NIF],
       {IDENTITY_POSITION_TEMP}.[CurrentPositionId],
       {IDENTITY_POSITION_TEMP}.[NewPositionId],
       {IDENTITY_POSITION_TEMP}.[IsMain],
       OriginalTeam.[Name],
       OriginalFunction.[Name],
       DestinationTeam.[Name],
       DestinationFunction.[Name],
       {IDENTITY}.[Name],
       DECODE({IDENTITY_TEAM_ROLE}.[Id], NULL, 0, 1),
       {IDENTITY_TEAM_ROLE}.[CreatedBy],
       {IDENTITY_CHANGE}.[SapCompany],
       {PARTNER}.[Name],
       {IDENTITY_CHANGE}.[IdentityTypeId],
       {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[IdentityRoleTypeId],
       OriginalDirection.DIRECTION,
       OriginalDirection.PATH,
       DestinationDirection.DIRECTION,
       DestinationDirection.PATH,
       0 IsSelected,
       {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[ApprovalProcessId],
       T_APA.[StatusMessage] [ApprovalProcessAsyncStatusMsg]

    FROM {APPROVAL_PROCESS}
    INNER JOIN {APPROVAL_TYPE} ON {APPROVAL_TYPE}.[Id] = {APPROVAL_PROCESS}.[ApprovalTypeId]
    INNER JOIN {APPROVAL_PROCESS_IDENTITY_RESPONSE} ON {APPROVAL_PROCESS}.[Id] = {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[ApprovalProcessId] AND
                                                    {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[IsClosed] = 0 AND
                                                    {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[TempIdentityPositionId] IS NOT NULL AND
                                                    {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[ApprovalStatusId] = @ApprovalStatusId_ToBeApproved AND
                                                    (
                                                        {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[ParentResponseId] IS NULL OR 
                                                        (@ApprovalStatusId_Approved = 
                                                            (
                                                                SELECT InsideResponse.[ApprovalStatusId]
                                                                    FROM {APPROVAL_PROCESS_IDENTITY_RESPONSE} InsideResponse
                                                                  WHERE {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[ParentResponseId] = InsideResponse.[Id]
                                                            )
                                                        )
                                                    )
    INNER JOIN {IDENTITY_CHANGE} ON {IDENTITY_CHANGE}.[Id] = {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[IdentityChangeId]
    INNER JOIN {IDENTITY_POSITION_TEMP} ON ({IDENTITY_POSITION_TEMP}.[Id] = {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[TempIdentityPositionId] AND {IDENTITY_POSITION_TEMP}.[ChangeStatusId] = @ChangeStatusId_Submitted)
    LEFT  JOIN {IDENTITY} ON {IDENTITY}.[Id] = {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[CreatedBy]
    LEFT  JOIN {IDENTITY_TEAM_ROLE} ON ({IDENTITY_TEAM_ROLE}.[TeamId] = {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[TeamId] AND ({IDENTITY_TEAM_ROLE}.[IdentityRoleTypeId] = @IdentityTypeRoleId_Impersonate or {IDENTITY_TEAM_ROLE}.[IdentityRoleTypeId] = @IdentityTypeRoleId_Clearance ) AND {IDENTITY_TEAM_ROLE}.[IdentityId]=@SessionIdentityId)
    LEFT  JOIN {POSITION} OriginalPosition ON OriginalPosition.[Id] = {IDENTITY_POSITION_TEMP}.[CurrentPositionId]

    LEFT  JOIN {POSITION} DestinationPosition ON DestinationPosition.[Id] = {IDENTITY_POSITION_TEMP}.[NewPositionId]
    LEFT  JOIN {TEAM} OriginalTeam ON OriginalTeam.[Id] = OriginalPosition.[TeamId]

    LEFT  JOIN (SELECT DIRECTION.[DIRECTION_NAME] DIRECTION,
                TEAM.[PATH] PATH,
                TEAM.[TEAM_ID] ID
                FROM {VIEW_GUIA_TEAM_TREE} TEAM
                INNER JOIN {VIEW_GUIA_TEAM_TREE} DIRECTION 
                ON TEAM.[DIRECTION_ID] = DIRECTION.[TEAM_ID]) OriginalDirection ON OriginalDirection.ID = OriginalTeam.[Id]   

    LEFT  JOIN {TEAM} DestinationTeam ON DestinationTeam.[Id] = DestinationPosition.[TeamId]

    LEFT  JOIN (SELECT DIRECTION.[DIRECTION_NAME] DIRECTION,
                TEAM.[PATH] PATH,
                TEAM.[TEAM_ID] ID
                FROM {VIEW_GUIA_TEAM_TREE} TEAM
                INNER JOIN {VIEW_GUIA_TEAM_TREE} DIRECTION 
                ON TEAM.[DIRECTION_ID] = DIRECTION.[TEAM_ID]) DestinationDirection ON DestinationDirection.ID = DestinationTeam.[Id]

    LEFT  JOIN {FUNCTION} OriginalFunction ON OriginalFunction.[Id] = OriginalPosition.[FunctionId]
    LEFT  JOIN {FUNCTION} DestinationFunction ON DestinationFunction.[Id] = DestinationPosition.[FunctionId]
    LEFT  JOIN {PARTNER} ON {PARTNER}.[Id] = {IDENTITY_CHANGE}.[PartnerId]
    LEFT  JOIN {APPROVAL_PROCESS_ASYNC} ON ({APPROVAL_PROCESS_ASYNC}.[ApprovalProcessId] = {APPROVAL_PROCESS}.[Id])
    LEFT JOIN (
        SELECT 
            APA.[ApprovalProcessId], 
            APA.[StatusMessage]
        FROM (
            SELECT 
                {APPROVAL_PROCESS_ASYNC}.[ApprovalProcessId], 
                {APPROVAL_PROCESS_ASYNC}.[StatusMessage], 
                ROW_NUMBER() OVER (PARTITION BY {APPROVAL_PROCESS_ASYNC}.[ApprovalProcessId] ORDER BY {APPROVAL_PROCESS_ASYNC}.[Id] DESC) [RN]
            FROM 
                {APPROVAL_PROCESS_ASYNC}
            WHERE 
                {APPROVAL_PROCESS_ASYNC}.[ApprovalProcessId] IS NOT NULL
        ) APA
        WHERE APA.[RN] = 1
    ) T_APA ON ({APPROVAL_PROCESS}.[Id] = T_APA.[ApprovalProcessId])
  WHERE (@Filter_TeamId = 0 OR {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[TeamId] = @Filter_TeamId)
    AND 
    ( @Permission_CanAppAllProcesses = 1 OR 
        ( {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[IdentityRoleTypeId] IN (@Identity_IdentityRoleTypeIdList) AND
            EXISTS
            (
                SELECT Identity_Id
                    FROM TABLE(@DB_Catalog.GUIA_FUNCTIONS.FT_GET_IDENTS_BY_ROLETYPE_TEAM('' || {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[IdentityRoleTypeId] || ',' || @IdentityTypeRoleId_Impersonate || ',' || @IdentityTypeRoleId_Clearance || '', {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[TeamId], {IDENTITY_CHANGE}.[PartnerId], 1, 0))
                  WHERE Identity_Id = @SessionIdentityId or @SessionIdentityId is null
            )

        )
    )
    AND NOT EXISTS (
        SELECT 1
        FROM {APPROVAL_PROCESS_ASYNC}
        WHERE {APPROVAL_PROCESS_ASYNC}.[ApprovalProcessId] = {APPROVAL_PROCESS}.[Id]
        AND {APPROVAL_PROCESS_ASYNC}.[ApprovalProcessAsyncStatusId] = @ApprovalProcessAsyncStatusId_ToProcess 
    )
  ORDER BY @OrderBy


When I search to get a report, I've a error like this:


Error executing query. Error in advanced query User_IdentitiesPositions in Preparation in Report_Tasks in ReportsFlow in GUIA (SELECT {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[Id],           {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[CreatedAt],         {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[LastUpdatedAt],         {APPROVAL_TYPE}.[Label],         {IDENTITY_CHANGE}.[IdentityId],         {IDENTITY_CHANGE}.[Id],         {IDENTITY_CHANGE}.[Name],         {IDENTITY_CHANGE}.[NIF],         {IDENTITY_POSITION_TEMP}.[CurrentPositionId],         {IDENTITY_POSITION_TEMP}.[NewPositionId],         {IDENTITY_POSITION_TEMP}.[IsMain],         OriginalTeam.[Name],         OriginalFunction.[Name],         DestinationTeam.[Name],         DestinationFunction.[Name],         {IDENTITY}.[Name],         DECODE({IDENTITY_TEAM_ROLE}.[Id], NULL, 0, 1),         {IDENTITY_TEAM_ROLE}.[CreatedBy],         {IDENTITY_CHANGE}.[SapCompany],         {PARTNER}.[Name],         {IDENTITY_CHANGE}.[IdentityTypeId],         {APPROVAL_PROCESS_IDENTITY_RESPONSE}.[IdentityRoleTypeId],         OriginalDirection.DIRECTION,         OriginalDirection.PATH,         D ...): Could not assign '' to 'STR_DASHBOARD_POSITION.IsApprove'... Invalid column index specified


The STR_DASHBOARD_POSITION structure is in the image


Can you help me please


str.png

Missing two attributes in Select

IsApprove , IsReject

Are you using a column number in the "order by" ? 

When @OrderBy is 0 or out of range you get an error similar to that.

Regards

Graça

Hi,

Please check the data type of structure attribute "STR_DASHBOARD_POSITION.IsApprove". I think this is Boolean and you are assigning blank value in it. Please see the below error -

Could not assign '' to 'STR_DASHBOARD_POSITION.IsApprove'


Thanks

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.