Contact Search Window - User Defined SQL query shows result in SQL Query and on the SSMS but remains empty in the Contact Search Center (CSC) 

Version 1

    Details

    Contact Search Window - User Defined SQL query shows result in SQL Query but remains blank in the Contact Search Center (CSC) 

    [STEPS TO REPRODUCE]
    - Global Support recommends using a demo database
    1. create an own SQL query, a simple for this matter is

    SELECT C1.CONTACT, C1.ACCOUNTNO 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. Query Button

    [RESULT]
    - As expected the Lauren Hall record information appears with the related ACCOUNTNO value as a result



    5. Go To > Search > Contact Search Center (CSC)
    6. Options Button
    7. Activate 'Show SQL query editor in Search Center'
    8. OK
    9. Edit Button
    10. Highlight all within the query pane and paste or overwrite it manually with the above sample query

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

    11. Preview Button

    [RESULT]
    - Although we know there is Lauren Hall as a result (this is verified already via the SQL Query feature), the Contact Search Center remains empty as if there is no record matching the criteria




    Resolution

    [CAUSE]
    - The SQL queries in the Contact Search Center require always the related RECID column in the SELECT statement


    [RESOLUTION]

    - Add the CONTACT1.RECID field into the SELECT statement
    - For the above example a correct query is then

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



    [ADDITIONAL NOTES]
    - Regardless which columns are selected in the Contact Search Center, ONLY the columns within the SELECT clause will be displayed at maximum in the Contact Search Center (it might be considerable to use instead a C1.* where possible or amend the SELECT clause to include also further desired columns)
    - This also applies when columns from further tables are set in the Contact Search Center (for example. from CONTACT2 or from CONTSUPP)



    vs.




    - The same applies vice versa, regardless how many columns are set within the SELECT clause ONLY the values related to the set columns in the current Contact Search Center view will be displayed.
    - Once SQL Table aliases are set it is strongly recommended to reference each field by the correct SQL table alias, although once a field is unique in the queried tables no alias is necessary for the specific column.