May 9, 2012 at 10:10 am
In SQL 2008R2 database I'm using openquery and a link to an oracle database. It works ok when run directly, but I created a view with it and that has an error.
select * from openquery (LAWSONPROD,
'SELECT EMPLOYEE, R_PLAN, AVAIL_HRS_BAL, MSTR_END
FROM PROD.TAEEMASTER
where MSTR_END = TO_DATE(''01-JAN-1700'', ''DD-MON-YYYY'') ')
The error:
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
Is there something about a sql server view that won't work with this query?
Any ideas to get around it aer greatly appreciated.
Thanks
May 9, 2012 at 2:32 pm
Denise McMillan (5/9/2012)
In SQL 2008R2 database I'm using openquery and a link to an oracle database. It works ok when run directly, but I created a view with it and that has an error.select * from openquery (LAWSONPROD,
'SELECT EMPLOYEE, R_PLAN, AVAIL_HRS_BAL, MSTR_END
FROM PROD.TAEEMASTER
where MSTR_END = TO_DATE(''01-JAN-1700'', ''DD-MON-YYYY'') ')
The error:
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
Is there something about a sql server view that won't work with this query?
First thing is to determine which instance of MSTR_END is causing the issue - you can easily test by ...
1- Taking MSTR_END out of select list so to check if that instance caused the issue
2- Taking whole WHERE clause out of the query so to check if that instance caused the issue
If MSTR_END on select list is causing the issue replace it with:
TO_CHAR(MSTR_END,"DD-MON-YYYY")
If MSTR_END on WHERE clause is causing the issue replace the condition with:
where TO_CHAR(MSTR_END,"DD-MON-YYYY") = ''01-JAN-1700''
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 9, 2012 at 2:50 pm
I took out mstr_end out of the select portion and it works. I didn't really need to select it, just had it while working on the request.
Thanks so much this really helped. I was focusing on the where portion!
May 9, 2012 at 9:52 pm
PaulB-TheOneAndOnly (5/9/2012)
If MSTR_END on WHERE clause is causing the issue replace the condition with:where TO_CHAR(MSTR_END,"DD-MON-YYYY") = ''01-JAN-1700''
IIRC, that'll make for a non-SARGable search predicate just like in T-SQL. I believe the real problem might be the fact that the earliest date the DATETIME datatype will support is "01-Jan-1753".
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2012 at 2:28 pm
I still don't understand why the query works, but not as a view.
Any thoughts?
Thanks so much.
May 10, 2012 at 2:33 pm
Jeff Moden (5/9/2012)
PaulB-TheOneAndOnly (5/9/2012)
If MSTR_END on WHERE clause is causing the issue replace the condition with:where TO_CHAR(MSTR_END,"DD-MON-YYYY") = ''01-JAN-1700''
IIRC, that'll make for a non-SARGable search predicate just like in T-SQL. I believe the real problem might be the fact that the earliest date the DATETIME datatype will support is "01-Jan-1753".
No worries about the non-sargable predicate, IF an index is on that column we always have the ability to include an index hint on the statement 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 10, 2012 at 2:35 pm
Denise McMillan (5/10/2012)
I still don't understand why the query works, but not as a view.Any thoughts?
My guess is that the view is forcing a data type conversion that was not happening when the query was executed on its own.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 10, 2012 at 2:37 pm
Denise McMillan (5/9/2012)
I took out mstr_end out of the select portion and it works. I didn't really need to select it, just had it while working on the request.Thanks so much this really helped. I was focusing on the where portion!
Glad it worked out.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 10, 2012 at 5:36 pm
PaulB-TheOneAndOnly (5/10/2012)
Jeff Moden (5/9/2012)
PaulB-TheOneAndOnly (5/9/2012)
If MSTR_END on WHERE clause is causing the issue replace the condition with:where TO_CHAR(MSTR_END,"DD-MON-YYYY") = ''01-JAN-1700''
IIRC, that'll make for a non-SARGable search predicate just like in T-SQL. I believe the real problem might be the fact that the earliest date the DATETIME datatype will support is "01-Jan-1753".
No worries about the non-sargable predicate, IF an index is on that column we always have the ability to include an index hint on the statement 😉
That won't give you a seek though. In Oracle, you'd need a function index for a seek to occur, IIRC.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 14, 2012 at 8:10 am
Jeff Moden (5/10/2012)
PaulB-TheOneAndOnly (5/10/2012)
Jeff Moden (5/9/2012)
PaulB-TheOneAndOnly (5/9/2012)
If MSTR_END on WHERE clause is causing the issue replace the condition with:where TO_CHAR(MSTR_END,"DD-MON-YYYY") = ''01-JAN-1700''
IIRC, that'll make for a non-SARGable search predicate just like in T-SQL. I believe the real problem might be the fact that the earliest date the DATETIME datatype will support is "01-Jan-1753".
No worries about the non-sargable predicate, IF an index is on that column we always have the ability to include an index hint on the statement 😉
That won't give you a seek though. In Oracle, you'd need a function index for a seek to occur, IIRC.
That's not correct. Index hint will force a seek by key.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 14, 2012 at 7:51 pm
PaulB-TheOneAndOnly (5/14/2012)
Jeff Moden (5/10/2012)
PaulB-TheOneAndOnly (5/10/2012)
Jeff Moden (5/9/2012)
PaulB-TheOneAndOnly (5/9/2012)
If MSTR_END on WHERE clause is causing the issue replace the condition with:where TO_CHAR(MSTR_END,"DD-MON-YYYY") = ''01-JAN-1700''
IIRC, that'll make for a non-SARGable search predicate just like in T-SQL. I believe the real problem might be the fact that the earliest date the DATETIME datatype will support is "01-Jan-1753".
No worries about the non-sargable predicate, IF an index is on that column we always have the ability to include an index hint on the statement 😉
That won't give you a seek though. In Oracle, you'd need a function index for a seek to occur, IIRC.
That's not correct. Index hint will force a seek by key.
Even when the query isn't SARGable because of the function?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2012 at 7:09 am
Jeff's correct here - there's no appreciable difference between SQL Server and Oracle for this kind of predicate.
Wrapping a char function around the column rather than converting the scalar parameter to the appropriate date type will negate the use of an appropriate index.
Easy enough to test:
create table test (col1 int, col2 date);
Insert Into Test
Select rownum, trunc(sysdate)+rownum from user_tables ;
create index IDX_Test_Col2 on Test(Col2);
commit;
Select * From Test Where to_char(Col2,'YYYY-MM-DD')='2012-05-17'; --explain plan attached - to_char
Select * From Test Where Col2=to_date('2012-05-17','YYYY-MM-DD'); --explain plan attached - to_date
May 16, 2012 at 8:48 am
Jeff Moden (5/14/2012)
PaulB-TheOneAndOnly (5/14/2012)
Jeff Moden (5/10/2012)
PaulB-TheOneAndOnly (5/10/2012)
Jeff Moden (5/9/2012)
PaulB-TheOneAndOnly (5/9/2012)
If MSTR_END on WHERE clause is causing the issue replace the condition with:where TO_CHAR(MSTR_END,"DD-MON-YYYY") = ''01-JAN-1700''
IIRC, that'll make for a non-SARGable search predicate just like in T-SQL. I believe the real problem might be the fact that the earliest date the DATETIME datatype will support is "01-Jan-1753".
No worries about the non-sargable predicate, IF an index is on that column we always have the ability to include an index hint on the statement 😉
That won't give you a seek though. In Oracle, you'd need a function index for a seek to occur, IIRC.
That's not correct. Index hint will force a seek by key.
Even when the query isn't SARGable because of the function?
Exactly. That's the beauty of the hint - you are telling Oracle "I know better, trust me and start using that damn index!!!" 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 16, 2012 at 8:57 am
PaulB-TheOneAndOnly (5/16/2012)
Exactly. That's the beauty of the hint - you are telling Oracle "I know better, trust me and start using that damn index!!!" 😀
The problem with this is that although you can tell it to 'use' the index, the query inherently cannot seek within that index if you wrap a function around the column. If it uses the index, it's still doing a full scan of it, it's not 'using' it to perform a seek.
A date type is an internal numerical representation of a date. If you wrap a function around this date to convert it to a string, there's no index that holds that string representation, therefore the only way to compare this to anything else is to apply the function to every row value within the table (or index), convert it to a string, then compare it to the value on the other side of the condition.
May 16, 2012 at 10:45 am
You have a good point Howard which means Jeff also has it.
In a real world situation, facing that scenario we could always go back to the function based index Jeff suggested before - lucky us this would not be needed at all 'cause the predicate was not the problem.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply