Skip rows from Report based on conditions!

[see_one]

Hi,

I am trying to generate a report in Oracle Report6i and I want to show only certain records from a set of output by the query using formula.

Here is my query:

select f.account_num, f.account_name, s.origin_gbloc, avg(ff.q4), avg(ff.q5), count(1)
from f01names2 f, ff_cust_survey ff, shipment s
where ff.gbl_to = s.gbl_to
and f.account_num = s.origin_agent
group by f.account_num, f.account_name, s.origin_gbloc
order by avg(ff.q4) DESC

OUTPUT
------

ACCOUNT_NUM ACCOUNT_NAME   GBLOC  AVG(FF.Q4)  AVG(FF.Q5)  COUNT(1)
----------- ----------     ----  ---------   ---------   -------
160          NAME1         KKFA         12         12          1
178 	     NAME2	   EBAK         12         12          1
200 	     NAME3	   CHAT         12          6          1
1 	     NAME4	   KKFA        6.6        7.2          5
1 	     NAME5	   GTFL        8.8         10         15

From this result, I want to show only those scores where account_num is 1 and GBLOC = 'GTFL'.
And also show those where account_num is not 1.

I tried to do it in my formula column and assigned it to my place holders in the same query group but it would always give me fatal formula error or now it's working but scores are all the same for all records. Can anyone suggest a way to do this sort of thing? I have worked with report pretty much but I just can figure it out how to do it. Thanks a lot.

[RBARAER]

Why don't you simply add these conditions in the where clause of your original sql query ?

select f.account_num, f.account_name, s.origin_gbloc, avg(ff.q4), avg(ff.q5), count(1)
from f01names2 f, ff_cust_survey ff, shipment s
where ff.gbl_to = s.gbl_to
and f.account_num = s.origin_agent
and f.account_num = 1
and s.origin_gbloc = 'GTFL'
group by f.account_num, f.account_name, s.origin_gbloc
order by avg(ff.q4) DESC

HTH & Regards,

RBARAER

[Bonker]

try this and find out if it meets your need.

select f.account_num, f.account_name, s.origin_gbloc, avg(ff.q4), avg(ff.q5), count(1)
from f01names2 f, ff_cust_survey ff, shipment s
where ff.gbl_to = s.gbl_to
and f.account_num = s.origin_agent
and decode(s.origin_agent,1,s.origin_gbloc,'GTFL') = 'GTFL'
group by f.account_num, f.account_name, s.origin_gbloc
order by avg(ff.q4) DESC
/

[RBARAER]

And also show those where account_num is not 1.

Sorry, I didn't see that. So my query would be :

select f.account_num, f.account_name, s.origin_gbloc, avg(ff.q4), avg(ff.q5), count(1)
from f01names2 f, ff_cust_survey ff, shipment s
where ff.gbl_to = s.gbl_to
and f.account_num = s.origin_agent
and
(
(f.account_num = 1
and s.origin_gbloc = 'GTFL')
or
(f.account_num <> 1)
)
group by f.account_num, f.account_name, s.origin_gbloc
order by avg(ff.q4) DESC

Bonker, I'm not sure but I think your DECODE is not correct, what about that :

select f.account_num, f.account_name, s.origin_gbloc, avg(ff.q4), avg(ff.q5), count(1)
from f01names2 f, ff_cust_survey ff, shipment s
where ff.gbl_to = s.gbl_to
and f.account_num = s.origin_agent
and decode(f.account_num,1,s.origin_gbloc,'GTFL') = 'GTFL'
group by f.account_num, f.account_name, s.origin_gbloc
order by avg(ff.q4) DESC

HTH & Regards,

RBARAER

[Bonker]

quote:
_________________________________________________________________
Bonker, I'm not sure but I think your DECODE is not correct, what about that :
_________________________________________________________________

RBARAER,
Did you even try to create sample data and run the query?

see the decode again and decode(s.origin_agent,1,s.origin_gbloc,'GTFL') = 'GTFL'

decode says if account_num or origin_agent (because s.origin_agent is join with f.account_num so I used origin_agent you can use f_account_num as well) is 1 then get orig_gbloc otherwise take 'GTFL' as default and join to 'GTFL' which will give all the data where f_accountnum is <> 1 and only those data for account_num 1 which s.origin_gbloc = 'GTFL'

Suggest you create a sample data and test for yourself.

[RBARAER]

and f.account_num = s.origin_agent

Sorry Bonker I missed that , so you're right, you can use either f.account_num or s.origin_agent inside the decode.

Regards,

RBARAER