x

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

more ▼

asked Sep 16, 2011 at 03:44 PM in Default

avatar image

palum
249 27 29 33

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.

Sep 16, 2011 at 10:06 PM Magnus Ahlkvist

When i try using with the temp table by including the value coloumn in the select as well as in the CTE coloumn,i get those values, something like

select * from #table where (ISNUMERIC(REPLACE(value,'%','')) =1) AND (CONVERT(FLOAT,REPLACE(value,'%','')) >9.0)

Sep 20, 2011 at 08:14 AM palum
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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...

more ▼

answered Sep 18, 2011 at 01:09 PM

avatar image

ThomasRushton ♦♦
39.8k 20 49 52

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1066
x55

asked: Sep 16, 2011 at 03:44 PM

Seen: 1295 times

Last Updated: Sep 20, 2011 at 08:07 AM

Copyright 2016 Redgate Software. Privacy Policy