Home » SQL & PL/SQL » Client Tools » Check data types of columns from large query (Oracle Sql Developer)
Check data types of columns from large query [message #656286] Sun, 02 October 2016 01:56 Go to next message
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 #656288 is a reply to message #656286] Sun, 02 October 2016 03:16 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

You need to show the query, the result, and DESCRIBE the tables. If you format the query properly (for example, use the formatter here http://www.dpriver.com/pp/sqlformat.htm) 90 lines sould be OK to understand.
Please use SQL*Plus, and use[code] tags when you copy/paste.
Re: Check data types of columns from large query [message #656302 is a reply to message #656288] Sun, 02 October 2016 07:28 Go to previous messageGo to next message
BlackSwan
Messages: 25715
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/thread/3977337
Re: Check data types of columns from large query [message #656311 is a reply to message #656286] Sun, 02 October 2016 22:53 Go to previous message
Barbara Boehmer
Messages: 8749
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

Previous Topic: PLSQLDEVELOP V.10
Next Topic: Set path in variable
Goto Forum:
  


Current Time: Sat Nov 18 13:41:53 CST 2017

Total time taken to generate the page: 0.01711 seconds