December 4, 2015 at 12:10 am
Hi
I had a stage table where we dump all the data in nvarchar format, with the help of stored proc we transform the data and insert into transform table.for example the stage table has date and amount field which are nvarchar format we transformed these field into respective datatype date and decimal(38,16) and insert into transformed table. The stage table is used by other application too, so there is a chance of inserting invalid data in the table but we can identify our valid data through ID field.
As we know when we convert string to date or string to float it will cause error if there is invalid data.
My question is when we know our valid data by applying filter ID column is there any chance of getting conversion error even though that data does not satisfy our filter condition but it is available in table.
We had mockup the issue in the below code
Here ID=1 column will identify our vaild data based on the query it will not give error as the invalid data of date and amount is present in ID =2,3
Declare @stageTable table(Id int, datestring varchar(150),amount varchar(200))
INSERT INTO @stageTable
SELECT 1,'20150831','500'
UNION ALL
SELECT 2,'20150830','asdfdff'
UNION ALL
SELECT 3,'sdsfsf','452342'
UNION ALL
SELECT 1,'20150831','3000'
UNION ALL
SELECT 3,'20150831','5632849'
UNION ALL
SELECT 1,'20150831','45599'
--generate eresult without error
select CAST(datestring AS date ) as dates, CAST(amount AS decimal(38,16)) FROM @stageTable
WHERE id = 1
AND CAST(datestring AS date) = '2015/08/31'
--generate error as expected
select CAST(datestring AS date ) as dates, CAST(amount AS decimal(38,16)) FROM @stageTable
WHERE id = 3
AND CAST(datestring AS date) = '2015/08/31'
My colleague says there is a chance of getting error if the invalid data is available in table, but with this this mockup i am not able to identify that issue. Is it possible for sql server to generate error eventhoug that invalid data does not satisfy the filter condition .
Thanks
December 4, 2015 at 3:04 am
You have a cast in the where statement which is commented out. If this is part of the actual query it is possible that SQL will evaluate this first before the ID predicate which removes the invalid dates. You can't control the order in which SQL evaluates the predicates.
December 4, 2015 at 3:06 am
Query optimizer is free to choose any order of WHERE predicates evaluation allowed by operations priority. So under circumstances the predicate type of what you commented out may be evaluated before ID=1 and the conversion error may be raised.
No tests can prove that QE will choose this particular evaluation order among allowed ones now and forever.
December 4, 2015 at 3:13 am
Thank you guys for the reply...yeah that date filter will be there...i have removed the comment.
So there is no way which part of predicate will execute at runtime first that is it depends on query optimizer to choose the predicate at runtime.
What are the best practice to avoid that.Should i export the valid data to temporary table and then apply the Date predicate
December 4, 2015 at 3:44 am
You could use a CTE or subquery to initially select the rows with valid dates or you could replace the CAST with TRY_CAST which will return null for invalid values rather than an error.
December 4, 2015 at 7:52 am
FridayNightGiant (12/4/2015)
You could use a CTE or subquery to initially select the rows with valid dates...
Actually, that's also prone to error. Even with a view, the error could appear.
I like the option of using TRY_CAST if this is really a 2012+ instance.
December 4, 2015 at 10:27 pm
CAST(case id=1 then datestring else null end AS date) = '2015/08/31'
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply