0

I have the following code to pull a number of values from several rows into a single column and pipe-delimit the values.

SELECT DISTINCT P2.PatientID, 
SUBSTRING(
    (
        SELECT '| '+ CAST(P1.ProcedureCode AS NVARCHAR(MAX))  AS [text()]
        FROM #Proc50 P1
        WHERE P1.PatientID = P2.PatientID
        ORDER BY P1.PatientID
        FOR XML PATH ('')
    ), 2, 1000) [ProcedureCodes]
INTO #Proc_Concat
FROM #Proc50 P2

I then have a dataset that looks like this:

PatientId ProcedureCodes

1111111 11100| 17271| 1CHKS| 1CHN1| 1CHNS| 36415| 51798| 69210| 70450| 70544

When I try to insert the values into the final table I receive the conversion error. I tried casting the value but that hasn't helped. I also created a table for insertion where the field was nvarchar.

I am using SQL Server 2018. Any help would be appreciated.

EDIT to answer questions: Here is the definition of #Proc50 enter image description here

Both P1.PatientId and P2.PatientId are int

For the code that I am using to insert the results of the pipe-delimited procedure, I am creating a table for the insertion.

    CREATE TABLE dbo.tmpWellMedTest (
                    PatientId int not null
                    ,Lastname  varchar(30) not null
                    ,Firstname  varchar(30) not null
                    ,ProcedureCodes nvarchar(MAX) null
                    ,Diagnosis1 varchar(10) null
                    ,Diagnosis2 varchar(10) null
                    ,Diagnosis3 varchar(10) null) 

Then the insertion script:

SELECT
 ME.PatientId
,ME.LastName
,ME.FirstName   
,P.ProcedureCodes
,ISNULL(ME.Diagnosis1,'')                AS Diagnosis1
,ISNULL(ME.Diagnosis2,'')                AS Diagnosis2
,ISNULL(ME.Diagnosis3,'')               AS Diagnosis3
FROM vwPatientDemographic ME
LEFT OUTER JOIN #Proc_Concat        P
    ON P.LbPatientId = P.ProcedureCodes
  • I just edited my original post to try and answer your questions. Thanks. – FinChase Sep 19 '19 at 16:30
  • Still missing definition of LbPatientID – Piotr Sep 19 '19 at 16:32
  • That was a typo. That was the column's old name. I've edited it to show the correct name, PatientId. – FinChase Sep 19 '19 at 16:43
0

On of the following statements use implicit conversion from int to varchar:

 ON P1.PatientID = P2.LbPatientID
or
 ON P.LbPatientId = P.ProcedureCodes

We know that PatientID is int and ProcedureCodes is varchar. Type for LbPatientID is not clear from your post.

  • LBPatientId was a typo--the column's old name. It's the same as PatientId. – FinChase Sep 19 '19 at 16:44
  • Then join "LEFT OUTER JOIN #Proc_Concat P ON P.LbPatientId = P.ProcedureCodes" doesn't make sense, another typo? – Piotr Sep 19 '19 at 16:47
  • You're right, I just saw that myself. I've corrected it and it is running. Thanks! – FinChase Sep 19 '19 at 16:51
  • Looking at what you posted I just spotted my stupid mistake. Thanks for taking the time to look at this! – FinChase Sep 19 '19 at 16:52
1

Since #Proc_Concat and #Proc50 only have two columns in play here, PatientID and ProcedureCodes, one of them is throwing the error.

Guessing not ProcedureCodes as it's pipe-delimeted so must be a varchar.

That leaves PatientId. Numeric data types can't handle the space character '1111111 11100', so that's the first place I'd look.

Another possibility is within your subquery, P1.PatientID = P2.LbPatientID, in case one is a varchar and one is an int, and implicit conversion won't allow the varchar value to be converted to an int to test the equality.

Good luck.

  • Actually, it is ProcedureCodes that's throwing the error. – FinChase Sep 19 '19 at 16:20
  • Ok. Since both columns contain non-numeric values, unless you want to lose the space in PatientID and handle it in your reporting layer, then both will need to be varchar's. ID usually implies numeric anyways, unless you have some company-specific rules that say otherwise. – Jim Horn Sep 19 '19 at 16:25
  • I just spotted my stupid mistake. Thanks for taking the time to look at this! – FinChase Sep 19 '19 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.