January 2, 2008 at 8:56 am
I have a view that I have created for reporting purposes (Crystal 8.5), however I have a issue with one of the columns (RECENTINR) that is set to varchar and cannot be changed (it's a free text field in our application). Most of the data in this column are decimals however some values in this column are null or erroneous and I would like to report that back as zero. I have 3 tables in my view that I am using. I need help making a work around for this view in able to report on this field and do calculations (average, etc.) Below are my tables
--TESTTABLE1(COUMAD)
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
(
KEYID INT(4) PRIMARY KEY
COMPANY VARCHAR(10),
KEYDATE DATETIME(8),
RECENTINR VARCHAR(255), ---- NEED TO CONVERT TO DECIMAL IN THE VIEW
CMD VARCHAR(255),
THERAP VARCHAR(255) )
--===== NEED TO CONVERT THIS COLUMN TO DECIMAL VALUE
RECENTINR
SET IDENTITY_INSERT #mytable ON
INSERT INTO #mytable
(KEYID, COMPANY, KEYDATE, RECENTINR, CMD, THERAP)
SELECT '3','TRAIN','Feb 8 2007 12:00AM','2.7000002 ','John Doe, MD','' UNION ALL
SELECT '11','TRAIN','Mar 1 2007 12:00AM','1.500001','','' UNION ALL
SELECT '5','TRAIN','Feb 12 2007 12:00AM','2.5','','' UNION ALL
SELECT '9','TRAIN','Feb 21 2007 12:00AM','2.7','Jeffrey A Leavy, M.D.','Above Therapeutic Range' UNION ALL
SELECT '13','TRAIN','Mar 7 2007 12:00AM','5.1','','' UNION ALL
SELECT '12','TRAIN','Mar 1 2007 12:00AM','2.5','','Under Therapeutic Range' UNION ALL
SELECT '10','TRAIN','Mar 1 2007 12:00AM','2.5','John Doe, MD','Within Therapeutic Range' UNION ALL
SELECT '1','TRAIN','Nov 27 2006 12:00AM','1.5000005','John Doe, MD','Within Therapeutic Range' UNION ALL
SELECT '2','TRAIN','Nov 28 2006 12:00AM','NULL','John Doe, MD','Within Therapeutic Range' UNION ALL
SELECT '4','TRAIN','Feb 8 2007 12:00AM','1.5','Steven E Silver, M.D.','Within Therapeutic Range' UNION ALL
SELECT '6','TRAIN','Feb 19 2007 12:00AM','1.5666671','Jeffrey A Leavy, M.D.','Within Therapeutic Range' UNION ALL
SELECT '7','Feb 20 2007 12:00AM','1.5','Jeffrey A Leavy, M.D.','Within Therapeutic Range'
SET IDENTITY_INSERT #mytable OFF
----
--TESTTABLE2(CLMASTER)
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
(
ACCOUNT VARCHAR(10) PRIMARY KEY
COMPANY VARCHAR(10) ,
PLNAME VARCHAR(35),
PFNAME VARCHAR(30),
PMNAME VARCHAR(30),
DOB DATETIME(8) )
SET IDENTITY_INSERT #mytable ON
INSERT INTO #mytable
(ACCOUNT, COMPANY, PLNAME, PFNAME, PMNAME, DOB)
SELECT '010006','TRAIN','EVANS','RICHARD','','Jan 19 1948 12:00AM' UNION ALL
SELECT '010009','TRAIN','SIMONS','ROBERT','','Oct 15 1974 12:00AM' UNION ALL
SELECT '010011','TRAIN','CHAZIN','BARABARA','','Feb 17 1935 12:00AM' UNION ALL
SELECT '010013','TRAIN','BANNISTER','ANDREA','F','Sep 21 1967 12:00AM' UNION ALL
SELECT '010016','TRAIN','ACKERMAN','JODI','','Apr 20 1983 12:00AM' UNION ALL
SELECT '010019','TRAIN','KOMER','TERI','A','Oct 4 1964 12:00AM' UNION ALL
SELECT '010021','TRAIN','DUDDY','SUZANNE','','Jan 9 1969 12:00AM' UNION ALL
SELECT '010022','TRAIN','DUDDY','SUZANNE','','Jan 9 1969 12:00AM' UNION ALL
SELECT '010023','TRAIN','DUDDY','SUZANNE','','Oct 9 1969 12:00AM' UNION ALL
SELECT '010024','TRAIN','DUDDY','SUZANNE','','Jan 9 1969 12:00AM' UNION ALL
SELECT '010025','TRAIN','SULLIGAN','DAWN','','Mar 28 1975 12:00AM'
SET IDENTITY_INSERT #mytable OFF
-----
--TESTTABLE3(MWAPPTS)
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
(
ACCOUNT VARCHAR(10) PRIMARY KEY
COMPANY VARCHAR(10), PRIMARY KEY
ADATE DATETIME(8)
)
SET IDENTITY_INSERT #mytable ON
INSERT INTO #mytable
(ACCOUNT, COMPANY, ADATE)
SELECT '842','TRAIN','Feb 9 2007 12:00AM', UNION ALL
SELECT '196','TRAIN','Feb 9 2007 12:00AM', UNION ALL
SELECT '2129','TRAIN','Feb 9 2007 12:00AM', UNION ALL
SELECT '51910','TRAIN','Feb 9 2007 12:00AM', UNION ALL
SELECT '10611','TRAIN','Feb 9 2007 12:00AM', UNION ALL
SELECT '43594','TRAIN','Feb 9 2007 12:00AM', UNION ALL
SELECT '8896','TRAIN','Feb 9 2007 12:00AM', UNION ALL
SELECT '45887','TRAIN','Feb 9 2007 12:00AM', UNION ALL
SELECT '11196','TRAIN','Feb 9 2007 12:00AM', UNION ALL
SELECT '7037','TRAIN','Feb 9 2007 12:00AM',
SET IDENTITY_INSERT #mytable OFF
---
MY VIEW
SELECT dbo.CLMASTER.ACCOUNT, dbo.CLMASTER.PLNAME, dbo.CLMASTER.PFNAME, dbo.CLMASTER.PMNAME, dbo.CLMASTER.DOB,
(SELECT TOP 1 weekly
FROM coumad SubCoumad
WHERE SubCoumad.ssno = coumad.ssno AND SubCoumad.company = coumad.company
ORDER BY keyid DESC) AS MostRecentWeekly,
(SELECT TOP 1 weekly
FROM coumad SubCoumad
WHERE SubCoumad.ssno = coumad.ssno AND SubCoumad.company = coumad.company AND keyid NOT IN
(SELECT TOP 1 keyid
FROM coumad SubCoumad
WHERE SubCoumad.ssno = coumad.ssno AND SubCoumad.company = coumad.company
ORDER BY keyid DESC)
ORDER BY keyid DESC) AS NextTomostRecentWeekly, dbo.COUMAD.SSNO, dbo.MWAPPTS.ADATE, dbo.COUMAD.THERAP,
(SELECT TOP 1 adate
FROM mwappts
WHERE mwappts.account = clmaster.account AND mwappts.company = clmaster.company
AND mwappts.adate > cast(CONVERT(varchar(25),getdate(), 101) AS datetime) ORDER BY cast(adate AS datetime) ASC) AS NextScheduledAppt,
dbo.COUMAD.CMD, dbo.COUMAD.INRR, dbo.COUMAD.COMPANY,
(SELECT TOP 1 RECENTINR
FROM coumad SubCoumad
WHERE SubCoumad.ssno = coumad.ssno AND SubCoumad.company = coumad.company AND keyid NOT IN
(SELECT TOP 1 keyid
FROM coumad SubCoumad
WHERE SubCoumad.ssno = coumad.ssno AND SubCoumad.company = coumad.company
ORDER BY keyid DESC)
ORDER BY keyid DESC) AS LastINRR, dbo.COUMAD.RECENTINR,
(SELECT TOP 1 RECENTINR
FROM coumad SubCoumad
WHERE SubCoumad.ssno = coumad.ssno AND SubCoumad.company = coumad.company AND keyid NOT IN
(SELECT TOP 2 keyid
FROM coumad SubCoumad
WHERE SubCoumad.ssno = coumad.ssno AND SubCoumad.company = coumad.company
ORDER BY keyid DESC)
ORDER BY keyid DESC) AS NextTomostRecentToMostRecentWeekly,
(SELECT TOP 1 KEYDATE
FROM coumad SubCoumad
WHERE SubCoumad.ssno = coumad.ssno AND SubCoumad.company = coumad.company AND keyid NOT IN
(SELECT TOP 1 keyid
FROM coumad SubCoumad
WHERE SubCoumad.ssno = coumad.ssno AND SubCoumad.company = coumad.company
ORDER BY keyid DESC)
ORDER BY keyid DESC) AS LASTINRDATE, dbo.COUMAD.KEYDATE,
(SELECT TOP 1 KEYDATE
FROM coumad SubCoumad
WHERE SubCoumad.ssno = coumad.ssno AND SubCoumad.company = coumad.company AND keyid NOT IN
(SELECT TOP 2 keyid
FROM coumad SubCoumad
WHERE SubCoumad.ssno = coumad.ssno AND SubCoumad.company = coumad.company
ORDER BY keyid DESC)
ORDER BY keyid DESC) AS NextTomostRecentToMostRecentINRDATE
FROM dbo.COUMAD INNER JOIN
dbo.CLMASTER ON dbo.COUMAD.COMPANY = dbo.CLMASTER.COMPANY AND dbo.COUMAD.SSNO = dbo.CLMASTER.SSNO INNER JOIN
(SELECT ssno, MAX(keyid) MaxKeyID
FROM coumad
GROUP BY ssno) TopCoumad ON dbo.COUMAD.SSNO = TopCoumad.ssno AND dbo.COUMAD.KEYID = TopCoumad.MaxKeyID INNER JOIN
dbo.MWAPPTS ON dbo.COUMAD.COMPANY = dbo.MWAPPTS.COMPANY AND dbo.CLMASTER.COMPANY = dbo.MWAPPTS.COMPANY AND
dbo.CLMASTER.ACCOUNT = dbo.MWAPPTS.ACCOUNT
WHERE CLMASTER.COMPANY='MAIN'
January 2, 2008 at 9:14 am
Try isnumeric. It would look something like this.
CASE WHEN ISNUMERIC(RECENTINR)=1 THEN CAST(RECENTINR AS DECIMAL(10,2) ELSE 0 END
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
January 3, 2008 at 1:56 pm
While I agree with Kenneth that Isnumeric will likely solve your problem, keep in mind that it's borked, to put it nicely. If your data isn't too bad, it should work fine, but if you have bizarre values in that field, there are things that will pass the Isnumeric test, but won't convert to a decimal, such as tabs.
SELECT IsNumeric(Char(9)) -- That's a tab, and will return true (1)
SELECT Cast(Char(9)) AS decimal(10,2)) --this will return an error
If you find that you're getting conversion failures that are passing the Isnumeric test, you might have to roll your own function to test that the string meets the necessary requirements, or if it's a single pattern or two that are causing the problems, handling them prior to the conversion.
January 4, 2008 at 12:43 pm
The following ASCII values will return 1 from IsNumeric:
9 (tab), 10 (LF), 11 (VT), 12(FF), 13(CR),
36('$'), -- because IsNumeric also must recognize money
43('+'),
44(','), -- for example '123,456'
45('-'), 46('.')
and, of course, the digits '0' thru '9'.
There are even some combinations that will pass: '$.' and '+.' for example.
Below is a solid check you can use. If a string is only one character (len = 1) then that character must be a digit. If two characters, the first character must be a digit, '+', '-', or '.' and the second character must be a digit. These additional checks are performed only if the string first passes the IsNumeric test and is less than three characters in length. I haven't (yet) found a three character sequence that passes IsNumeric but fails Convert or Cast.
if IsNumeric( @String ) = 1
and (
Len( @String ) > 2
or (Len( @String ) = 1
and @String between '0' and '9'
)
or (Len( @String ) = 2
and Ascii( SubString( @String, 1, 1 )) not in (9, 10, 11, 12, 13, 36, 44)
and Ascii( SubString( @String, 2, 1 )) between '0' and '9'
)
)
You can make it a UDF for convenience but really, how many places will you use something like this?
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
January 4, 2008 at 5:55 pm
I thought I would test my own claim that no three-character sequence could pass IsNumeric and fail Convert. Sure enough, I found one (there could be more). The string '$+.' will return 1 (true) from IsNumeric but will generate the "Error converting data type varchar to numeric" error from Convert.
This isn't a problem for me as none of my data is going to be close to that pattern. If this prevents the code from being useful to you, perhaps I can fix it.
Aamof, I do create a general case IsTrulyNumeric function, but it is built on two other functions, one of which uses a Numbers table. It is based on the assumption that if it passes IsNumeric and contains at least one digit somewhere, it will successfully Convert. I won't throw all that code here unsolicited. If anyone is interested, let me know.
The two other functions are:
HasChar( s1, s2 ) returns 1 if any character in s1 is found in s2
HasDigit( s1 ) returns 1 if any character in s1 is a digit ('0' thru '9')
Obviously, the HasDigit function is nothing more than "return HasChar('0123456789', s1);" I just like writing functions. 😛
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply