OUTER issue - please help

Hi all, The following query returns values for sd, sd1, and sd2 records ONLY if there are records in the database. If at least one record is not the the database then the query returns empty values even for those records that ARE in the database. Could please tell me what it is wrong here? Thanks in advance for any help. Sil SELECT so.*, sp.*, ep.*, cu.*, sd.*, sd1.*, sd2.*, q.* FROM svc_order so, svc_package sp, evc_package ep, OUTER(pkg_customer_map pc, customer cu, svc_order_de tail sd, svc_order_detail sd1, svc_order_detail sd2, queue q, step s, step_instance si, tm_instance ti) WHERE sp.svc_package_id in (SELECT distinct sp.svc_package_id FROM svc_package sp WHERE sp.name matches '*' ) AND sp.service_tp_id in (select service_tp_id from service_tp WHERE service_spec_id = 401) AND so.svc_package_id = sp.svc_package_id AND so.order_num matches 'TEST0000902' AND q.queue_id = s.queue_id AND s.step_id = si.step_id AND si.tm_instance_id = ti.tm_instance_id AND si.step_inst_id = (SELECT MAX(step_inst_id) FROM step_instance WHERE tm_instance_id = ti.tm_instan ce_id) AND ti.tm_instance_id = so.tm_instance_id AND ep.name = sp.name AND pc.svc_package_id = so.svc_package_id AND cu.cust_id = pc.cust_id AND sd.svc_order_id = so.svc_order_id AND sd.name = 'BANDWIDTH' AND sd1.svc_order_id = so.svc_order_id AND sd1.name = 'RT_BANDWIDTH' AND sd2.svc_order_id = so.svc_order_id AND sd2.name = 'PD_BANDWIDTH';

The outer key word works differently than you expect. a, b, outer(c, d) means that c and d are inner joined but both of these tables are outer joined to the tables a and b. Since I don't know which tables really need outering I can't really help yet with the query. Clarify exactly what you want to outer to what and I can help you structure the outers better. Also, the following in statement seems unnessary: sp.svc_package_id in ( SELECT distinct sp.svc_package_id FROM svc_package sp WHERE sp.name matches '*' ) AND wouldn't you just use: sp.name matches '*' AND thelinuxguy1997@yahoo.com wrote: > Hi all, > > The following query returns values for sd, sd1, and sd2 records ONLY if > there are records in the database. If at least one record is not the > the database then the query returns empty values even for those records > that ARE in the database. > Could please tell me what it is wrong here? > Thanks in advance for any help. > Sil > > SELECT > so.*, sp.*, ep.*, cu.*, sd.*, sd1.*, sd2.*, q.* > FROM svc_order so, svc_package sp, evc_package ep, > OUTER(pkg_customer_map pc, customer cu, svc_order_de > tail sd, svc_order_detail sd1, svc_order_detail sd2, queue q, step s, > step_instance si, tm_instance ti) > WHERE > sp.svc_package_id in (SELECT distinct sp.svc_package_id FROM > svc_package sp WHERE sp.name matches '*' > ) > AND sp.service_tp_id in (select service_tp_id from service_tp WHERE > service_spec_id = 401) > AND so.svc_package_id = sp.svc_package_id > AND so.order_num matches 'TEST0000902' > AND q.queue_id = s.queue_id > AND s.step_id = si.step_id > AND si.tm_instance_id = ti.tm_instance_id > AND si.step_inst_id = (SELECT MAX(step_inst_id) FROM step_instance > WHERE tm_instance_id = ti.tm_instan > ce_id) > AND ti.tm_instance_id = so.tm_instance_id > AND ep.name = sp.name > AND pc.svc_package_id = so.svc_package_id > AND cu.cust_id = pc.cust_id > AND sd.svc_order_id = so.svc_order_id > AND sd.name = 'BANDWIDTH' > AND sd1.svc_order_id = so.svc_order_id > AND sd1.name = 'RT_BANDWIDTH' > AND sd2.svc_order_id = so.svc_order_id > AND sd2.name = 'PD_BANDWIDTH';

Thanks for answering my question. 1. Basically the query worked correctly before adding the new records sd1 and sd2. Therefore, I suspect that adding these sd1 and sd2 somehow generated an issue. And that was my initial question: why i am not getting values from sd, sd1, sd2 when one of them are not present in the database. I other words if I have sd, sd1, and sd2 records present in the database then the query returns the expected values. If sd1 (for example) is not present in the database then the query does NOT return values for sd and sd2 (even if they ARE in the database). 2. I will also modify the statement you pointed out: sp.svc_package_id in.... Thanks for this. Thanks, Sil

That is because sd1 and sd2 are not outer joined in relation to the other tables in the outer(..) frame. So if they are missing you will loose those rows. So I think that you want to outer those tables to the sd table. You should really look closely into what you want to outer and only outer those but here is a stab at it: Try this in your from: svc_order so, svc_package sp, evc_package ep, OUTER( pkg_customer_map pc, customer cu queue q, step s, step_instance si, tm_instance ti ), outer(svc_order_detail sd), outer(svc_order_detail sd1), outer(svc_order_detail sd2) It is over outering so it might give you an error. Think more carefully about which data can actually be missing from the query before you include it in the outer. You probably could get away with just sd, sd1, sd2 being in their seperate outers and the rest of the tables being inner joins. I always find that it helps to draw boxes for each table in the query and then draw lines from each box to represent joins. Highlight lines that are outer (where the data may or may not be available but where you still want to return a row.) and then rewrite the query from there. This helps me find missing joins and joins that I can add to give the optimizer more choices. Plus it highlights the outer join tables.