CODEWITHSUNDEEP
sqloracle-databasesqlplus
user974047
user974047

Reputation: 2295

Column alias name being truncated

Using SQL*Plus I'm trying to use a select union statement to combine the results of two columns from the same table like this:

select substr(startdate,4,3) milestone 
  from projects 
union 
select substr(enddate,4,3) milestone 
  from projects

Using the alias milestone for the column, but for some reason the result shows with the column name mil. It's being truncated for some reason, and I think the substr part is the problem since it grabs 3 characters from the stardate and enddate column. how should i fix this issue?

Upvotes: 2

Views: 4389

Answers (3)

Anjan Biswas
Anjan Biswas

Reputation: 7932

Does this Help-

SQL> set linesize 100
SQL> column milestone format a25
SQL> select substr(startdate,4,3) "milestone"
  from projects 
union 
select substr(enddate,4,3) "milestone" 
  from projects;

Upvotes: 0

Dave Richardson
Dave Richardson

Reputation: 5005

It's because you are using sql-plus. If I run the same query in my database GUI (PL/SQL developer) it works fine but in sqlplus it just truncates the column header to fit the data.

I'm no guru on sqlplus, but this fixes the issue:

SQL> column milestone format a20;
SQL> select substr(sysdate,4,3) milestone from dual;

results in:

MILESTONE
--------------------
OCT

Upvotes: 5

John D
John D

Reputation: 2365

You most likely need to set the column width.

COLUMN MILESTONE FORMAT A20
SET VERIFY ON
SET HEADING ON
SET PAGES 25
SET LINES 60

select substr(startdate,4,3) milestone 
  from projects 
union 
select substr(enddate,4,3) milestone 
  from projects

Upvotes: 3

Related Questions