see cte has certain limit.still you can use technique.
Example in ur query above remove where clause for now.
Select distinct OID,1
from cten c
innerjoin @TmpINT t
on t.TagId=c.TagId
Declare one table variable above ur CTE,nu mber of column should equal to number of column in select query.and decide datatype according to return column datatype.since i am removeing
where clause now,include those column in select
v_rangasamy
Member
158 Points
549 Posts
Help me to write a query
Jul 29, 2012 08:17 AM|LINK
Hi all,
I have a following query
Select distinct OID,1 from cten c inner join @TmpINT t on t.TagId=c.TagId where CAST(c.Value AS FLOAT) < CAST(t.Value AS FLOAT)
and there is a variable @Relation and based on the @Relation value the operator should be used. For example,
@Relation Where Condition
3 < (where CAST(c.Value AS FLOAT) < CAST(t.Value AS FLOAT))
4 > (where CAST(c.Value AS FLOAT) > CAST(t.Value AS FLOAT))
5 <= (where CAST(c.Value AS FLOAT) <= CAST(t.Value AS FLOAT))
6 >= (where CAST(c.Value AS FLOAT) < CAST(t.Value AS FLOAT))
help me how to write a query based on @Relation value
sumoncse12
Member
80 Points
25 Posts
Re: Help me to write a query
Jul 29, 2012 08:31 AM|LINK
You can use make the where condition as string or varchar. then add it to main query then execute.
hope it will work.
v_rangasamy
Member
158 Points
549 Posts
Re: Help me to write a query
Jul 29, 2012 09:29 AM|LINK
Is it possible without using string or nvarchar... something like IF LESE or CASE WHEN...
KumarHarsh
All-Star
15133 Points
3647 Posts
Re: Help me to write a query
Jul 29, 2012 10:37 AM|LINK
see cte has certain limit.still you can use technique.
Example in ur query above remove where clause for now.
Select distinct OID,1 from cten c inner join @TmpINT t on t.TagId=c.TagId
Declare one table variable above ur CTE,nu mber of column should equal to number of column in select query.and decide datatype according to return column datatype.since i am removeing where clause now,include those column in select
Declare @t table(oid int,cValue float,tValue float)
insert into @t
Select distinct OID,cas(c.Value as float),cast(t.Value as foat) from cten c inner join @TmpINT t on t.TagId=c.TagId
now apply ur condition,
If (@Relation=1)
Select * from @t where CAST(cValue AS FLOAT) < CAST(tValue AS FLOAT)
else if
Though i am not happy with solution.but u r asking them in titbit,so not getting the whole scenerio and no idea about db design
Kumar Harsh