question

palum avatar image
palum 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'?
t-sqlcte
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
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)
0 Likes 0 ·

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
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...
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.