Ashish Ashish - 3 months ago 7
SQL Question

column name in alias is getting shortened

I am using below query to create a table

CREATE TABLE FINAL_TBL AS SELECT /*+ leading(e) use_hash(t) full(t) parallel(t,2) */
t.customer_id
, max(decode(type,'C',t.num,null)) AS CONTACT_TELNO
, max(decode(type,'H',t.num,null)) AS HOME_TELNO
, max(decode(type,'W',t.num,null)) AS WORK_TELNO
, max(decode(type,'O',t.num,null)) AS OTHER_TELNO
from TABLE_A e, TMP_NUMBERS t
where t.num = e.num
and e.status = 'P'
group by t.customer_id
order by 1;


But the table is getting created with the truncated column name as below
CUSTOMER_ID CONTACT_TE HOME_TELNO WORK_TELNO OTHER_TELN

Why is So? How can I fix it?

Answer

It sounds like you must be running this in SQL*Plus... you need to use the command COLUMN with the FORMAT option, like so: column CONTACT_TELEPHONE format a18 (notice no semicolon since this is not a SQL command, it only applies to your interface). The SQL query runs as you expected, but then SQL*Plus formats the result according to its own settings.

Comments