SQL Query - The multi-part identifier "CONTACT1.ACCOUNTNO" could not be bound

Version 1

    Details

    SQL Query - The multi-part identifier "CONTACT1.ACCOUNTNO" could not be bound

    [STEPS TO REPRODUCE]
    - Global Support recommend using a demo database
    1. Have a SQL query available, one of the easiest possibilities for this matter is simply

    SELECT C1.CONTACT FROM CONTACT1 c1
    WHERE C1.CONTACT LIKE '%Lauren%'
    ORDER BY C1.CONTACT

    2. Tools > SQL Query
    3. Copy and paste the query into the query pane
    4. Button Query

    [RESULT]
    - An error message appears 'The multi-part identifier "CONTACT1.ACCOUNTNO" could not be bound' although neither ACCOUNTNO nor CONTACT1.ACCOUNTNO is part of the SELECT clause





    Resolution

    [CAUSE]
    - When this query is submitted to the back end, GoldMine/MS SQL Server amends the query as below. This is a preventive action as ACCOUNTNO is the link column/field between the contact related tables.

    SELECT  C1.CONTACT,CONTACT1.ACCOUNTNO FROM CONTACT1 c1 WITH (NOLOCK) WHERE C1.CONTACT LIKE '%Lauren%' ORDER BY C1.CONTACT   

    - This is incorrect as it needs either not to be referenced with its SQL table alias at all (...,ACCOUNTNO ...) which is possible as only one single table is queried in this example, or it requires the correct SQL table alias reference (... ,C1.ACCOUNTNO...) which would be necessary as soon as the SQL query includes further tables.


    [RESOLUTION]
    - Add the ACCOUNTNO column proactively into the SELECT statement
    - It is recommended always to add the ACCOUNTNO field  with its SQL table alias when an alias is used (SQL aliases are used to give a database  table, or a column in a table, a temporary name)
    - For the above example the recommended query is then

    SELECT  C1.CONTACT,C1.ACCOUNTNO FROM CONTACT1 c1
    WHERE C1.CONTACT LIKE '%Lauren%'
    ORDER BY C1.CONTACT   




    - Alternatively for this specific example (as only the CONTACT1 table is used) also the following query does also work, but we strongly recommend wherever setup to use the SQL table alias

    SELECT  C1.CONTACT,ACCOUNTNO FROM CONTACT1 c1
    WHERE C1.CONTACT LIKE '%Lauren%'
    ORDER BY C1.CONTACT