| Author |
Message |
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
   Posted: 31 Jan 2011 at 10:15am |
I am asking because as it appears now you will have to run the sub report on multiple detail section which means at least one run per row in your report (maybe group header if you are grouping some how) and just crush performance.
If you can join or union these tables you can avoid the subreport altogther.
|
 IP Logged |
|
Nav522
Senior Member
Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
|
Posted: 31 Jan 2011 at 10:53am |
Well am using the command option for all these reports. Because each query has different logic inside but looking at the same 3 tables. So if i use Group by and place the subreports will it work then. Now i understood the concepts of shared variables. Do u think we can acheive this with manipulation in the shared variables? or if we do union of these tables how can we acheive the precedence condition('Y' over 'C')
Thanks
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 31 Jan 2011 at 11:04am |
If you have a forced heirchy and can use outer joins i would use a command to outer join them join them together using a case statment to drop the last letter and join on that field (I'll call it PrimaryID).
Then use a fomrula field to get the value you want.
If NOT(isnull(Yfield)) then Yfield else Cfield
Or you can union them together.
create a formula field that drops the last letter of your field and group on that (called PrimayID)
Then group on that same PrimaryID suppress details and do a MAX value in the group footer to grab the Y over the C.
|
|
IP Logged |
|
Nav522
Senior Member
Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
|
Posted: 31 Jan 2011 at 11:22am |
|
I think i understand what you are saying but am pretty much amateur in query writing. Am not sure of rewriting 3 different queries into a UNION. I will give a shot though. But thanks for the idea.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 31 Jan 2011 at 11:25am |
|
|
|
IP Logged |
|
Nav522
Senior Member
Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
|
Posted: 31 Jan 2011 at 11:41am |
select c.client_code,sh.change_date, c.client_name, '1TRAN' || ecf.client_field_data || 'C'
from event e, status_history sh, event_client_field ecf, client c
where e.event_id = sh.event_id
and e.event_id = ecf.event_id(+)
and e.client_id = c.client_id
and ecf.client_field_name = 'CLIENT LOSS IDENTIFIER'
and e.client_id = 3180
and sh.current_status_code = e.event_status_code
and sh.event_case_id is null
and sh.current_status_code in ('REJECTAPPR', 'CLOSEAPPR')
and (e.reject_code not in ('A/O-BR REJ', 'BRANCH REJ') or
e.reject_code is null)
and trunc(sh.change_date) = trunc(sysdate -1)
-----
select c.client_code,e.created_date, c.client_name,'1TRAN' || ecf.client_field_data || 'Y'
from event_client_field ecf, event e, client c
where e.event_id = ecf.event_id(+)
and e.client_id = c.client_id
and ecf.client_field_name = 'CLIENT LOSS IDENTIFIER'
and e.client_id = 3180
and e.event_status_code = 'OPEN'
and trunc(e.created_date) = trunc(sysdate)-1
-----
select c.client_code,e.created_date, c.client_name,'1TRAN' || ecf.client_field_data || 'Y'
from event_client_field ecf, event e, client c
where e.event_id = ecf.event_id(+)
and e.client_id = c.client_id
and ecf.client_field_name = 'CLIENT LOSS IDENTIFIER'
and e.client_id = 3180
and e.event_status_code = ('WAREHOUSE')
and trunc(e.created_date) = trunc(sysdate -1)
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 31 Jan 2011 at 11:57am |
select c.client_code,sh.change_date, c.client_name, '1TRAN' || ecf.client_field_data || 'C'
from event e, status_history sh, event_client_field ecf, client c
where e.event_id = sh.event_id
and e.event_id = ecf.event_id(+)
and e.client_id = c.client_id
and ecf.client_field_name = 'CLIENT LOSS IDENTIFIER'
and e.client_id = 3180
and sh.current_status_code = e.event_status_code
and sh.event_case_id is null
and sh.current_status_code in ('REJECTAPPR', 'CLOSEAPPR')
and (e.reject_code not in ('A/O-BR REJ', 'BRANCH REJ') or
e.reject_code is null)
and trunc(sh.change_date) = trunc(sysdate -1)
UNION
select c.client_code,e.created_date, c.client_name,'1TRAN' || ecf.client_field_data || 'Y'
from event_client_field ecf, event e, client c
where e.event_id = ecf.event_id(+)
and e.client_id = c.client_id
and ecf.client_field_name = 'CLIENT LOSS IDENTIFIER'
and e.client_id = 3180
and e.event_status_code = 'OPEN'
and trunc(e.created_date) = trunc(sysdate)-1
UNION
select c.client_code,e.created_date, c.client_name,'1TRAN' || ecf.client_field_data || 'Y'
from event_client_field ecf, event e, client c
where e.event_id = ecf.event_id(+)
and e.client_id = c.client_id
and ecf.client_field_name = 'CLIENT LOSS IDENTIFIER'
and e.client_id = 3180
and e.event_status_code = ('WAREHOUSE')
and trunc(e.created_date) = trunc(sysdate -1)
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 31 Jan 2011 at 12:00pm |
or from a glance it looks like you can rework this into one slelect statment and use 2 case statements to decide on "C" or "Y" and change_date or create_date
with a WHERE based on your status_code
|
|
IP Logged |
|
Nav522
Senior Member
Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
|
Posted: 31 Jan 2011 at 3:05pm |
|
well i have used the 3 queries as a union and created a separate report. Droped the last letter of the field and created a formula. Grouped on that formula and suppressed the Detail section. FInally created a Max on the field in the group footer. Looks like it suppressed the Y digit rows rather than the C digit rows. Any ideas?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Feb 2011 at 4:00am |
so you have a maximum(C_or_Y_field,group_formulafield) in the group footer.
Is it giving you the field that you expect or want to show?
If so then you can suppress the details section as
NOT( maximum(C_or_Y_field,group_formulafield) = C_or_Y_field)
|
|
IP Logged |
|
|
|
