April 23, 2020 at 3:29 pm
Hey Folks, did. a little digging around however I was not able to find a simple solution for this.
I have a column called [Composite Med Procedure ID] that is being SUBSTRINGED to remove erroneous data and renamed to [Unique CPT]. I want to then use the results in the JOIN clause to bring in the description from another table. If Don't rename [Composite Med Procedure ID] then the SQL results display NULL value in Description because it's not using the SUBSTRINGed Values. I then tried renaming the [Composite Med Procedure ID] to [Unique CPT] but for obvious reasons the JOIN clause fails and indicates the Column doesn't exist. I get why
So how can I use the results from the SUBSTRING in the JOIN Clause
Thank you!
SELECT DISTINCT A.[Organization Name],
SUBSTRING(A.[Composite Med Procedure ID],4,5) [Unique CPT],
B.DESCRIPTION
FROM dbo.CCN_837 A
LEFT JOIN dbo.CPT_CODES B ON [Unique CPT]=[HCPCS]
April 23, 2020 at 3:32 pm
Sorry figured it out. I guess I needed to perform the substring in the JOIN clause DUH.
SELECT DISTINCT A.[Organization Name],
SUBSTRING(A.[Composite Med Procedure ID],4,5) [Unique CPT],
B.DESCRIPTION
FROM dbo.CCN_837 A
LEFT JOIN dbo.CPT_CODES B ON SUBSTRING(A.[Composite Med Procedure ID],4,5) = [HCPCS]
April 24, 2020 at 10:11 am
CROSS APPLY can be used to make the multiple use of the same expression more readable:
SELECT DISTINCT N.[Organization Name],
X.UniqueCPT,
C.[DESCRIPTION]
FROM dbo.CCN_837 N
CROSS APPLY ( VALUES(SUBSTRING(N.[Composite Med Procedure ID], 4, 5)) ) X (UniqueCPT)
LEFT JOIN dbo.CPT_CODES C
ON X.UniqueCPT = C.HCPCS;
Also, put an alias against all columns and try to avoid spaces in column names.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply