| Help - Urgent [message #89803] |
Thu, 29 July 2004 02:36  |
Sam
Messages: 255
Registered: April 2000
|
Senior Member |
|
|
In my report
Query Q1 is
------------
select 'OPL' type,count(*) apr
from she_manhour_mas
where to_char(mdate,'MON-YY')='APR-04'
Group by 'OPL'
union
select 'CONTRACTOR' type, count(*) apr
from she_manhour_mas
where to_char(mdate,'MON-YY')='APR-04'
group by 'CONTRACTOR'
order by type desc;
Query Q2 is
-----------
select 'OPL' type,count(*) may
from she_manhour_mas
where to_char(mdate,'MON-YY')='MAY-04'
union
select distinct 'CONTRACTOR' type, count(*) may
from she_manhour_mas
where to_char(mdate,'MON-YY')='MAY-04'
order by type desc;
Q1 is Linked with Q2 by Type.
Report Layout Style is Group Left.
In May there is no Records.
But the Result that is coming is
Type1 Apr May
----- ---- ----
OPL 14 0
0
CONTRACTOR 14 0
0
But the Result Should be
Type1 Apr May
----- ----- ----
OPL 14 0
CONTRACTOR 14 0
How to do it.
Thanks in Advance
|
|
|
|
| Re: Help - Urgent [message #89819 is a reply to message #89803] |
Mon, 02 August 2004 03:53  |
Karthik
Messages: 63
Registered: February 2000
|
Member |
|
|
Don't use UNION,
Roughly I'm giving you an idea,
select OPL_APR.TYPE,OPL_MAY,CON_APR,CON_MAY,
(select 'OPL' type,count(*) apr
from she_manhour_mas
where to_char(mdate,'MON-YY')='APR-04'
Group by 'OPL' ) OPL_APR,
(select 'CONTRACTOR' type, count(*) apr
from she_manhour_mas
where to_char(mdate,'MON-YY')='APR-04'
group by 'CONTRACTOR') CON_APR,
(select 'OPL' type,count(*) may
from she_manhour_mas
where to_char(mdate,'MON-YY')='MAY-04') OPL_MAY
(select distinct 'CONTRACTOR' type, count(*) may
from she_manhour_mas
where to_char(mdate,'MON-YY')='MAY-04') CON_MAY
from
<give some table>
<order by 1,2>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
