Ok here are the six queries. I want
1)select ORDER_NUMBER, sub_zone
from ame_nemesis_ordrep
where week = '#url.week#'
and channel_driver >= 1
and exists (select 'x' from nem_cds_summ ncs
where ncs.order_NO=ORDER_NUMBER
and css_database=sub_zone
and week = week_number
and visits is null)
order by sub_zone
2) select ORDER_NUMBER, sub_zone
from ame_nemesis_ordrep
where week = '#url.week#'
and channel_driver >= 1
and exists (select 'x' from nem_cds_summ ncs
where ncs.order_NO=ORDER_NUMBER
and css_database=sub_zone
and week = week_number
and visits is not null)
order by sub_zone
3) select ORDER_NUMBER, sub_zone
from ame_nemesis_ordrep
where week = '#url.week#'
and (SOL_CP='Y'
OR SOL_PD='Y'
OR SOL_NB='Y'
OR DACS='Y'
OR SOL_PEU='Y'
OR SOL_FD='Y'
OR SOL_EX='Y')
and channel_driver >= 1
and exists (select 'x' from nem_cds_summ ncs
where ncs.order_NO=ORDER_NUMBER
and css_database=sub_zone
and week = week_number
and visits is not null)
and customer_comp_date >= (select max(to_date(date_time_com,'dd-mon-rrrr'))
from morse
where morse.order_no = order_number
and css_database = sub_zone
and week = week_number)
order by sub_zone
4) select ORDER_NUMBER, sub_zone
from ame_nemesis_ordrep ords
where week = '#url.week#'
and (SOL_CP='Y'
OR SOL_PD='Y'
OR SOL_NB='Y'
OR DACS='Y'
OR SOL_PEU='Y'
OR SOL_FD='Y'
OR SOL_EX='Y')
and channel_driver >= 1
AND exists (select 'x' from morse
where MORSE.ORDER_NO=ORDS.ORDER_NUMBER
AND MORSE.CSS_DATABASE=ORDS.SUB_ZONE
AND MORSE.WEEK_NUMBER=ORDS.WEEK
AND TRUNC(MORSE.DATE_TIME_COM) >
ORDS.CUSTOMER_COMP_DATE
AND MORSE.ACTIVITY_TYPE LIKE 'X1PR%J%')
and exists (select 'x' from nem_cds_summ ncs
where ncs.order_NO=ords.ORDER_NUMBER
and css_database=ords.sub_zone
and ords.week = week_number
and visits is not null)
order by sub_zone
5) SELECT ALL AMEADMIN.AME_NEMESIS_ORDREP.ORDER_NUMBER,
AMEADMIN.NEM_CDS_SUMM.VISITS,
AMEADMIN.AME_NEMESIS_ORDREP.SOL_FD,
AMEADMIN.EXPEDIENT.ORDER_FAULT_NUM,ameadmin.ame_nemesis_ordrep.sub_zone,ameadmin.expedient.css_exch_id,substr(ameadmin.expedient.css_exch_id,1,2)
FROM AMEADMIN.AME_NEMESIS_ORDREP, AMEADMIN.NEM_CDS_SUMM, AMEADMIN.EXPEDIENT
WHERE week = '#url.week#'
AND (AMEADMIN.NEM_CDS_SUMM.VISITS IS NOT NULL
AND AMEADMIN.AME_NEMESIS_ORDREP.SOL_FD='Y'
AND AMEADMIN.AME_NEMESIS_ORDREP.CHANNEL_DRIVER>='1')
AND ((AMEADMIN.AME_NEMESIS_ORDREP.ORDER_NUMBER=AMEADMIN.NEM_CDS_SUMM.ORDER_NO)
AND (AMEADMIN.AME_NEMESIS_ORDREP.SUB_ZONE=AMEADMIN.NEM_CDS_SUMM.CSS_DATABASE)
AND (AMEADMIN.AME_NEMESIS_ORDREP.WEEK=AMEADMIN.NEM_CDS_SUMM.WEEK_NUMBER)
AND
(AMEADMIN.AME_NEMESIS_ORDREP.ORDER_NUMBER=AMEADMIN.EXPEDIENT.order_fault_num
and
ameadmin.ame_nemesis_ordrep.sub_zone=substr(ameadmin.expedient.css_exch_id,1,2))
)
order by ameadmin.ame_nemesis_ordrep.sub_zone
6) select ORDER_NUMBER, sub_zone
from ame_nemesis_ordrep
where week = '#url.week#'
and channel_driver >= 1
and add_act_ind ='Y'
and exists (select 'x' from nem_cds_summ ncs
where ncs.order_NO=ORDER_NUMBER
and css_database=sub_zone
and week = week_number
and visits is not null)
order by sub_zone
If the order_number exists in query1 and query2 it should only appear in the
output from the highest query i.e query2 and not also in query1. Similarly if
the order_number appears in the output from query1 and also query6 I only want
to show it in the output from the highest query i.e query6. Hope that makes
sense.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185441
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
