SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SQL Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tooba
Posting Yak Master

154 Posts

Posted - 05/21/2013 :  21:06:27  Show Profile  Reply with Quote
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.

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/21/2013 :  23:29:42  Show Profile  Reply with Quote
You are passing –ve values to SUBSTRING() and RIGHT() functions when maxval is null.
Use ‘/’ instead of ‘’ to get non zero value from charindex.
This may not be the most elegant way to solve it, but it goes:



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) 


Out of curiosity, why are you storing integer values (maxval and minval) as varchar(20)? You may want to consider changing it to the data type to int.

Edited by - MuMu88 on 05/21/2013 23:30:26
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/22/2013 :  00:32:03  Show Profile  Reply with Quote
the issue is with values without / which is why CHARINDEX fails.
I dont know why you've values in this format for maxval. i would have expected a single value which will represent maximum value.

Anyways if its format v1/v2 then use like

SELECT
FROM test1 t1
Inner Join Test2 t2 ON T1.ID = T2.ID
WHERE STUFF(t1.MAXVAL,CHARINDEX('/',t1.MAXVAL + '/'),LEN(t1.MAXVAL),'') > T2.MAXVAL 
AND STUFF(t1.MAXVAL,1,CHARINDEX('/',t1.MAXVAL + '/'),'') > T2.MINVAL
or (T1.VALUETYPE <= T2.Type)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

tooba
Posting Yak Master

154 Posts

Posted - 05/23/2013 :  23:08:47  Show Profile  Reply with Quote
Thanks Guys awesome.... Questions/ Answered..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/24/2013 :  00:30:43  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000