Report Desk: SELECT Statement Errors Using Alternate Table Names

Report Desk: SELECT Statement Errors Using Alternate Table Names

Release Date: 4/24/2025
Versions: Elliott v8.6 and up

The Problem

When the SELECT statement in the SQL Template uses alternate table names in join statements, an error may occur during the running of the report:
      [SQL Engine] Error in expression: ATTRIB_AMT_1

In order to join the same table (SYATTRIB) one or more times, an alternate table name ,TAXDS, is used in the JOIN.  Unfortunately, this causes the generated SQL SELECT statement, below, to have the above error:

  1. SELECT ORDER_CUSTOMER_NO, ORDER_SHIP_TO_NO, ORDER_SHIP_TO_NAME, ORDER_SHIP_TO_CITY, ORDER_SHIP_TO_ST, ORDER_SHIP_TO_ZIPCD, CUS_TP, CUS_TXBL_FG, ORDER_NO, ORDER_DATE, ORDER_TOTAL_SALE_AMT, ATTRIB_AMT_1, ATTRIB_AMT_2 FROM SYATTRIB

    LEFT JOIN CPORDHDR ON ORDER_NO = SYATTRIB.ATTRIB_REF_NUMBER

    LEFT JOIN ARCUSFIL ON CUS_NO = ORDER_CUSTOMER_NO

    LEFT JOIN SYATTRIB TAXDS ON TAXDS.ATTRIB_FILENAME = 'CPORDHDR' AND TAXDS.ATTRIB_REF_NUMBER = SYATTRIB.ATTRIB_REF_NUMBER AND TAXDS.ATTRIB_CODE = '_AVATAXDS'     

    WHERE SYATTRIB.ATTRIB_FILENAME = 'CPORDHDR'  AND SYATTRIB.ATTRIB_CODE = '_AVATAXSNAPSHOT' AND SYATTRIB.ATTRIB_CHKBOX_2 = 'Y'            

    ORDER BY CPORDHDR.ORDER_CUSTOMER_NO ASC         , CPORDHDR.ORDER_NO ASC

The bold column names above (ATTRIB_AMT_1 and ATTRIB_AMT_2) need to be generated with their alternate table names (TAXDS.ATTRIB_AMT_1 and TAXDS.ATTRIB_AMT_2).

The Workaround

Fortunately, there is a simple workaround.  Simply check the box as shown below and Save the template. 
Note that the following illustration is for a different report, just to show the location of the checkbox.


This will result in the correct syntax for the generated SELECT statement:

  1. SELECT ORDER_CUSTOMER_NO, ORDER_SHIP_TO_NO, ORDER_SHIP_TO_NAME, ORDER_SHIP_TO_CITY, ORDER_SHIP_TO_ST, ORDER_SHIP_TO_ZIPCD, CUS_TP, CUS_TXBL_FG, ORDER_NO, ORDER_DATE, ORDER_TOTAL_SALE_AMT, TAXDS.ATTRIB_AMT_1, TAXDS.ATTRIB_AMT_2 FROM SYATTRIB

    LEFT JOIN CPORDHDR ON ORDER_NO = SYATTRIB.ATTRIB_REF_NUMBER

    LEFT JOIN ARCUSFIL ON CUS_NO = ORDER_CUSTOMER_NO

    LEFT JOIN SYATTRIB TAXDS ON TAXDS.ATTRIB_FILENAME = 'CPORDHDR' AND TAXDS.ATTRIB_REF_NUMBER = SYATTRIB.ATTRIB_REF_NUMBER AND TAXDS.ATTRIB_CODE = '_AVATAXDS'     

    WHERE SYATTRIB.ATTRIB_FILENAME = 'CPORDHDR'  AND SYATTRIB.ATTRIB_CODE = '_AVATAXSNAPSHOT' AND SYATTRIB.ATTRIB_CHKBOX_2 = 'Y'            

    ORDER BY CPORDHDR.ORDER_CUSTOMER_NO ASC         , CPORDHDR.ORDER_NO ASC


Author: JEG
Program: UDRDesign

    • Related Articles

    • How to Investigate or Debug Report Desk Problem by Using SQL Statement

      Released Date: 1/25/2025 Version: 8.6 & Higher Report Desk is based on the PSQL relational engine. It sends a SQL statement to the PSQL engine to retrieve data. Sometimes, you don't get the result back as you expected, so what next? For example, when ...

    • Developing a New Elliott V8.6 Report Desk User Defined Report (UDR)

      'Release Date: 6/20/2018 Version: 8.6 & Up This document is written for licensed Elliott Software developers. It is not relevant for regular Elliott users. Overview With proper licensing, Elliott developers can follow these three steps to create a ...

    • How to Investigate or Debug Report Desk Problem by Using Registry Keys

      Release Date: 01/25/2025 Version: 8.6 & Up Report Desk is based on the PSQL relational engine. It sends a SQL statement to PSQL engine to retrieve data. Sometimes, you don't get the result back that you expected, so what next? For example, when you ...

    • Data Structures for Report Desk Defaults and Enforcements

      Release Date: 9/19/2022 Version 8.6 and Higher In Report Desk, users can save their input values, as well as output option in templates and re-use them in the future. On the first Report Desk screen, the system prompts you to input the selection of ...

    • Report Desk: Customizing Reports

      Release Date: 4/28/23 Version: 8.6 Customizing Reports Once a user has been given rights to modify Elliott Report Desk User Design Reports (UDR), they will have access to two additional buttons on the report parameter screens. SQL: This button will ...