Browse by Tags
All Tags »
Databases (
RSS)
Well, I am impressed... I know of Oracle's START WITH and CONNECT BY PRIOR clause that is a great help in selecting hierarchical data, but up till now I have only used it to select data top-down. TOP-DOWN: SELECT LEVEL , g.groupid, s.groupname, g.parentid FROM grouphierarchy g INNER JOIN sitegroups s ON g.groupid = s.groupid CONNECT BY PRIOR g.groupid = g.parentid START WITH parentid = 0 I have functionality in my code which links different groups to each other, the only rule being that lets say...
We have a service that needs to synchronise data between MySQL and Oracle and it used to take AGES!!! Firstly we tried to optomize the queries, made sure the indexes was there, etc - but didn't get much improvement. In most of the Oracle queries we used the lower function to compare data... we removed that and looked at the execution plan - the difference was astounding!!!! In Oracle, obviously, as it is case sensitive, when you use any function like lower, upper, etc it skips the indexes as it wasn...
Something I needed today... I wish this was standarized, but it is not, so here are some examples: SQL Server: SELECT TOP 10 product, descr, email FROM products ORACLE: SELECT product, descr, email FROM products WHERE ROWNUM <= 10 MySQL: SELECT product, descr, email FROM products LIMIT 10 powered by IMHO 1.2 Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!
A colleague of mine showed me the other day that you can use Query Analyzer to get data from normal text files by using the Microsoft Jet Driver. So say you get data in a text file, but you don't want to import everything, then you can first query the text file getting only what you need! I was quite impressed... So what do you need? 1. test.txt with the following content: ID;Name;Tel 1;Maryke;011 2;Shantelle;012 2. schema.ini with the following definition: [test.txt] Format=Delimited(;) ColNameHeader...
I needed to retrieve data from Oracle in XML format with all the detail as attributes: userid ="1" firstname ="John" surname ="Doe" / > userid ="2" firstname ="Jane" surname ="Gregory" / > userid ="1" firstname ="Samantha" surname ="Miller" / > The following procedure did the trick: SELECT XMLElement ("E", XMLAttributes(userid, commonname as firstname, surname)) FROM UserProfile WHERE uniqueno LIKE '01589%'; If you need to get the data as elements, you can use the following: SELECT XMLElement...
Yesterday we needed to upgrade our server's Oracle Client from 9i to 9i Release 2. Got the error “Unable to load DLL (OraOps9.dll)” after we started using the ODP.Net provider and hoped this would fix the error. And I thought it was a simple thing to do. So I came in early, 'deinstalled' the old one, which actually doesn't remove the Oracle Home (ora90 at that stage), and also doesn't update the registry (this I only found out later). I saw that the old folders & files were still...
I can't say that the last few days at work were the most pleasurable days. Apparently the proxy we went through's security wasn't up to scratch, so the only way to access the internet now is through the old proxy that has screwed up marshal software on it which I have blogged about previously ... no SharpReader, no posting of comments on blogs, no posting on my own blog, no downloads, blocked sites, etc etc. Needless to say, very frustrating. And then I just started working with SQL Reporting Services...
This is an error that I have received a couple of times previously. The first time I couldn't understand what was going on, as I didn't change my SQL password or anything... After some investigation I saw that this is due to SQL Server logging on with a domain account and my domain password expired and I changed it. Quickfix: 1. Go to Services in Administrative tools. 2. Open MSSQLSERVER and click on the Log On type. 3. Enter your domain password. And voila! - your SQL is back to normal. What you...
Stumbled across it via Julie Lerman and Clemens Vasters ... Kimberly L. Tripp started to blog . I went over to her blog and she has loads of usefull content on there on SQL. If you use a lot of SQL, subscribe to her blog. Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!