Home » SQL & PL/SQL » SQL & PL/SQL » select with as column header reanming not working
select with as column header reanming not working [message #305989] Wed, 12 March 2008 08:40 Go to next message
marttali
Messages: 2
Registered: March 2008
Junior Member
Hi helpful people Smile
Here goes:
i have following query:
SELECT
RIMAKUOR.RIMAKNRO,
RIMAKUOR.TOIMITT ,
RIMAKUOR.PUULAJI ,
RIMAKUOR.DIMENSIO,
RIMAKUOR.LAATU,
RIMAKUOR.KUIVASTE,
RIMAKUOR.RIMAKM3,
RIMAKUOR.RIMAKKPL,
RIMAKUOR.KUIVPVM,
to_char(RIMAKUOR.KESKIPIT) ,
RIMAKUOR.RAIDE,
to_char(NULL) ,
to_char(NULL)
FROM TUOT.RIMAKUOR
UNION ALL
SELECT
PAKETREK.PAKETNRO ,
PAKETREK.TOIMITT,
PAKETREK.PUULAJI,
PAKETREK.DIMENSIO,
PAKETREK.LAATU,
PAKETREK.KUIVASTE,
PAKETREK.YHTM3,
PAKETREK.YHTKPL,
PAKETREK.PAVALPVM,
to_char(Avg(PAKETPIT.PITUUS)),
to_char(NULL) ,
PAKETREK.VARASTOS,
PAKETREK.PTRYH
FROM TUOT.PAKETPIT PAKETPIT, TUOT.PAKETREK PAKETREK
WHERE
(PAKETPIT.PAKETNRO=PAKETREK.PAKETNRO)
AND
(PAKETREK.PAKTILA='0')
GROUP BY PAKETREK.PAKETNRO,
PAKETREK.TOIMITT,
PAKETREK.PUULAJI,
PAKETREK.DIMENSIO,
PAKETREK.LAATU,
PAKETREK.KUIVASTE,
PAKETREK.YHTM3,
PAKETREK.YHTKPL,
PAKETREK.PAVALPVM,
PAKETREK.VARASTOS,
PAKETREK.PTRYH

It's Excel's database query from a very old Oracle database (10 years old maybe, possibly more) What i would like to achieve is the renaming of the column headers with "as" but it doesn't work for me.
I tried SELECT
RIMAKUOR.RIMAKNRO AS "package number" and SELECT
RIMAKUOR.RIMAKNRO AS 'package number' and all kind of other versions, nada..
Re: select with as column header reanming not working [message #305990 is a reply to message #305989] Wed, 12 March 2008 08:48 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
completely unreadable. Have a wee look at the forum guidelines. It will show you how to use [code]..your code here[/code] tags in order to help others read your code.
Re: select with as column header reanming not working [message #305992 is a reply to message #305989] Wed, 12 March 2008 08:50 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>i would like to achieve is the renaming of the column headers with "as" but it doesn't work for me.
Then do NOT use "as".

SELECT RIMAKUOR.RIMAKNRO "package number", ....
Re: select with as column header reanming not working [message #306000 is a reply to message #305989] Wed, 12 March 2008 09:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
marttali wrote on Wed, 12 March 2008 14:40
.. but it doesn't work for me.

Define "it doesn't work for me."
Do you get an error? Do you not get the expected result?
If so, what error? What result did you expect?
Re: select with as column header reanming not working [message #306068 is a reply to message #305989] Wed, 12 March 2008 13:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
I am going to guess that the problem might be that the width of your column is shorter than the width of your desired column heading. If that is the problem, then you need to modify the column length somehow, like by padding it using RPAD or specifying a column format from SQL*Plus. Please see the demonstration below.


SCOTT@orcl_11g> SELECT DUAL.DUMMY AS "package number" FROM DUAL
  2  /

p
-
X

SCOTT@orcl_11g> SELECT RPAD (DUAL.DUMMY, 14) AS "package number" FROM DUAL
  2  /

package number
--------------
X

SCOTT@orcl_11g> COLUMN "package number" FORMAT A14
SCOTT@orcl_11g> SELECT DUAL.DUMMY AS "package number" FROM DUAL
  2  /

package number
--------------
X

SCOTT@orcl_11g> 


./fa/4003/0/
  • Attachment: CRYSTA~12.GIF
    (Size: 11.92KB, Downloaded 305 times)

[Updated on: Wed, 12 March 2008 13:25]

Report message to a moderator

Re: select with as column header reanming not working [message #306222 is a reply to message #306068] Thu, 13 March 2008 06:12 Go to previous message
marttali
Messages: 2
Registered: March 2008
Junior Member
Thanks to everyone who tried to help !
I tried your versions but still didn't get the header names in excel as i expected.
Eventually what worked is the following:
SELECT PAKINUMBER,PÄRITOLU,PUULIIK,RISTLÕIGE,KVALITEET,NIISKUS, 
PAKITÜÜP,LAO_ASUKOHT, M3, TÜKKE,  KESKMINE_PIKKUS, RAIDE, SÜNNIPÄEV
FROM 
(
SELECT
RIMAKUOR.RIMAKNRO AS PAKINUMBER, 
RIMAKUOR.TOIMITT AS PÄRITOLU ,  
RIMAKUOR.PUULAJI AS PUULIIK,  
RIMAKUOR.DIMENSIO AS RISTLÕIGE,  
RIMAKUOR.LAATU AS KVALITEET,  
RIMAKUOR.KUIVASTE AS NIISKUS,
to_char(NULL) AS PAKITÜÜP,
to_char(NULL) AS LAO_ASUKOHT,
RIMAKUOR.RIMAKM3 AS M3, 
RIMAKUOR.RIMAKKPL AS TÜKKE,   
to_char(RIMAKUOR.KESKIPIT) AS KESKMINE_PIKKUS ,
RIMAKUOR.RAIDE AS RAIDE,
RIMAKUOR.KUIVPVM AS SÜNNIPÄEV  
FROM TUOT.RIMAKUOR
)
UNION ALL
SELECT  
PAKETREK.PAKETNRO ,  
PAKETREK.TOIMITT, 
PAKETREK.PUULAJI,  
PAKETREK.DIMENSIO,  
PAKETREK.LAATU,  
PAKETREK.KUIVASTE,
PAKETREK.PTRYH,
PAKETREK.VARASTOS,
PAKETREK.YHTM3,  
PAKETREK.YHTKPL,     
to_char(Avg(PAKETPIT.PITUUS)),
to_char(NULL) ,
PAKETREK.PAVALPVM  
FROM TUOT.PAKETPIT PAKETPIT, TUOT.PAKETREK PAKETREK
WHERE 
(PAKETPIT.PAKETNRO=PAKETREK.PAKETNRO) 
AND 
(PAKETREK.PAKTILA='0')
GROUP BY PAKETREK.PAKETNRO,  
PAKETREK.TOIMITT, 
PAKETREK.PUULAJI,  
PAKETREK.DIMENSIO,  
PAKETREK.LAATU,  
PAKETREK.KUIVASTE,
PAKETREK.YHTM3,  
PAKETREK.YHTKPL,     
PAKETREK.VARASTOS,
PAKETREK.PTRYH,
PAKETREK.PAVALPVM
Previous Topic: can we create a table
Next Topic: referential integrity
Goto Forum:
  


Current Time: Sun Dec 04 23:05:30 CST 2016

Total time taken to generate the page: 0.07352 seconds