asked
CTE-common table EXP
WITH CTE1 (name ,studentID ,name ,date ,rmg) AS ( SELECT studentID ,name ,date ,CASE WHEN (ISNUMERIC(REPLACE(ldd.Value, '%', '')) =0) THEN 'NONE' WHEN (CONVERT(FLOAT,REPLACE(ldd.Value, '%', '')) > 9.0) THEN 'HIGH' WHEN (CONVERT(FLOAT,REPLACE(ldd.Value, '%', '')) < 7.0 ) THEN 'LOW' WHEN (CONVERT(FLOAT,REPLACE(ldd.Value, '%','')) BETWEEN 7.0 AND 9.0 ) THEN 'MEDIUM' WHEN (ISNULL(ldd.Value,'') = '') THEN 'NONE' END rmg FROM student ) SELECT name ,studentID ,name ,date ,rmg FROM CTE1 thr where date =(SELECT MAX(date) FROM CTE1 thr1 WHERE thr1.studentID=thr.StudentID) order by name,date ; when i try to get only the values which shows 'HIGH' it gives me an error: Converting data type varchar to float. SELECT name ,studentID ,name ,date ,rmg FROM CTE1 thr where date =(SELECT MAX(date) FROM CTE1 thr1 WHERE thr1.studentID=thr.StudentID) AND rmg='HIGH' order by name,date ; HOW can i get only the records which are 'HIGH'?
Do you have any sample data which can reproduce the error? I don't get why you declare column names in the WITH-clause of your CTE-declaration, and then use other columns inside the CTE. Fourth column name is named value in the WITH-clause, but rmg in the body.
At a rough guess, some of your data in the `ldd.Value` is non-numeric. If you RTFM at , you'll see that what you are doing with your `CASE` statement is a "searched expression", which means that the individual `WHEN` clauses are evaluated in order until a match is found. What you might want to do is to rethink the order in which these `WHEN` statements are written, so that the first ones identify data that can't be dealt with (non-numerics, blanks, empty strings, nulls, character data etc), before you get to the good stuff. OK, it's almost certain to knock back the speed of execution, but if you can't guarantee the quality of the data within the CTE, then it's a way to go. The alternative is, of course, to put a `WHERE` clause within the CTE to exclude this data...
