Home » SQL & PL/SQL » SQL & PL/SQL » Data is set has Column name during Union Query (Oracle 9i, form 6i)
Data is set has Column name during Union Query [message #402030] Thu, 07 May 2009 06:34 Go to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Hi,

I Required to use the data in one column is used has a heading of the another column during the sql execution.


If it possible please reply me.


Regards,

C V S



Re: Data is set has Column name during Union Query [message #402034 is a reply to message #402030] Thu, 07 May 2009 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Data is set has Column name during Union Query [message #402178 is a reply to message #402030] Fri, 08 May 2009 00:15 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

hi michel,

The query i am using is follows.


select a.CODE,a.emplname,a.MTH,a.YRS,
sum(case when salcode = 'E001' then amount End) "GROSS" ,
sum(case when salcode = 'D002' then amount End) "PF",
sum(case when salcode = 'E002' then amount End) "NETPAY" 
From
(
(
select a.empcode"CODE",b.empname,monthno"MTH",yearno "YRS",c.saldesc, c.salcode,
Amount from salaryfile a,employmast b,salarydetails c 
where a.empcode = b.empcode 
and a.salcode = c.salcode 
and a.salcode in ('D002') and 
a.empcode ='2000742' 
and a.monthno <= 03 
and a.yearno =2009 
)
Union
(
select a.empcode"CODE",b.empname,monthno"MTH",yearno "YRS",c.saldesc, c.salcode,
amount  from salaryfile a,employmast b,salarydetails c 
where a.empcode = b.empcode 
and a.salcode = c.salcode 
and a.salcode in ('E001','E002') 
and a.empcode ='2000742' 
and a.monthno <= 03 
and a.yearno =2009 
)
) a
Group by  a.CODE,a.emplname,a.MTH,a.YRS 
order by a.yrs,a.mth;


In this query split the amount of salary for the employee by Salcode. Here i used the saldesc is the field i required to display as a heading for the amount column during the splitup.


Thanks and Regards,

C V S
Re: Data is set has Column name during Union Query [message #402190 is a reply to message #402178] Fri, 08 May 2009 00:58 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 07 May 2009 13:40
Post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Regards
Michel

Previous Topic: table partitioning
Next Topic: To get four top most records from a table
Goto Forum:
  


Current Time: Sun Dec 04 04:41:02 CST 2016

Total time taken to generate the page: 0.09292 seconds