Convert char var to integer for use in join

I am trying to get a report to run through crystal using our postgres databse.

My SQL looks like

SELECT 
    slcnote.note_date, 
    slcnote.account, 
    customer.name, 
    slcnote.reference,
    slcnote.operator,
    salesman.name,  
    slcnote.system_date,
    slcnote.note_type, 
    slcnote.note_note,
                CAST(salesman.code as integer)
FROM   
   slcnote
    Left JOIN customer ON slcnote.account=customer.account
    left JOIN salesman ON slcnote.operator=salesman.code
 ORDER BY "slcnote"."note_date"

However it does not seem to be converting the salesman code to an integer. I am encountering the following error message in Crystal Reports:

--------------------------- Crystal Reports --------------------------- 
Failed to retrieve data from the database. 
Details: 42883:
ERROR: operator does not exist: integer = character varying; 
Error while executing the query 
[Database Vendor Code: 7 ] 
--------------------------- OK ---------------------------

Thanks for help

Chris

Answer

Your issue is that you cast salesman.code to an integer in the SELECT clause, but not in the ON clause. Therefore, the query is trying to join tables by comparing an integer to a VARCHAR, which generates the error message.

To solve your immediate problem, you need to perform the cast in your ON clause:

SELECT * 
FROM slcnote
    LEFT JOIN salesman ON CAST(salesman.code AS INT) = slcnote."operator"

As noted in comments, however, performance of this query will be abysmal. If you can’t change the table, are you able to create a materialized view that performs the cast? This might help. You should also see if the individual who is able to change the table will do so, citing these performance issues.