Hi,
Thank you in advance for your comments/suggestions. I am trying to create a View of a Table. The table is created by another application so I am unable to recreate it they way I want, also the data that is in the columns that I want to CAST are "numbers" not letters and will only be numbers. In the view I need certain columns to be CAST as numeric from varchar.
Here is the syntax that I am currently using:
Code Snippet
SELECT CAST(szF1 AS datetime) AS [Login Date/Time], szF2 AS [User Name], CAST(szF3 AS numeric) AS [Documents Indexed], CAST(szF4 AS datetime) AS [Logout Date/Time], CAST(szF5 AS numeric) AS [Documents Sent to QC], CAST(szF6 AS numeric) AS [Documents Reconciled], szF7 AS [Reject Reason], CAST(szF8 AS datetime) AS [Report Date]
FROM dbo.F_Report_Data AS a
WHERE (szF3 <> 'Blank')
When I open the view I get the error message about converting varchar to numeric.
Thanks,
Erik
Try running this query:
Code Snippet
SELECT CAST(szF1 AS datetime) AS [Login Date/Time],szF2 AS [User Name],
-- CAST(szF3 AS numeric) AS [Documents Indexed],
szF3,
CAST(szF4 AS datetime) AS [Logout Date/Time],
-- CAST(szF5 AS numeric) AS [Documents Sent to QC],
-- CAST(szF6 AS numeric) AS [Documents Reconciled],
szF5,
szF6,
szF7 AS [Reject Reason],
CAST(szF8 AS datetime) AS [Report Date]
FROM dbo.F_Report_Data AS a
where isNumeric (szF3 + 'D0') = 0
or isNumeric (szF5 + 'D0') = 0
or isNumeric (szF6 + 'D0') = 0
And post any results that get returned.|||
I tried your suggestion and I got an error: "Error in list of function arguments: '=' not recognized. Unable to parse query text.
|||It's likely because you have data in the szF5 or szF6 columns that can't be converted to numeric. For example, if I had the value aaa in szF5, I would get that error. More common is if I have a zero length string in the column, that can't be converted to numeric and I would get the error. A null would be fine but a zero length string would cause the error.
-Sue
|||I would suggest (1) give the schema of the table and (2) give 5 sample rows of data from the table by doing a
select top 5 * from F_Report_Data
|||The 3 columns that I want to cast as numeric have only numbers in them.
1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM
The items in bold are thecolumns that I am trying to covnert/cast as numeric.
|||The 3 columns that I want to cast as numeric have only numbers in them.
Code Snippet
1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM
The items in bold are thecolumns that I am trying to covnert/cast as numeric.
Could you clarify by what you mean "Schema", it has been a while since my DB class and I am not a DBA. Every column is varchar(8000),null except for the PK which is (int, not null).
I awm giong to attempt to see if I can get the necessary results w/o convert/cast because there is supposed to be implicit conversion of varchar to numeric.
|||Actually, you have answered the schema question -- all columns are varchar(8000) except for the PK which is integer -- a "wow" table. That should be enough for now. Try running this query and see if any results are returned:
Code Snippet
select left(szF1, 25) as szF1,
left(szF2, 25) as szF2,
left(szF3, 25) as szF3,
left(szF4, 25) as szF4,
left(szF5, 25) as szF5,
left(szF6, 25) as szF6,
left(szF7, 25) as szF7,
left(szF8, 25) as szF8
from dbo.F_Report_data
where isDate(szF1) = 0
or isNumeric (szF3 + 'D0') = 0
or isDate(szF4) = 0
or isNumeric (szF5 + 'D0') = 0
or isNumeric (szF6 + 'D0') = 0
or isDate(szF8) = 0
Yes it returned a result, it's good that it returned a result but does that mean that we can/cannot convert/cast a varchar as a numeric? Thanks for your help!!
|||Please post a sampling of the results that you received. It means that you will might either need to change the table or modify the way you display the data so that it is properly "clensed" -- you have dirty data.
|||Here are the results:
Code Snippet
01/24/2007 09:58:03 AM admin 207 01/24/2007 02:08:55 PM 0 0 2007012401/24/2007 09:59:03 AM admin 0 01/24/2007 04:09:25 PM 1 0 Unable to read case numbe 20070124
01/24/2007 09:56:03 AM admin 0 01/24/2007 04:26:33 PM 0 3 20070124
01/25/2007 01:55:19 PM admin 3 01/25/2007 03:32:51 PM 0 0 20070125
01/25/2007 01:55:19 PM test 0 01/25/2007 04:11:09 PM 1 0 Unable to read case numbe 20070125
| szf1| |szF2| szF3 |--szF4-| szF5 sz F6 |-szF7-| |szF8|
I just noticed that the query trimmed szF7 (Where it says "Unable to read case numbe"), I will need that field a little larger for the text. I never would have thought that there would be so much trouble to convert/cast a varchar to a numeric in a view.|||That did not display the same way it did on my screen when I was typing it. I hope it isn't too confusing.|||
Can somebody point Erik to an article about cleaning up data? My tests weren't strong enough and I really am not interested in wasting Erik's time. I suspect that blanks in his data caused the isNumeric tests to fail.
Erik:
You can try this query; it will exhibit which test is failing:
Code Snippet
select isDate(rtrim(szF1)) as szF1isDate,isNumeric (rtrim(szF3) + 'D0') as szF3IsNumeric,
isDate(rtrim(szF4)) as szF4IsDate,
isNumeric (rtrim(szF5) + 'D0') as szF5IsNumeric,
isNumeric (rtrim(szF6) + 'D0') as szF6IsNumeric,
isDate(rtrim(szF8)) as szF8isDate,
left(szF1, 30) as szF1,
left(szF2, 30) as szF2,
left(szF3, 30) as szF3,
left(szF4, 30) as szF4,
left(szF5, 30) as szF5,
left(szF6, 30) as szF6,
left(szF7, 30) as szF7,
left(szF8, 30) as szF8
from dbo.F_Report_data
where isDate(rtrim(szF1)) = 0
or isNumeric (rtrim(szF3) + 'D0') = 0
or isDate(rtrim(szF4)) = 0
or isNumeric (rtrim(szF5) + 'D0') = 0
or isNumeric (rtrim(szF6) + 'D0') = 0
or isDate(rtrim(szF8)) = 0
I feel like I need a fresh set of eyes on this at this point. Help?
|||Here are the results of the query. I don't really know what they are saying though, could you give me pseudo code explanation of the query?
0,1,0,1,1,0,,Blank,0,,0,0,,
1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518
Erik:
To me the problem here is that the columns are not sufficiently typed; this is a design problem that should be fixed. If a column is intended to be used as a number it should be typed as numeric. Similarly, if a column is going to be used as a date it should be typed as a datetime column, not as a varchar. Here is the basic response to the records returned from the query:
0,1,0,1,1,0,,Blank,0,,0,0,,
this record failed for 3 reasons:
(1) The szF1 field is not a valid date (it is an empty string)
(2) The szF4 field is not a valid date (it is an empty string)
(3) the szF8 field is not a valid date (it is an empty string)
1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
this record faild for two reasons:
(1) The szF5 field is not numeric (it is an empty string)
(2) the szF6 field is not numeric (it is an empty string)
]
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)
Now, you might be able to use the NULLIF function to get around these problems since all of these are manifest when the column is an EMPTY string. If you are wanting to test for NUMERIC columns you might also want to give a look to this article about problems with the "isNumeric" built-in function:
http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
No comments:
Post a Comment