CODEWITHSUNDEEP
sqlsql-server
Laserwolf
Laserwolf

Reputation: 21

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.

Upvotes: 2

Views: 747

Answers (4)

GMB
GMB

Reputation: 222582

If I understand you correctly, you would need to use REPLACE in the join condition. Also, fields having the same name need to be prefixed :

SELECT 
    PRODUCTION_ALL.* 
    CAST (PLTReports.ENDDATE AS DATETIME) AS ENDDATE 
FROM PLTReports 
INNER JOIN PRODUCTION_ALL ON PRODUCTION_ALL.ITEMID = REPLACE (PLTReports.ITEMID,'CV','PH')

Upvotes: 1

Doug Coats
Doug Coats

Reputation: 7107

Without too much input from you I think this is what youre after

SELECT
    x.ITEMID
    , x.ENDDATE
FROM
    (
        SELECT 
            REPLACE (ITEMID,'CV','PH') AS ITEMID, 
            CAST (ENDDATE AS DATETIME) AS ENDDATE 
        FROM PLTReports 
    ) x 
INNER JOIN PRODUCTION_ALL ON PRODUCTION_ALL.ITEMID = x.ITEMID

you could also do it this way which is probably infinitely better

; with cte AS 
    (
        SELECT 
            REPLACE (ITEMID,'CV','PH') AS ITEMID, 
            CAST (ENDDATE AS DATETIME) AS ENDDATE 
        FROM PLTReports 
    )
SELECT 
    cte.ITEMID
    , ENNDATE
    , PRODUCTION_ALL.*
FROM cte 
    INNER JOIN PRODUCTION_ALL ON cte.ITEMID = PRODUCTION_ALL.ITEMID

Upvotes: -1

erik258
erik258

Reputation: 16304

since both tables have an 'ITEMID' you need to specify which should be used by REPLACE(): 

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

But I think from what you're saying you also have to adjust PRODUCTION_ALL.ITEMID = PLTReports.ITEMID because as you say,

The second table has the same key record but with a different suffix

So you'd need to replace in the join condition, too:

PRODUCTION_ALL.ITEMID = REPLACE(PLTReports.ITEMID,'CV','PH')

This is regrettably inefficient as a temporary unindexed result set from the REPLACE will have to be generated; that's why it's rather unfortunate that your indexes don't match.

Upvotes: 0

Daniel E.
Daniel E.

Reputation: 2480

You almost had the solution : 

SELECT 
    REPLACE (ITEMID,'CV','PH') AS ITEMID, 
    CAST (ENDDATE AS DATETIME) AS ENDDATE 
FROM PLTReports 
INNER JOIN PRODUCTION_ALL 
    ON PRODUCTION_ALL.ITEMID = REPLACE(PLTReports.ITEMID,'CV','PH')

Upvotes: 1

Related Questions