Skip rows from Report based on conditions!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Skip rows from Report based on conditions!

  1. #1
    Join Date
    Jun 2004
    Posts
    125

    Skip rows from Report based on conditions!

    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
    ------

    Code:
    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.

  2. #2
    Join Date
    May 2005
    Location
    France
    Posts
    34
    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

  3. #3
    Join Date
    May 2005
    Posts
    31
    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
    /
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  4. #4
    Join Date
    May 2005
    Location
    France
    Posts
    34
    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

  5. #5
    Join Date
    May 2005
    Posts
    31
    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.
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  6. #6
    Join Date
    May 2005
    Location
    France
    Posts
    34
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width