May 21, 2013 at 7:07 pm
Create Table Test1
(
ID INT,
VALUE VARCHAR(20),
MAXVAL VARCHAR(20),
VALUETYPE INT,
OutCome int
)
--select * from Test1
INSERT INTO Test1
VALUES ('1','FirstValue','140/90','1','4')
INSERT INTO Test1
VALUES ('2','SecondValue','140/90','1','4')
INSERT INTO Test1
VALUES ('3','ThirdValue',null,'4','3')
INSERT INTO Test1
VALUES ('4','FourthValue',null,'4','3')
Create Table Test2
(
ID INT,
MAXVAL VARCHAR(20),
MINVAL VARCHAR(20),
Type int
)
INSERT INTO Test2
VALUES ('1','139','89','1')
INSERT INTO Test2
VALUES ('2','141','95','1')
INSERT INTO Test2
VALUES ('3','141','95','4')
Select
* from test1 t1
Inner Join Test2 t2 ON T1.ID = T2.ID
WHERE ((SUBSTRING(isnull(t1.MaxVal,''),1,CHARINDEX('/',ISNULL(T1.MAXVAL,''))-1))> T2.MAXVAL
AND LTRIM(RIGHT(ISNULL(t1.MaxVal,''), CHARINDEX('/', ISNULL(t1.MaxVal,'') + '/')-2) ) > T2.MINVAL)
or (T1.VALUETYPE <= T2.Type)
Here is the error that I am getting
“Invalid length parameter passed to the LEFT or SUBSTRING function”
Please Advice.
Thanks in advance.
May 21, 2013 at 7:59 pm
rocky_498 (5/21/2013)
INSERT INTO Test1VALUES ('1','FirstValue','140/90','1','4')
INSERT INTO Test1
VALUES ('2','SecondValue','140/90','1','4')
INSERT INTO Test1
VALUES ('3','ThirdValue',null,'4','3')
INSERT INTO Test1
VALUES ('4','FourthValue',null,'4','3')
Select
* from test1 t1
Inner Join Test2 t2 ON T1.ID = T2.ID
WHERE ((SUBSTRING(isnull(t1.MaxVal,''),1,CHARINDEX('/',ISNULL(T1.MAXVAL,''))-1))> T2.MAXVAL
AND LTRIM(RIGHT(ISNULL(t1.MaxVal,''), CHARINDEX('/', ISNULL(t1.MaxVal,'') + '/')-2) ) > T2.MINVAL)
or (T1.VALUETYPE <= T2.Type)
You can't trust the order of operations when restricting data, particularly for a WHERE clause. Additionally, ID 3 exists in both tables so 4 is trying to substring there as well. So you're ending up not finding a /, so CHARINDEX = 0. Then you try to pass a negative 1 and a negative 2, which is invalid.
You need to use a CASE to short circuit the process, such as this (untested):
CASE CHARINDEX('/', ISNULL( T1.MAXVAL,'')) = 0
THEN ''
ELSE (SUBSTRING(isnull(t1.MaxVal,''),1,CHARINDEX('/',ISNULL(T1.MAXVAL,''))-1)
END > T2.Minval
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 21, 2013 at 8:27 pm
Hi Evil,
Thank You for your reply. Could you please help me with right-down the query?
May 21, 2013 at 8:34 pm
I think i got it. Here is the final query.
Select
* from Test1 t1
Inner Join Test2 t2 ON T1.ID = T2.ID
where
Case
when CHARINDEX('/',ISNULL(t1.maxval,'')) = 0
THEN ''
ELSE (SUBSTRING(ISNULL(T1.MAXVAL,''),1,CHARINDEX('/',ISNULL(T1.MAXVAL,''))-1)) END > T2.MAXVAL
--((SUBSTRING(isnull(t1.MaxVal,''),1,CHARINDEX('/',ISNULL(T1.MAXVAL,''))-1))> T2.MAXVAL
AND
case
when CHARINDEX('/',ISNULL(T1.MAXVAL,'')) = 0
then ''
ELSE
(LTRIM(RIGHT(ISNULL(t1.MaxVal,''), CHARINDEX('/', ISNULL(t1.MaxVal,'') + '/')-2) )) END > T2.MINVAL
or (t1.VALUETYPE <= T2.Type)
Its looks good to me. Please double check.
Thanks for your help.
May 21, 2013 at 8:39 pm
Yeah, sorry, been working all night and hoped a quickie would get your answer for you. Got some time now so here's your query end to end:
IF OBJECT_ID ('tempdb..#test1') IS NOT NULL
DROP TABLE #test1
IF OBJECT_ID ('tempdb..#test2') IS NOT NULL
DROP TABLE #test2
Create Table #Test1
(
ID INT,
VALUE VARCHAR(20),
MAXVAL VARCHAR(20),
VALUETYPE INT,
OutCome int
)
--select * from #Test1
INSERT INTO #Test1
VALUES ('1','FirstValue','140/90','1','4')
INSERT INTO #Test1
VALUES ('2','SecondValue','140/90','1','4')
INSERT INTO #Test1
VALUES ('3','ThirdValue',null,'4','3')
INSERT INTO #Test1
VALUES ('4','FourthValue',null,'4','3')
Create Table #Test2
(
ID INT,
MAXVAL VARCHAR(20),
MINVAL VARCHAR(20),
Type int
)
INSERT INTO #Test2
VALUES ('1','139','89','1')
INSERT INTO #Test2
VALUES ('2','141','95','1')
INSERT INTO #Test2
VALUES ('3','141','95','4')
Select
*
from
#Test1 t1
Inner Join
#Test2 t2
ONT1.ID = T2.ID
WHERE
CASE
WHEN CHARINDEX( '/', ISNULL( T1.MAXVAL, '')) = 0
THEN ''
ELSE LEFT( ISNULL( T1.MAXVAL, ''), CHARINDEX( '/', ISNULL( T1.MAXVAL, '')) -1)
END > T2.MAXVAL
ANDCASE
WHEN CHARINDEX( '/', ISNULL( T1.MAXVAL, '')) = 0
THEN ''
ELSE RIGHT( ISNULL( T1.MAXVAL, ''), CHARINDEX( '/', ISNULL( T1.MAXVAL, '')) -2)
END > T2.MAXVAL
or (T1.VALUETYPE <= T2.Type)
Let me know if you have questions.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 21, 2013 at 8:41 pm
No No Please don't say Sorry.
I really appreciate your help.
May 22, 2013 at 12:57 pm
Purely fyi, I personally think a cross apply that names the value being used makes the code much easier to follow and easier to change later. For example:
Select
*
from
#Test1 t1
Inner Join
#Test2 t2
ONT1.ID = T2.ID
cross apply (
select ISNULL( CHARINDEX( '/', T1.MAXVAL ), 0 ) AS maxval_first_slash
) as ca1
WHERE
CASE
WHEN maxval_first_slash = 0
THEN ''
ELSE LEFT( T1.MAXVAL, maxval_first_slash - 1 )
END > T2.MAXVAL
ANDCASE
WHEN maxval_first_slash = 0
THEN ''
ELSE RIGHT( T1.MAXVAL, maxval_first_slash - 2)
END > T2.MAXVAL
or (T1.VALUETYPE <= T2.Type)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply