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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy