OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Question

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Question [message #23021]

Tue, 12 November 2002 17:10

Rizwan Qazi
Messages: 135
Registered: August 2002

Senior Member

I have tables

desc xyz;
Name Type
----- --------
A_NO VARCHAR2(10)

desc aaa;
Name Type
----- --------
B_NO VARCHAR2(10)
CXP VARCHAR2(20)

select * from xyz;

A_NO
----------
111111111
211111111
221111111
222111111
222211111

select * from aaa;

B_NO CXP
---------- --------------------
111111111 sbc
211111111 sbc
221111111 sbc
222111111 sbc
222211111 sbc

When I type

select A.A_NO, B.* from xyz A, aaa B where A_NO=B_NO;

A_NO B_NO CXP
---------- ---------- --------------------
111111111 111111111 sbc
211111111 211111111 sbc
221111111 221111111 sbc
222111111 222111111 sbc
222211111 222211111 sbc

When I type this

COL A_NO NOPRINT

the query returns

select A.A_NO, B.* from xyz A, aaa B where A_NO=B_NO;

B_NO CXP
---------- --------------------
111111111 sbc
211111111 sbc
221111111 sbc
222111111 sbc
222211111 sbc

However, when I type

create table moo as select A.A_NO, B.* from xyz A, aaa B where A_NO=B_NO;

the table structure created is

desc moo;
Name Type
----- --------
A_NO VARCHAR2(10)
B_NO VARCHAR2(10)
CXP VARCHAR2(20)

How can I create a table structure with the same query using * and not including field A_NO in the structure?

Rizwan

Report message to a moderator