How do I compare two columns from two different tables

Question

I am running a CREATE TABLE TBL AS SELECT statement as below. I want to write a CASE STATEMENT that will compare values from column X.PRESC_ID to values from column Y.PRSC_NPI and if there is match, it should INSERT to TBL.PRESC_ID, and for all the X.PRESC_ID that do not match with any value in Y.PRSC_NPI should be INSERTED to TBL.PRSC_NPI_N

CREATE  TABLE TBL (
      Col1,
      Col2,
      PRESC_ID,
      PRSC_NPI_N,
 AS 
 (
          SELECT  
                 Col1,
                 Col2,
                 PRESC_ID,
                 PRSC_NPI_N,
                 FROM   TBL2 X
    JOIN
        (SELECT CLAIM_ID,PRSC_NPI FROM TBL3) Y
                ON Y.CLAIM_ID  = Y.Col1

I have tried the one below but it is not working

CASE
WHEN X.PRESC_ID = Y.PRSC_NPI THEN TBL.PRESC_ID
ELSE TBL.PRSC_NPI_N
END

             

Answer 1

Seems you really want two CASE expressions, one for each result column. Something like

CASE WHEN X.PRESC_ID = Y.PRSC_NPI THEN X.PRESC_ID END AS PRESC_ID,
CASE WHEN NOT(X.PRESC_ID = Y.PRSC_NPI) THEN X.PRSC_NPI_N END AS PRSC_NPI_N