java中replaceselect_Select replace and then join

I have two tables that I want to combine.

The first table has a key record column ITEMID where the key records have the suffix ITEMID_CV, and the table contains one other column of data that I want. The second table has the same key record but with a different suffix ITEMID_PH and the rest of the columns of data.

For the first table, I can use:

SELECT

REPLACE (ITEMID,'CV','PH') AS ITEMID,

CAST (ENDDATE AS DATETIME) AS ENDDATE

FROM PLTReports

Which gives me the ITEMID name that is the same as the second table. So I'm partway there. Now what I don't know how to do is to join this result onto the rest of the other table. The following gives me an ambiguous column name error. The error is: Error --------------------

Unable to link to external table: Unexpected Server Error: 'Deferred prepare could not be completed. Statement(s) could not be prepared. Ambiguous column name 'ITEMID'.'. Use Event Viewer to see the PI AF Server log file for more information.

I think I might be close to solving this, but can't really find an example of how to do it, and I've probably used the wrong syntax.

SELECT

REPLACE (ITEMID,'CV','PH') AS ITEMID,

CAST (ENDDATE AS DATETIME) AS ENDDATE

FROM PLTReports

INNER JOIN PRODUCTION_ALL ON PRODUCTION_ALL.ITEMID = PLTReports.ITEMID

Does anyone have a strategy on how to do this? It's probably simple but I'm just not quite getting there.

Edit: This is for creating a linked table in PI AF, I can't recall offhand what flavor of SQL it uses.