I have the following Union query:
SELECT DS.SOURCE_ID, DSD.COLUMN_NAME, 'N' AS MAP_FLAG
FROM CDIP.SV_DATA_SOURCE DS INNER JOIN CDIP.SV_DATA_SOURCE_DETAIL DSD ON DS.ID = DSD.DATA_SOURCE_ID WHERE DSD.VC_FLAG = 0
UNION SELECT SOURCE_ID, COLUMN_NAME, 'Y' AS MAP_FLAG FROM CDIP.SV_DEFAULT_SOURCE_MAPPING
What I am wanting to do is this:
There will be column names in the top part of the UNION that will exist also in the bottom CDIP.SV_DEFAULT_SOURCE_MAPPING table. If there is a record in the CDIP.SV_DEFAULT_SOURCE_MAPPING table then I want to have a flag that says MAP_FLAG = 'Y" to signify I have a mapped record otherwise if it only exists in the top part have it be 'N' but right now of course based on if it exists in both the UNION will create 2 records for each. There will be other records with different column names in the bottom part so those I want again flagged as 'Y' for the MAP_FLAG.
Hope this makes sense.