Set variable to different values 1

[mcs1169]

Hi all. I am a first time poster and a relative neophyte when it come to SQL stored procedures.

I have an existing SP that I am trying to modify. The existing SP gives me all of the latest weights on a group of patients in our hospital. I am trying to add the patient's heights as well. The values are stored in metric so I have to convert them to imperial values to display on my report. I am getting errors that I do not know what to do with. Any help is appreciated.

SELECT
CV3ClientVisit.ChartGUID, 
CV3ClientVisit.ClientGUID, 
CV3ClientVisit.ClientDisplayName,
CV3ClientVisit.CurrentLocation,
CV3ClientVisit.ProviderDisplayName,
CV3ClientVisit.InternalVisitStatus,
CV3Client.BirthYearNum,
CV3Client.BirthMonthNum,
CV3Client.BirthDayNum,
CV3Client.GenderCode,
CV3Client.IDCode,
CV3Client.IsMaterialized,
CV3VisitListJoin_R.ObjectGUID,
CV3PhysicalNoteDeclaration.TouchedWhen, 
CV3PhysicalNoteDeclaration.Text,
CV3PhysicalNoteDeclaration.TypeCode,
CV3Order.GUID,
CV3Order.Name,
CV3Order.RequestedDTM,
CV3Order.RequestedTime,
CV3Order.FrequencyCode,
CV3Order.SummaryLine
FROM
 CV3ClientVisit 
INNER JOIN CV3VisitListJoin_R
		ON ( CV3VisitListJoin_R.ObjectGUID = CV3ClientVisit.GUID
			 AND CV3VisitListJoin_R.JobID = @JobID )
INNER JOIN CV3Client
		ON ( CV3ClientVisit.ClientGUID = CV3Client.GUID )
LEFT OUTER JOIN  CV3PhysicalNoteDeclaration ON
        CV3ClientVisit.GUID = CV3PhysicalNoteDeclaration.ClientVisitGUID AND
	CV3PhysicalNoteDeclaration.TypeCode = 'WEIGHT'
LEFT OUTER JOIN CV3Order ON
	CV3ClientVisit.GUID = CV3Order.ClientVisitGUID AND
	CV3ClientVisit.ClientGUID = CV3Order.ClientGUID AND
	CV3ClientVisit.ChartGUID = CV3Order.ChartGUID AND 
	(Name = 'Weigh patient daily' OR
	Name = 'Weigh patient daily: dialysis' OR
	Name = 'Weigh patient daily: TPN' OR
	Name = 'Weigh patient weekly: tube feeding' OR
	Name = 'Weigh patient daily: CHF' OR
	Name = 'Weigh patient')
 	AND
 	CV3Order.TouchedWhen = ( Select max(TouchedWhen) 
                                  from  CV3Order 
				  where 
					CV3ClientVisit.GUID = CV3Order.ClientVisitGUID AND
					CV3ClientVisit.ClientGUID = CV3Order.ClientGUID AND
					CV3ClientVisit.ChartGUID = CV3Order.ChartGUID AND
					(Name = 'Weigh patient daily' OR
					Name = 'Weigh patient daily: dialysis' OR
					Name = 'Weigh patient daily: TPN' OR
					Name = 'Weigh patient weekly: tube feeding' OR
					Name = 'Weigh patient daily: CHF' OR
					Name = 'Weigh patient')
				   	AND CV3Order.Status = 'Active' )


IF CV3PhysicalNoteDeclaration.TypeCode = 'WEIGHT' 
     set Text = Cast (Round((Cast(CV3PhysicalNoteDeclaration.Text as Float)/1000 * 2.2046244201837774916665196917053),2) as VarChar (10)) + ' LB'
Else
     set Text = Cast (Round((Cast(CV3PhysicalNoteDeclaration.Text as Float) * 0.3937),2) as VarChar (10)) + ' IN'

With the above code, I get the following error

Server: Msg 170, Level 15, State 1, Procedure FH_2Weights_R_PR, Line 92
Line 92: Incorrect syntax near '='.


If I try and use @Text, I get an error that I need to declare @Text. I added

DECLARE @TEXT Varchar(10)

between the AS and SELECT statements but then received the error


Server: Msg 107, Level 16, State 3, Procedure FH_2Weights_R_PR, Line 91
The column prefix 'CV3PhysicalNoteDeclaration' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Procedure FH_2Weights_R_PR, Line 92
The column prefix 'CV3PhysicalNoteDeclaration' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Procedure FH_2Weights_R_PR, Line 94
The column prefix 'CV3PhysicalNoteDeclaration' does not match with a table name or alias name used in the query.


I know that this is lengthy so my appologies for that. Any help is appreciated.

Thank you...

Mitch

 

[vongrunt]

<very long SELECT>

IF CV3PhysicalNoteDeclaration.TypeCode = 'WEIGHT' 
...

Nope, won't work that way. First put TypeCode from <very long SELECT> into @variable, then compare it with 'WEIGHT'.

Btw. just curious... what is 2.2046244201837774916665196917053? lb/kg?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[mcs1169]

Vongrunt,

Thank you for the reply. Remember, I am a relative neophyte so please be patient.

I put

DECLARE @Text varchar(10)
DECLARE @Type varchar

above the SELECT statement and modified the compare to

SET @Type = CV3PhysicalNoteDeclaration.TypeCode
IF @Type = 'WEIGHT'
     set @Text = Cast (Round((Cast(CV3PhysicalNoteDeclaration.Text as Float)/1000 * 2.2046244201837774916665196917053),2) as VarChar (10)) + ' LB'
Else
     set @Text = Cast (Round((Cast(CV3PhysicalNoteDeclaration.Text as Float) * 0.3937),2) as VarChar (10)) + ' IN'

I still get the MSG107 errors noted above.

In regards to your curiosity question, 2.2046244201837774916665196917053 is the grams -> pounds conversion. The original SP was done by a Canadian SQL programmer. This is the conversion factor that she used to get the most accurate value for the weight. I would love to actually report in pounds/ounces but she did not do that for us.

 

[vongrunt]

OK then... SQL works with sets of data. When you do SELECT, results may contain more than one row. According to your description that's exactly the case here. @variables accept only single scalar value and cannot be used for that purpose.

You can try to incorporate conversion into SELECT:

SELECT
CV3ClientVisit.ChartGUID,
...
CV3PhysicalNoteDeclaration.TouchedWhen, 
[b]Case When CV3PhysicalNoteDeclaration.TypeCode = 'WEIGHT' 
	Then Cast (Round((Cast(CV3PhysicalNoteDeclaration.Text as Float)/1000 * 2.2046244201837774916665196917053),2) as VarChar (10)) + ' LB'
	Else Cast (Round((Cast(CV3PhysicalNoteDeclaration.Text as Float) * 0.3937),2) as VarChar (10)) + ' IN'
	End,[/b]
CV3PhysicalNoteDeclaration.TypeCode,
...
-- remove IF/Else code...

At this point you may encounter another problem: column [Text] is obviously string - char, varchar or maybe even text. There are no guarantees it always holds numeric value. When that assumption gets violated, CAST() crashes and query stops.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[mcs1169]

Vongrunt,

Thank you so much for your help. The last trick did it. I GREATLY appreciate the help given.

Thanx...

Mitch