Lance, I had the same proplem awhile ago. The two tables had a different numbers of columns needed returned so I jioned them in a view and then issued a select against the view with the proper where clause. CREATE OR REPLACE VIEW camsview AS SELECT a.saledate saledate, a.retnbr retnbr, a.settlementamt insettle, a.returnamt inret, a.cashamt incash, a.SALESCOMMAMT incomm, a.CASHBONUSAMT inbonus, to_number(null) olsales, to_number(null) olcash, to_number(null) olcomm, to_number(null) olbonus from glciwsr a union all select b.saledate, b.retnbr retnbr, to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), b.salesamt olsales, b.cashamt olcash, b.salescommamt olcomm, b.cashbonusamt olbonus from glcowsr b;
set pagesize 60 set linesize 130 break on report compute suReceived: from CONNECT-MTA by galottem of insettle inret incash incomm inbonus on report compute sum of olsales olcash olcomm olbonus on report select saledate, (sum(insettle) - sum(inret)) insettle, sum(inret) inret, sum(incash) incash, sum(incomm)*-1 incomm, sum(inbonus)*-1 inbonus, sum(olsales) olsales, sum(olcash)*-1 olcash, sum(olcomm)*-1 olcomm, sum(olbonus)*-1 olbonus from camsview where retnbr = &retnbr and saledate between '&start_dt' and '&end_dt' group by saledate; This allowed the user to enter the desired retnbr and saledate range and get back the data from both tables on a single row. ROR m���m >>> [EMAIL PROTECTED] 01/11/02 12:05PM >>> Here is my situation, I need to join the following two queries: TABLE RELATIONS: PT_SOLUTION-----1 to Many----a.PT_SOLUTION_EVENT PT_SOLUTION-----1 to Many----a.PT_INCIDENTLINK PROBLEM: When I attempt to join all three tables the result set is x amout of time that I need for example: RESULTS: Query 1returns 8 rows Query2 returns 6 rows I attempt to join the result is 48. I believe using intersect is the best way to resolve this but I am having problems and am afraid I may be going down the wrong path. Thank you Lance Query 1 SELECT a.pc_solution_id, b.pc_session_user_name FROM pt_solution a, pt_solution_event b WHERE a.pc_secure_id = b.pc_sol_secure_id and a.pc_solution_id = 'sk4968'; RESULT: sk4968 tchung sk4968 tchung sk4968 dtrevino sk4968 oreiter sk4968 oreiter sk4968 oreiter sk4968 dkotha sk4968 kwinfield Query 2 SELECT a.pc_solution_id, b.pc_incident_id FROM pt_solution a, pt_incidentlink b WHERE a.pc_solution_uuid = b.pc_solution_uuid and a.pc_solution_id = 'sk4968'; RESULT: sk4968 1-283108680 sk4968 83362 sk4968 93765 sk4968 93771 sk4968 93802 sk4968 93905 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lance Prais INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
