February 11, 2010 at 12:08 pm
The situation. We have CRM application and a financial application. The financial application FT stores everything as a Patient_Number integer. When our CRM was setup, a field named UFT was created as a varchar and FT Patient Numbers are entered.
I've been tasked with determining how many customers are not in our CRM compared to FT's scheduled orders.
Both databases are on the same server and FT Patient Numbers are 4 to 5 digits in length. Realizing I was going to do a subquery, I created my CRM query first and it looks like this:
SELECT [CRMDATABASE].[UFT]
FROM [CRMDATABASE].[dbo].[CRMTABLE]
WHERE ([CRMDATABASE].[UFT] LIKE '[0-9][0-9][0-9][0-9]' OR [CRMDATABASE].[UFT] LIKE '[0-9][0-9][0-9][0-9][0-9]')
ORDER BY [CRMDATABASE].[UFT]
The above query is modified to not give real names, so if I made a typo, you can ignore them. This query works great. It filters out invalid entries in the UFT field and gives me exactly what I'm looking for. FYI, I was trying to do my regex like [0-9]{4,5} but it wouldn't work. I tried other regex shortcuts as well and this is the only statement which would work for me. Now on to FT.
SELECT DISTINCT [FTSCHEDULE].[PATIENT_NUMBER]
FROM [FTDATABASE].[dbo].[FTSCHEDULE]
WHERE [FTSCHEDULE].[PATIENT_NUMBER] NOT IN
(SELECT [CRMDATABASE].[UFT]
FROM [CRMDATABASE].[dbo].[CRMTABLE]
WHERE ([CRMDATABASE].[UFT] LIKE '[0-9][0-9][0-9][0-9]' OR [CRMDATABASE].[UFT] LIKE '[0-9][0-9][0-9][0-9][0-9]'))
I get an error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '13268/2320' to data type int.
What's weird is, if I modify my where clause to just [UFT] LIKE '[0-9][0-9][0-9][0-9]', it works for all 4 digit numbers. But as soon as I do 5 digits, I get that error every time. The WHERE clause of the subquery should be filtering out the value '13268/2320' but it doesn't when looking for a 5 digit number. I figure it is the "/" but that's the 6th digit. When I run the subquery by itself with 4-5 numeric characters the record '13268/2320' is not included in the results. So why does it appear when the subquery runs?
Any help would be appreciated, thanks!
February 11, 2010 at 8:52 pm
It seems likely that either FTSCHEDULE.Patient_Number or CRMDatabase.UFT (or maybe both) are character datatypes (char, varchar, nchar, or nvarchar). If so, and the values that your query is comparing starts off with a numeric Patient_Number, you could get an error like this when it encounters the non-numeric '13268/2320' in column UFT.
I'd guess that you're using the LIKE operators and subselect to try to filter out these mismatched types. I'd suggest instead that a compound condition may do the trick. Here's a script using table variables in place of your actual tables to demonstrate:
Declare @FTSched table (Patient_Number int, Patient_Name varchar(30))
Declare @CRM table (UFT char(10))
Insert @FTSched
Select 1234, 'Joe' Union All
Select 12345, 'John' Union All
Select 4454, 'Ashok' Union All
Select 1246, 'Sudra' Union All
Select 33234, 'Vinnet' Union All
Select 12934, 'Sue'
Insert @CRM
Select '4454' Union All
Select '33234' Union All
Select 'Hello!' Union All
Select '44334' Union All
Select '1234'
SELECT DISTINCT [PATIENT_NUMBER]
FROM @FTSCHED
WHERE Not Exists
(Select * from @CRM
Where UFT = PATIENT_NUMBER
and IsNumeric(UFT) = 1
)
This will find the patient numbers not matched in the CRM db and the (not) EXISTS structure has the performance advantage of not building a subselect resultset and allowing the use of an index on UFT if present.
February 12, 2010 at 11:50 am
Awesome, the (WHERE ISNUMERIC(UFT) = 1) was exactly what I needed. Thank you!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply