string concatenation 1

[yan19454]

according to this :

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

I created my function.

create FUNCTION dbo.combine_cptcode
(@pid As char(8))
RETURNS varchar(1000)
AS
BEGIN

DECLARE @cptcode AS varchar(1000)
SET @cptcode = ''
SELECT @cptcode = case @cptcode when '' then BILLINGHC
else
@cptcode + ','+ BILLINGHC
END
from CPTCodeop where PatientID=@pid order by BILLINGHC


RETURN @cptcode

end


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


Thx. it has results , but the results are not my expected. but the BILLINGHC is not rowset string concatenation . I looked at my function is nothing different from the example.

select distinct PatientID, dbo.combine_cptcode(PatientID) from CPTCodeop group by PatientID

Thx.

 

[AlexCuse]

I have not tested this yet, but I think this is your problem

SET @cptcode  = ''

If @cptcode is ALWAYS going to be '', then why do you need a CASE to test for it??

This is just my first guess. How do your results look (I imagine always simply showing the column BILLINGHC?)

Good Luck,

Alex

ps- try using [ignore]

type some code

[/ignore] tags, to make your code more readable. They will give this result:

type some code

Ignorance of certain subjects is a great part of wisdom

 

[yan19454]

I do not know . I just modified the function according to my fields. The example come out the result right.


select patientid, billinghc from CPTCodeop
output.
40000028 80061
40000028 83735
40000028 84100
40000028 84436
40000028 84443
40000028 84479
40000028 84480
40000028 85025
40000028 86617
40000028 86618
40000069 83615

select distinct PatientID, dbo.combine_cptcode(PatientID) from CPTCodeop group by PatientID
return
40000028 86618
40000069 86665

I do not know how to test the function. sql does not give me the line by line option to test.

 

[gmmastros]

What is the data type for BILLINGHC ? I'm guessing integer.

Try this...

create   FUNCTION dbo.combine_cptcode 
  (@pid As char(8)) 
RETURNS varchar(1000) 
AS 
BEGIN 

  DECLARE @cptcode  AS varchar(1000) 
  SET     @cptcode  = '' 
  SELECT  @cptcode = case @cptcode when '' then BILLINGHC 
                    else 
                    @cptcode + ','+ [!]Convert(VarChar(20), [/!]BILLINGHC[!])[/!]
                       END 
         from CPTCodeop  where PatientID=@pid order by BILLINGHC 
    

RETURN @cptcode 

end

If my assumption is correct that BILLINGHC is an integer, then you should convert it to a string before concatenating it with another string.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[yan19454]

it is varchar(5). I modified according to your suggestion.the result is the same. no string concatenating

 

[AlexCuse]

That is interesting. I set up a test, and got no results with an integer datatype column, and when changing to varchar I got the desired results. Here they are:

40000028	80061,83735,84100,84436,84443,84479,84480,85025,86617,86618
40000069	83615

Is that what you were expecting?


Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Also, is it possible you have NULL values in BILLINGHC column?

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

I just created a table with the same data that you show in your previous post. I also created the user defined function. Everything worked as expected.

I get:

[tt][blue]
PatientID
--------- ------------------------------------------------40000028 80061,83735,84100,84436,84443,84479,84480,85025,86617,86618
40000069 83615
[/blue][/tt]

Is there more to the query that you are not showing?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Alex, good point about the NULLs.

Try this...

create   FUNCTION dbo.combine_cptcode 
  (@pid As char(8)) 
RETURNS varchar(1000) 
AS 
BEGIN 

  DECLARE @cptcode  AS varchar(1000) 
  SET     @cptcode  = '' 
  SELECT  @cptcode = case @cptcode when '' then BILLINGHC 
                    else 
                    @cptcode + ','+ Convert(VarChar(20), BILLINGHC)
                       END 
         from  CPTCodeop  
         where PatientID=@pid 
               [!]And BILLINGHC Is Not NULL[/!]
         order by BILLINGHC 
    

RETURN @cptcode 

end

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Wow George, I was a minute ahead of you for once ;-)

yan- why not post the results that you are getting (along with full query), this would be very helpful to us.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[yan19454]

I rebooted my computer.
I reran my query. it still the same result. it is my full query. it is not null.

select * from CPTCodeop where BILLINGHC is null
return 0 record.

select distinct PatientID, dbo.combine_cptcode(PatientID) from CPTCodeop group by PatientID

I
return
40000028 86618
40000069 86665
40000101 86140
40000168 86618
40000184 93005
40000192 85025
40000200 85730
40000283 80061
40000432 83036
40000440 86618
40000515 G0283
40000549 85610
40000556 85025
40000614 86618
40000648 97150
40000655 83036
40000671 85025
40000739 93005

it should be (49092 row(s) affected)

I also modified my function according to gmmastros's suggetion.


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



