| Problem in Query during creating Report [message #437759] |
Tue, 05 January 2010 23:13  |
allianz2010
Messages: 101
Registered: October 2009
Location: Pakistan
|
Senior Member |
|
|
I m creating a report in Report 6i and i create it..
but not i want to make some change in this that...
i have two table Master and Detail Table...
First when i create report i just use Detail Table and create the report ...
but now requirement is change that...
i want to retrieve just those record which dates between two dates....
and date field is in Master table and i m use Group by clause in my previous report.. and when i join these table then its say that which field i add in my report its not group by field...
and when i add this field too in group by clause then my retrieved record not retrieve correctly......
...
|
|
|
|
|
|
|
|
| Re: Problem in Query [message #437866 is a reply to message #437759] |
Wed, 06 January 2010 05:28  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Quote:i want to retrieve just those record which dates between two dates.... and date field is in Master table and i m use Group by clause in my previous report.. and when i join these table then its say that which field i add in my report its not group by field...
I don't know how query looks like now (nor how it looked like before), but - did you have to add that column into the query? Could you have done it differently?
I *suppose* that your query might have looked like this:
select d.ename,
d.hiredate,
sum(d.salary) sal
from detail d
group by d.ename, d.hiredate
Now that you have included the "master" table and parameter date values (par_date_from and par_date_to), it looks like this:
select d.ename,
d.hiredate,
sum(d.salary) sal,
m.some_column --> newly added master table column
from detail d,
master m
where d.id = m.id
and m.date_column between :par_date_from and :par_date_to
group by d.ename, d.hiredate
, m.some_column --> did you forget to include it into
-- the GROUP BY clause?
If you don't really need the "m.some_column" in SELECT column list, how about this:
select d.ename,
d.hiredate,
sum(d.salary) sal
from detail d
where d.id in (select m.id
from master m
where m.date_column between :par_date_from and :par_date_to
)
group by d.ename, d.hiredate
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
