I am looking at a strange behaviour of oracle 8.1.7 on IBM AIX, which is working fine with Informix and DB2 on the same machine. I have a SQL statement embeded in a C program with a SQL union in it. This SQL statement is fetching some columns which have C-datatypes as char, short, long and float. When I get data back from Oracle, datatypes of all the numeric data types is double only. (Informix and DB2 return me proper data types for this query.) Strangely, just for a trial, I commented the union and one of the select statements, and I got back the correct data types! Is there any problem with union clause, or do I have to treat it specially in the pro*c program? If yes, then could you please elaborate.
I don't have too much experience with Informix and DB2. But I can tell you about Oracle. To illustrate what I believe to be your problem, a short example will help. Let's suppose that I have two tables defined as follows:
Table_A (name CHAR2(20), id NUMBER(20)) Table_B (name CHAR2(30), id NUMBER(10))
Now I issue the following query:
SELECT * FROM table_a UNION SELECT * FROM table_b;
The first column returned from this query will be 'name'. But how "big" will it be? It will be the bigger of the two fields. In this case, the 'name' column will be 30 characters long. If it was shorter, then we chop of data from table_b. What about the 'id' column? Again, it will be the bigger of the two. In this case, 20. Any shorter and it would chop of data from table_a. So the UNION clause takes this into account and adjusts accordingly. I suspect that this is your problem. When you remove the UNION clause, it doesn't have to adjust for all columns in the result set.
For More Information
- What do you think about this answer? E-mail the Edtior at email@example.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle and SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.