ALTER FUNCTION dbo.combine_cptcode
(@pid As char(8))
RETURNS varchar(1000)
AS
BEGIN

DECLARE @cptcode AS varchar(1000)
SET @cptcode = ''
SELECT @cptcode = case @cptcode when '' then BILLINGHC
else
@cptcode + ','+ Convert(VarChar(20), BILLINGHC )
END
from CPTCodeop where PatientID=@pid And BILLINGHC Is Not NULL
order by BILLINGHC




RETURN @cptcode

end

 

[yan19454]

I just create the same thing with just two records which I extracted from the table.
It worked fine but with all the data , it did not work.

Here is my table definition.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CPTCODEOP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CPTCODEOP]
GO

CREATE TABLE [dbo].[CPTCODEOP] (
[PATIENTID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PATIENTIDNAME] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BILLINGHC] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOt NULL ,
[BILLINGHCNAME] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CASES] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BILLINGHCPCS] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DISCHARGEDATE] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRIMARYAPCCODE] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


Thx.

 

[gmmastros]

yan,

This is weird, but I do have an idea. Please run this query and post back the results.

[tt][blue]
Select Column_Name,
Data_Type,
Character_Maximum_Length
From information_schema.Columns
Where Table_Name = 'CPTCodeop'
And Column_Name = 'PatientId'
[/blue][/tt]

I'm thinking that the PatientId column may not be char(8) like the parameter you have to your function.

Also, you could try calling the function with a single parameter to see what the output is.

[tt][blue]select dbo.combine_cptcode('40000028')[/blue][/tt]

And finally, you could try....

ALTER    FUNCTION dbo.combine_cptcode 
  (@pid As char(8)) 
RETURNS varchar(1000) 
AS 
BEGIN 

  DECLARE @cptcode  AS varchar(1000) 
  SET @cptcode  = '' 
  SELECT @cptcode =   case  @cptcode  when '' then BILLINGHC 
                    else 
                    @cptcode + ','+ Convert(VarChar(20), BILLINGHC  ) 
                       END 
         from CPTCodeop  where [!]RTrim([/!]PatientID[!])[/!]=[!]RTrim([/!]@pid[!])[/!]  And BILLINGHC Is Not NULL
 order by BILLINGHC 
    

            

RETURN @cptcode 

end

Also, you don't need the Group By in the query and the distinct may be causing some problems, so you could try...

select PatientID, dbo.combine_cptcode(PatientID) 
from   (
       select distinct PatientID
       from   CPTCodeop
       ) As A
Order By PatientId

Also, does the 19545 in your handle represent the zipcode that you live or work. (i'm in Pottstown, PA).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Well, I think we found the culprit.

[PATIENTID] [varchar] (255)

You should change the function to...

ALTER    FUNCTION dbo.combine_cptcode 
  (@pid As [!]varchar(255)[/!]) 
RETURNS varchar(1000) 
AS 
BEGIN 
  etc....

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I forgot to mention...

If changing the paramter fixes the problem, then you should remove the RTrim's that I suggested in a previous post and then test again to see if the function still returns the correct records.

I mention this because with the RTrim's, you will get a table scan (which is slow). Without the RTrim's, you would be able to use an index on the PatientId column (which would speed up performance).



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[yan19454]

After the trim, it worked. as you know , it is table scan. it took long time. might be the data pid different while I imported the table over. but it did have patient. If I just changed the paramter. it did not work. one odd thing.

I used the
select * into CPTCODEOP from CPTCODEOPo where patientid='40000028' or patientid='40000069' to create the test data. it work. If I run select dbo.combine_cptcode('40000028') with the origin table. it did not work. Why ?

19454 is where I live. I went Pottstown before. I like outdoor activities. I went to Kayak that way in summer time.

 

[gmmastros]

What is the data type for the PatientId column in the real table. Is it char(8) or something else. I think the problem is that the where clause within the function is causing your problems.

Since your parameter is Char(8), if you pass a char(9) to the function, it will get truncated to 8 characters.

Ex.
[tt][blue]
Declare @T1 Char(8)

Set @T1 = '123456789'

Select @T1
[/blue][/tt]

Notice how the 9 is dropped from the end?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[yan19454]

but when I rename the CPTCODEOP as CPTCODEOpo and use

select * into CPTCODEOP from CPTCODEOPo where patientid='40000028' or patientid='40000069'

to test.
it create the some data table structure. the function work.

Thx.

 

[AlexCuse]

You raelly need to post your results, as well as how they differ from what was expected.

I did a little more testing, and anything with character length > 8 will cause the function to return an empty string (as it should)

Is this what you are experiencing?

why not run this query at least, and post the results

select len(PatientID), count(PatientID) as CNT 
from cptCodeOp
group by len(PatientID)

GoodLuck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[yan19454]

select len(PatientID), count(PatientID) as CNT
from cptCodeOp
group by len(PatientID)
return:

8 192638