CTE-common table EXP

Question

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'?

Answer 1

At a rough guess, some of your data in the ldd.Value is non-numeric.

If you RTFM at http://msdn.microsoft.com/en-us/library/ms181765.aspx, 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...

CTE-common table EXP

table

Follow this question

Related Questions