SQL Server - Where condition usage -
i want add condition below select sql code.
select rtrim(ltrim(substring(line,1,charindex('|',line) -1))) drivename ,round(cast(rtrim(ltrim(substring(line,charindex('|',line)+1, (charindex('%',line) -1)-charindex('|',line)) )) float)/1024,0) 'capacity(gb)' ,round(cast(rtrim(ltrim(substring(line,charindex('%',line)+1, (charindex('*',line) -1)-charindex('%',line)) )) float) /1024 ,0)as 'freespace(gb)' #output line '[a-z][:]%' order drivename the result ;
drivename capacity(gb) freespace(gb) c:\ 120 36 d:\ 100 7 i want add : 'freespace(gb) > 10'
how can add condition?
multiple ways this.. temp table , cte may seems same try understand difference between them here.
by using temporary table
select * ##t (select rtrim(ltrim(substring(line,1,charindex('|',line) -1))) drivename, round(cast(rtrim(ltrim(substring(line,charindex('|',line)+1, (charindex('%',line) -1)-charindex('|',line)) )) float)/1024,0) 'capacity(gb)', round(cast(rtrim(ltrim(substring(line,charindex('%',line)+1, (charindex('*',line) -1)-charindex('%',line)) )) float) /1024 ,0)as 'freespace(gb)' #output line '[a-z][:]%') t select * ##t [freespace(gb)] > 10 order drivename by using cte
;with cte ( select rtrim(ltrim(substring(line,1,charindex('|',line) -1))) drivename ,round(cast(rtrim(ltrim(substring(line,charindex('|',line)+1, (charindex('%',line) -1)-charindex('|',line)) )) float)/1024,0) 'capacity(gb)' ,round(cast(rtrim(ltrim(substring(line,charindex('%',line)+1, (charindex('*',line) -1)-charindex('%',line)) )) float) /1024 ,0)as 'freespace(gb)' #output line '[a-z][:]%' ) select * cte [freespace(gb)] > 10 order drivename; by directly using condition in clause
select rtrim(ltrim(substring(line,1,charindex('|',line) -1))) drivename, round(cast(rtrim(ltrim(substring(line,charindex('|',line)+1, (charindex('%',line) -1)-charindex('|',line)) )) float)/1024,0) 'capacity(gb)', round(cast(rtrim(ltrim(substring(line,charindex('%',line)+1, (charindex('*',line) -1)-charindex('%',line)) )) float) /1024 ,0)as 'freespace(gb)' #output line '[a-z][:]%' , (round(cast(rtrim(ltrim(substring(line,charindex('%',line)+1, (charindex('*',line) -1)-charindex('%',line)) )) float) /1024 ,0)) > 10 order drivename 
Comments
Post a Comment