calculation in select query with case in mysql.

[pritesh2010]

HI
all Experites can you help me on this Query How to get perfect result set
I have three table.

1)vitalsigns
ID	DisplayName		DisplayShort	ToMetrics			ToEnglish
1	Temp			Temp			(0.556*({1}-32))	({1}/0.556)+32
2	BP(Systolic)	BPH				(NULL)				(NULL)
3	Wt				WtH				{3}/2.2				{3}*2.2
4	Height			Height			2.54*{4}			{4}/2.54
5	Pulse			Pulse			(NULL)				(NULL)
6	Respiratory		Respiratory		(NULL)				(NULL)
7	Body Mass Index	BMI				(NULL)				(NULL)
8	Wt				WtL				{8}*28.3			{8}/28.3
9	BP(Diastolic)	BPL				(NULL)				(NULL)

2)xrefvitalsigns
vitalsignID		Value	TypeME
3				40		E
1				30		E
2				0		E
9				0		E
8				0		E
4				8.5		E
5				40		E
6				50		E
7				0		E
1				28		M
2				0		M
9				0		M
3				60		M
8				0		M
4				10.5	M
5				0		M
6				0		M
7				0		M

now, I want calculate the result based on this Like if my value come in matric then in query i want to calcultae in English
something like this

Select vitalsigns.DisplayShort,vitalsigns.ToEnglish,xrefvitalsigns.Value,xrefvitalsigns.TypeME 
			From xrefvitalsigns INNER JOIN vitalsigns 
			 ON xrefvitalsigns.vitalsignID=vitalsigns.ID;

//now i want to calculate in query if my value Type me is in metric then convert it to english see the query and tell me the proper ans.
ho w to do it.			 
Select  Case WHEN vitalsigns.DisplayShort='Temp' And xrefvitalsigns.TypeMe='M' Then ((xrefvitalsigns.Value/0.556)+32)
			 WHEN  vitalsigns.DisplayShort='WtH' AND xrefvitalsigns.TypeMe='M' THEN (xrefvitalsigns.Value*2.2) 	
			 END AS VALUE
			 From xrefvitalsigns INNER JOIN vitalsigns 
			 ON xrefvitalsigns.vitalsignID=vitalsigns.ID
		Where vitalsigns.DisplayShort = 'WtH';

[smantscheff]

To the best of my knowledge MySQL does not have macro or eval capabilities. This means that there is no built-in parser which can evaluate a string expression as program code. You will have to supply this parser yourself.

I'd recommend you write a function named "convert" or the like which parses the ToMetrics and ToEnglish field and returns the calculation result.

The alternative is to hard-code the calculation into the query and to keep the ToEnglish and ToMetric fields as reference only, not used in the actual calculation, like you do in your code.

You can actually use the CASE construct directly as a calculated field, much like you did already:

Select 
vitalsigns.DisplayShort,
vitalsigns.ToEnglish,
xrefvitalsigns.Value,
xrefvitalsigns.TypeME,
CASE
WHEN vitalsigns.DisplayShort='Temp' And xrefvitalsigns.TypeMe='M' 
Then ((xrefvitalsigns.Value/0.556)+32)
WHEN  vitalsigns.DisplayShort='WtH' AND xrefvitalsigns.TypeMe='M' 
THEN (xrefvitalsigns.Value*2.2) 	
END AS VALUE
From xrefvitalsigns INNER JOIN vitalsigns 
ON xrefvitalsigns.vitalsignID=vitalsigns.ID;

[pritesh2010]

hi, smantscheff
thanks for replay i know i cant do it in this query.
I'd solved this problem as i have written there in query with small change.
change is that in my program i'm going to check what i have selected (Like Temp,Wth, etc.) .
and based on that i'd created query.
can you tell me how to it with macros if i'm not using the mysql.

[smantscheff]

You could store the calculation formulas in a database table (as you do) and then generate your query from PHP (or any other programming language with a MySQL interface) so that the query contains those formulas as literal values - exactly as in my example query above. The only database language I know of which is capable of macro processing is CLIPPER but I doubt that there is anyone around here old enough to have heard of it.