| Check data types of columns from large query [message #656286] |
Sun, 02 October 2016 01:56  |
 |
sparamanga
Messages: 1
Registered: October 2016
|
Junior Member |
|
|
Hi,
I have a large query which selects several columns through numerous joins and aliases from several different databases. The query is around 90 lines in length.
Due to its size its quite difficult to read.
I have tried to use the data set provided by the query by minusing a similar data set from another table.
So.. I have 6 columns from the large query and I have used MINUS to subtract the same 6 columns from another table.
This does produce a result set, but to test it is giving me the correct number of rows i likmited the query and the table with WHERE criteria to produce the same 2 rows. I then minused the 2 rows from the table from the large query, expecting the result to be 0 rows displayed. Howeve, i got 2 rows still!
I think maybe this is becasue the columns between the query and the table may have different character types and this would affect the result?
To check this I wanted to know if there is a quick way to check the data types of each column produced in the large query after running it?
Is there a right click menu etc that will doisplay the data type for a result set displayed by a query?
Thanks
R
|
|
|
|
|
|
|
|
| Re: Check data types of columns from large query [message #656311 is a reply to message #656286] |
Sun, 02 October 2016 22:53  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- If you have a query with some joins that returns a limited number of rows, like this:
SCOTT@orcl_12.1.0.2.0> select d.deptno, d.dname, e.empno, e.ename
2 from dept d, emp e
3 where d.deptno = e.deptno
4 and d.deptno = 10
5 /
DEPTNO DNAME EMPNO ENAME
---------- -------------- ---------- ----------
10 ACCOUNTING 7782 CLARK
10 ACCOUNTING 7839 KING
10 ACCOUNTING 7934 MILLER
3 rows selected.
-- and you have another query on a single table that appears to return the same data:
SCOTT@orcl_12.1.0.2.0> select deptno, dname, empno, ename
2 from test
3 /
DEPTNO DNAME EMPNO ENAME
---------- -------------- ---------- --------------
10 ACCOUNTING 7782 CLARK
10 ACCOUNTING 7839 KING
10 ACCOUNTING 7934 MILLER
3 rows selected.
-- and you use minus to subtract the data from one query from the other, but still get the same rows:
SCOTT@orcl_12.1.0.2.0> select d.deptno, d.dname, e.empno, e.ename
2 from dept d, emp e
3 where d.deptno = e.deptno
4 and d.deptno = 10
5 minus
6 select deptno, dname, empno, ename
7 from test
8 /
DEPTNO DNAME EMPNO ENAME
---------- -------------- ---------- --------------
10 ACCOUNTING 7782 CLARK
10 ACCOUNTING 7839 KING
10 ACCOUNTING 7934 MILLER
3 rows selected.
-- in order to compare the data types easily, you can create a view of your query:
SCOTT@orcl_12.1.0.2.0> create or replace view your_query as
2 select d.deptno, d.dname, e.empno, e.ename
3 from dept d, emp e
4 where d.deptno = e.deptno
5 and d.deptno = 10
6 /
View created.
-- then you can describe the view and describe the other table and compare the differences:
SCOTT@orcl_12.1.0.2.0> describe your_query
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
SCOTT@orcl_12.1.0.2.0> describe test
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER
DNAME CHAR(14)
EMPNO NUMBER
ENAME CHAR(14)
SCOTT@orcl_12.1.0.2.0>
In the above example, the differences are in the usage of CHAR versus VARCHAR2 data types for the dname and ename columns. The CHAR data type pads the data with spaces up to 14 characters, whereas the VARCHAR2 data type does not, causing the data to be different.
[Updated on: Sun, 02 October 2016 22:55] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
