Home » SQL & PL/SQL » SQL & PL/SQL » constant name
constant name [message #192151] Mon, 11 September 2006 04:56 Go to next message
Messages: 16
Registered: April 2006
Junior Member

I would like to add a constant in my select. Is it possible to give the constant an alias different then the value itself.

Explanation I add a constant 'programmer' in my select1 and then another constant in my select2 'designer'. Then I want to make a union and have one column with the label function and all appropriate values in it: 'programmer', 'designer' etc. Can I achieve this without using a function like NVL? If yes , how?
Re: constant name [message #192158 is a reply to message #192151] Mon, 11 September 2006 05:29 Go to previous messageGo to next message
Messages: 53
Registered: April 2005
Location: Bangalore


I don't know the purpose of this query here, anyways, its possible to do this but the table name you are using in the FROM clause should have atleast one column with the data type of your constant.

For example I am taking a Constant of type Character here and say, table1 doesn't have the data type of character family, then it will only display the 'Designer' in the output.

SELECT 'Programmer' FUNCTION FROM table1
SELECT 'Designer' FUNCTION FROM table2;

for the time being you can test this query using the dummy table DUAL for the proper output.

Thks & Rgds
Re: constant name [message #192178 is a reply to message #192151] Mon, 11 September 2006 06:31 Go to previous messageGo to next message
Messages: 52
Registered: August 2006
Location: India


In sql prompt you have decalre the variables;

create table var(a,b);
insert into var values(1,2);
insert into var values(2,4);
insert into var values(4,6);

variable var1 number;
variable var2 number;

:var1 := 2;

select * from var where a = :var1;

this may solue you problem


Mohan Reddy
Re: constant name [message #192639 is a reply to message #192158] Wed, 13 September 2006 02:32 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The first response to this is partly correct, but adds a lot of restrictions that are false.

You may provide a column alias to a constant in a select in exactly the same way that you can provide a column alias to a column from a table.


create table temp_1 (col_1 date);

create table temp_2 (col_1 date);

insert into temp_1 (col_1) values (sysdate);

insert into temp_2 (col_1) values (sysdate);

SELECT 'Programmer' col_2 FROM temp_1
SELECT 'Designer' col_2 FROM temp_1;

Previous Topic: last value in cursor active set
Next Topic: how to import.....
Goto Forum:

Current Time: Sat Oct 22 07:41:43 CDT 2016

Total time taken to generate the page: 0.10390 seconds