September 17, 2008 at 4:35 am
All,
I had worked on the below requirement few days back.
Create table Calc_History
(
MID varchar(25),
Created_Date datetime,
STD_DEV numeric(8,3),
MEAN numeric(8,3)
VARIANCE numeric(8,3)
)
insert into Calc_History
select '100',getdate(),4.0,2.0,1.0
union all
select '200',getdate(),1.0,1.0,1.0
union all
select '300',getdate(),7.0,3.0,1.0
union all
select '400',getdate(),8.0,4.0,1.0
union all
select '500',getdate(),3.0,1.0,1.0
UI has the option to apply filter condition, so USER will choose the filter condition at run time.
Examples:
1) list of managers whose STD_DEV value is 5.0.i.e STD_DEV = 5.0
2) list of managers whose STD_DEV value is greatr than 5.0 i.e STD_DEV > 5.0
3) list of managers whose STD_DEV value between 3 and 4. i.e STD_DEV BETWEEN 3 AND 4.
After choosing these filter condition, they will click SAVE FILTER button to save the conditions.
A new SP is developed to save the filter condition. I have written the following code within the SP.
Create Proc P_Filter_Condtions
(
@MID varchar(25),
@Filtercondition1 varchar(255),
@Filtercondition2 varchar(255)
)
As
Begin
Declare @filter_1_param varchar(20),
@filter_1_operator varchar(10),
@filter_1_value numeric(8,3),
@filter_1_btw_value numeric(8,3),
@filter_1_op varchar(1),
@filter_2_param varchar(20),
@filter_2_operator varchar(10),
@filter_2_value numeric(8,3),
@filter_2_btw_value numeric(8,3),
@filter_2_op varchar(1)
If @Filtercondition1 <> ''
Begin
Declare @filter varchar(255)
select @filter = '~STD_DEV~=~5.0~0~A~' --@Filtercondition1
Select ID = identity(int,1,1), substring(@filter,N+1,charindex('~',substring(@filter,N+1,dataLength(@filter)))-1) as Filter
into #f1
from Tally
where N < Len(@filter)
and substring(@filter,N,1) = '~'
Select @filter_1_param = Filter
from #f1
Where ID = 1
Select @filter_1_operator = Filter
from #f1
Where ID = 2
Select @filter_1_value = convert(numeric(8,3),Filter)
from #f1
Where ID = 3
Select @filter_1_btw_value = convert(numeric(8,3),Filter)
from #f1
Where ID = 4
Select @filter_1_op = Filter
from #f1
Where ID = 5
End
If @Filtercondition2 <> ''
Begin
Declare @filter varchar(255)
select @filter = '~STD_DEV~=~5.0~0~A~' --@Filtercondition2
Select ID = identity(int,1,1), substring(@filter,N+1,charindex('~',substring(@filter,N+1,dataLength(@filter)))-1) as Filter
into #f2
from Tally
where N < Len(@filter)
and substring(@filter,N,1) = '~'
Select @filter_1_param = Filter
from #f2
Where ID = 1
Select @filter_2_operator = Filter
from #f2
Where ID = 2
Select @filter_2_value = convert(numeric(8,3),Filter)
from #f2
Where ID = 3
Select @filter_2_btw_value = convert(numeric(8,3),Filter)
from #f2
Where ID = 4
Select @filter_2_op = Filter
from #f2
Where ID = 5
End
Insert into tbl_FILT_COND
Values
(
@MID,
@filter_1_param ,
@filter_1_operator,
@filter_1_value numeric(8,3),
@filter_1_btw_value ,
@filter_1_op ,
@filter_2_param ,
@filter_2_operator ,
@filter_2_value ,
@filter_2_btw_value ,
@filter_2_op
)
End
tbl_FILT_COND Table Structure :
Create table tbl_FILT_COND
(
MID varchar(25),
ParamName1 varchar(25),
Operator1 varchar(10),
Value1 numeric(8,3),
Betweenvalue1 numeric(8,3),
Operand1 varchar(3) - It will store AND,OR only
ParamName2 varchar(25),
Operator2 varchar(10),
Value2 numeric(8,3),
Betweenvalue2 numeric(8,3),
Operand2 varchar(3) - It will store AND,OR only
)
My task is to insert the splitted values as a single row.
Say for example,
@filtercondition1 = '~STD_DEV~>~5.0~~A~'
@filtercondition2 = '~MEAN~=~1~~~'
will be splittled and inserted into the tbl_FILT_COND as below.
100,17/09/2008,STD_DEV,BTW,5.0,6.0,A,MEAN,=,1,0,''
I am facing problem in the filter condition parts. if the UI passes the parameter like
'~STD_DEV~>~5.0~~A~' ,I have split it into 5 values. The problematic area is double ~~ symbol
If the input parameter value is like '~STD_DEV~BTW~5.0~6~A~', I can handle them easily.
How should i split double ~~ symbol into two ?
I hope i have explained enough. Inputs are welcome !
Also i wanted to know whether the way which i used to make the rows into column is correct or not. is there any other work around there to do this ?
karthik
September 17, 2008 at 6:04 am
REPLACE(@FilterCondition1, '~~', '~0~')
You can put that inline with where you're splitting the rest of your string. Replace the Zero with whatever character you need for that value to show no choice set.
September 17, 2008 at 8:58 pm
karthikeyan (9/17/2008)
How should i split double ~~ symbol into two ?I hope i have explained enough. Inputs are welcome !
Also i wanted to know whether the way which i used to make the rows into column is correct or not. is there any other work around there to do this ?
Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays
http://www.sqlservercentral.com/articles/T-SQL/63003/
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
http://www.sqlservercentral.com/articles/T-SQL/63681/
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2008 at 2:57 am
DECLARE @Parameter VARCHAR(255)
SELECT @Parameter = 'Part#1,PartName1,1|Part#2,PartName2,2|Part#3,PartName3,3'
SET @Parameter = ','+STR_REPLACE(@Parameter,'|',',') +','
DECLARE @GroupCount INT
--===== Suppress the auto-display of rowcounts to keep them from being -- mistaken as part of the result set.
SET NOCOUNT ON
--===== Determine the number of elements in a group (assumes they're all the same)
--SELECT @GroupCount = -- Find the length of the first group...
SELECT LEN(SUBSTRING(@Parameter,1,CHARINDEX('|',@Parameter)))
19
SELECT LEN(REPLACE(SUBSTRING(@Parameter,1,CHARINDEX('|',@Parameter)),',',''))
19
so
SELECT @GroupCount = -- Find the length of the first group...
LEN(SUBSTRING(@Parameter,1,CHARINDEX('|',@Parameter))) -- ... subtract the length of the first group without any commas...
- LEN(REPLACE(SUBSTRING(@Parameter,1,CHARINDEX('|',@Parameter)),',','')) -- ... and add 1 because there is always 1 element more than commas.
+ 1
19 -19 + 1
0 +1
1
Here i am confused. Am i getting correct result ?
drop table #Elements
go
Create Table #Elements
(
ElementNumber INT IDENTITY(1,1) , --Order it appears in original string
ElementValue VARCHAR(255), --The string value of the element
RowNum INT NULL, --One of the two indices
ColNum INT NULL --One of the two indices
)
INSERT INTO #Elements (ElementValue)
SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)
FROM dbo.Tally WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = ',' --Notice how we find the comma
Select * from #Elements
UPDATE #Elements
SET RowNum = (ElementNumber-1)/@GroupCount , --"Zero" based row
ColNum = (ElementNumber-1)%@GroupCount --"Zero" based col
I got the below result.
1Part#1 00
2PartName110
31 20
4Part#2 30
5PartName240
62 50
7Part#3 60
8PartName370
93 80
I think somewhere else i did mistake.
karthik
September 18, 2008 at 9:29 pm
karthikeyan (9/18/2008)
For what YOU did, yes. But you moved lines of code I posted out of order. You have to figure out where the Pipe character is first before you attempt to count parameters. Why did you change the order of the code I wrote?
So the real answer is no, you did not do it right.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply