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

palum gravatar image

palum
249 26 29 30

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

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x991
x41

asked: Sep 16, 2011 at 03:44 PM

Seen: 1154 times

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