Convert /Error

Transact-SQL
1

Hi All
I'm trying to pull data from couple of tables using a inner join. I have to use substring to get the request id part from a varchar field and then use convert function to convert to int

Below is my query and have been getting an error

SELECT RR.Requestid,RR.Rdate,RR.EDate ,REL.Requestid,Convert(INT,substring(REL.Itext,30,6))
from report_request RR inner join request_event_log REL
on RR.Requestid = Convert(INT,substring(REL.Itext,30,6))
Where REL.EVentTypeid = 50

Error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'm28324' to data type int.

The same convert works ok in the below Query

SELECT Requestid,Convert(INT,substring(Itext,30,6)) as OrigRequestid from requesteventlog where requestid = 560813
and eventtypeid =50

How should I proceed with my problematic Query?

Thanks in Advance

2

Well, clearly you have a least one row in the input where substring(Itext,30,6) = 'm28324' which is not an integer. What do you want to do with it?

3

Your second query doesn't use same filter (where statement) as the first one.

4 
SELECT RR.Requestid,RR.Rdate,RR.EDate ,REL.Requestid,Convert(INT,substring(REL.Itext,30,6))
from report_request RR inner join request_event_log REL
on 1 = case when substring(REL.Itext,30,6) LIKE '%[^0-9]%' then 0 --not digits only, ignore
            when RR.Requestid = Convert(INT,substring(REL.Itext,30,6)) then 1
            else 0 end
Where REL.EVentTypeid = 50
5

or just (I don't care for CASE in joins, but that's just me)

SELECT RR.Requestid
     , RR.Rdate
     , RR.EDate
     , REL.Requestid
     , Convert(INT, substring(REL.Itext, 30, 6)) AS Itext
FROM report_request RR
INNER JOIN request_event_log REL 
        ON RR.Requestid = Convert(INT, substring(REL.Itext, 30, 6))
WHERE substring(REL.Itext, 30, 6) NOT LIKE '%[^0-9]%'
  AND REL.EVentTypeid = 50
1 Like
6

The problem with that method is that you can't control the order of operations, and thus SQL might do the join before it applies the WHERE. Not likely, but possible. A CASE statement is always evaluated in order, so there's no chance of a conversion error.

7

true, should have put it in a subquery. Then you filter out the non-numerics before you try to convert them

1 Like
8

Thank You for the query. It worked

9

If it were me I would want to add an INT column to [request_event_log] so that I could join on that. Much more efficient, performance-wise, than a CONVERT etc. and with none of the issues of "sometimes its a number, sometimes not" - only populate the column on rows where the sub-data is numeric.

A Computed column would probably do ...

10

+1 for the computed column. But likely the OP can't modify the table

11

Yes, you are probably right. Perhaps an option to create a VIEW instead. Won't help with performance (unless the O/P is allowed to index the view) but would encapsulate the logic for reuse in other places ...

12

Here's another way of coding Scott's query that's a little shorter (don't need the 1/0). I think it will produce the same result

SELECT RR.Requestid
    , RR.Rdate
    , RR.EDate
    , REL.Requestid
    , Convert(INT, substring(REL.Itext, 30, 6))
FROM report_request RR
INNER JOIN request_event_log REL 
  ON RR.Requestid  = CASE 
        WHEN substring(REL.Itext, 30, 6) LIKE '%[^0-9]%'
            THEN NULL
	 ELSE Convert(INT, substring(REL.Itext, 30, 6))
        END
WHERE REL.EVentTypeid = 50
1 Like
13

Just in case not obvious to the O/P this sort of work-around syntax is going to have bad performance compared to having the numeric part of request_event_log.Itext available in its own column (or computed column), and of the correct INT datatype.

Being able too INDEX "part" of a column would help ...

14

I had to make some changes to the query and performance of the query is really slow. Can I please have some help to make it faster

select RR.requestid,Rdate,RR.ctname,(select FName+''+LName from contact where Cid = rr.requestor) as requestor,
Edate,Cexpiration,(select requestid from request_event_log where eventtypeid = 50 and RR.requestid = Convert(INT, substring(Itext, 30, 6))) as draftid,
(select top(1) eventtypeid from request_event_log where requestid = (select requestid from request_event_log where eventtypeid = 50 and  Convert(INT, substring(Itext, 30, 6)) = rr.requestid) and eventtypeid in(10,15,20,50) order by Edatetime desc) as drafstatusid,
(select Count(Cid) from contact where Cid = RR.requestor and Memail is not null) as avilableincompany from report_request RR
where RR.Rfrequency = 'Recurring'
and rr.Edate between '1/1/2016' and '1/31/2016'
15

No idea if this code will work as no sample Table / Data provided with which to test it.

Hopefully runs faster than what you have, but whilst you have the SUBSTRING in your JOINs it will still run like a dog (compared to if you make that available in its own column / computed-column

I assume you have Indexes on the appropriate columns in the JOINs, and "covering" the ORDER BY Edatetime DESC

SELECT 	RR.requestid,Rdate,RR.ctname,
	COALESCE(C.FName+' ', '')+COALESCE(C.LName, '') as requestor,
	Edate,
	Cexpiration,
	REL.requestid as draftid,
	REL2.eventtypeid as drafstatusid,
	AC.Count
FROM	report_request AS RR
	LEFT OUTER JOIN contact AS C
		 ON Cid = RR.requestor
	LEFT OUTER JOIN request_event_log AS REL
		 ON REL.eventtypeid = 50
-- This needs the CASE workaround if any SUBSTRINGs will NOT be valid numerics
		AND Convert(INT, substring(REL.Itext, 30, 6)) = RR.requestid
	OUTER APPLY
	(
		SELECT	TOP 1	REL3.eventtypeid
		FROM	request_event_log AS REL3
		WHERE	    REL3.eventtypeid = 50	-- <<<< SORT THIS DUP OUT!!
			AND REL3.eventtypeid in(10,15,20,50)
-- This needs the CASE workaround if any SUBSTRINGs will NOT be valid numerics
			AND Convert(INT, substring(REL3.Itext, 30, 6)) = RR.requestid
		ORDER BY REL3.Edatetime DESC
	) AS REL2

	OUTER APPLY
	(
		SELECT	COUNT(Cid) AS [Count]
		FROM	contact
		WHERE	    Cid = RR.requestor 
			AND Memail IS NOT NULL
	) AS AC
where RR.Rfrequency = 'Recurring'
-- Do NOT use ambiguous "m/d/y" date strings
-- nor BETWEEN (in case the Date includes a Time)
	and RR.Edate >= '20160101'
	and RR.Edate <  '20160201'
1 Like
16

Just noticed that you have

eventtypeid = 50
...
and eventtypeid in(10,15,20,50)

that obviously needs sorting out as its only going to match "50" as it stands

17

Thank You Much . It is slow but lot better
Mine took 2.14 Minutes Yours in 44 seconds
Long term we would have it pull from a field in the database instead of having to convert

18

I would be inclined to create some indexes, with temporary names so they are easy to DROP, and see if that helps (e.g. on a Test Server). If it does I would then figure out which of the indexes was actually used / made a difference and then could request that they were formally adopted (if you have a procedure like that)

report_request : Edate, Rfrequency, requestor

request_event_log : eventtypeid, Itext, Edatetime, requestid

contact : Cid , Memail

If you've got that lot on a test server stuff a Computed Column into [request_event_log] to store the INT value of substring(Itext, 30, 6) and use that column's name in the index instead of [Itext]