Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-26 : 08:53:29
|
| Conor writes "Hi,we have recently migrated to SQL Server 2000 from Oracle. We are currently is the process of migrating our applications. As we would ideally like to have a generic application we are trying to modify the SQL to work accross both databases. The main problem I have encountared is the Character Conversion Function, in Oracle this was TO_CHAR and SQL Server uses CONVERT(Character,Field Name).My question is this. Is there any way what we could keep the TO_CHAR statement in our SQL and create a function or stored procedure that would CONVERT the data when it sees the TO_CHAR statement in a SQL.Any help would be greatly appreciated." |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-26 : 08:58:42
|
yes, you can write a scalar user-defined function to do this....however, quote:
we are trying to modify the SQL to work accross both databases.
... don't bother. You are going to end up with code that doesn't work very well in either. PL/SQL and T-SQL are too different.<O> |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-26 : 08:59:26
|
quote:
As we would ideally like to have a generic application we are trying to modify the SQL to work accross both databases
I know this sounds like it's a good idea, but it's not. Trust me when I tell you, I've tried this, and gave up after about 2 days, and I've seen co-workers spend weeks and give up too.The problem is that in generalizing the SQL to work with both, you compromise the performance of both. The best you can do is to write stored procedures in each language (PL/SQL and T-SQL) that have the same names and parameters. That will at least allow you to write an application that calls them in the same way. But if you are going to build SQL strings and execute them, you'll end up crying very quickly.In any case, while you can write a TO_CHAR function in SQL 2000, it'll run like crap vs. the CAST and CONVERT features (CAST works in Oracle BTW). But, you can't create a || operator to perform string concatenation, which is does using the + in T-SQL. Not to mention JOIN syntax; Oracle outer joins simply don't work in T-SQL, and the Oracle inner join syntax, while it works, does not optimize as well as the INNER JOIN clause in ANSI SQL/T-SQL. Yeah, what he said!Edited by - robvolk on 07/26/2002 09:00:20 |
|
|
|
|
|
|
|
|
Creating Functions to perform data conversion