Can't seem to get an outer join working on informix

Question

I have a query that joins across multiple tables with orderline being the main table.

Select  sp.sale_code,
    oh.ware_code,
    cust.cust_code,
    oh.activation_date,
    oh.last_del_date,
    oh.order_date,
    ol.order_num,
    ol.line_num,
    ol.entry_date,
    oh.ship_date,
    trim(wrd.warereptgrp_code) warereptgrp_code,
    pr.maingrp_code,
    pr.prodgrp_code,
    pr.class_code,
    pr.cat_code,
    ol.part_code,
    pr.desc_text,
    oh.quote_amt / 1.1 quote_amt,
    ol.line_tot_amt / 1.1 line_tot_amt,
    ol.order_qty,
    (ol.line_tot_amt / ol.order_qty) / 1.1 product_price,
    dd.picked_qty,
    dh.pick_date,
    ol.uom_code,
    pr.weight_qty,
    pr.cubic_qty,
    pr.area_qty,
    pr.length_qty,
    oh.ord_ind,
    ol.back_qty
from    orderline ol
JOIN    ordhead AS oh   ON oh.order_num = ol.order_num AND oh.cmpy_code = ol.cmpy_code
LEFT OUTER JOIN product AS pr   ON pr.part_code = ol.part_code AND pr.cmpy_code = ol.cmpy_code
LEFT OUTER JOIN category    AS c    ON c.cat_code = pr.cat_code AND c.cmpy_code = ol.cmpy_code
JOIN    warereptdetl    AS wrd  ON wrd.ware_code = oh.ware_code AND wrd.cmpy_code = ol.cmpy_code
JOIN    salesperson AS sp   ON oh.sale_code = sp.sale_code AND oh.cmpy_code = sp.cmpy_code
JOIN    customer    AS cust ON oh.cust_code = cust.cust_code AND oh.cmpy_code = cust.cmpy_code
JOIN    delivhead   AS dh   ON dh.order_num = oh.order_num AND dh.cmpy_code = oh.cmpy_code
JOIN    delivdetl   AS dd   ON oh.order_num = dd.order_num AND oh.cmpy_code = dd.cmpy_code AND ol.line_num = dd.order_line_num AND ol.part_code = dd.part_code AND dh.pick_num = dd.pick_num
where   ol.cmpy_code = 'AB'
and     dh.pick_date BETWEEN '31/12/2019' AND '31/01/2020'
and OH.ord_ind = 7
and oh.quote_flag <> 'Y'
and dh.status_ind = 1
and pr.class_code in ('BRTILE','TAGT','TILE','TLAB','LABQLD','TRES')
and wrd.warereptgrp_code in ('BRNSW','BRVIC','BRQLD','BRSA','BRTAS','BRWA')

The orderline table has multiple rows of data per 1 order_no.

Within the order are products which "usually" have a part_code. There are occasions where a line within an order will NOT have a part_code this typically happens when an order has an extra charge for delivery or a training levy which doesn't have an actual product associated to it.

Currently my query will ONLY return the data where a part_code exists so it omits all the extra charges which have no part_code.

As an example, if I do Select * from orderline where order_num = '6033349' I get 6 lines returned and the 6th line has nothing in the part_code column.

If I lock my query above to only pull results for that order_num it only returns the first 5 rows omitting the 6th line which has no part_code. I need to modify this query to ensure that ALL results from the orderline are returned even if they don't have a part_code.

The linked answer didn't help me as I tried moving the where clause into the join like so LEFT OUTER JOIN product AS pr ON (pr.part_code = ol.part_code and pr.class_code in ('BRTILE','TAGT','TILE','TLAB','LABQLD','TRES')) or ol.part_code is null however it still did not return the values on the orderline table which have a null part_code.

Update: I have taken @Jonathan Leffler advice and cut the query right down to bare minimum and with his suggestion this now brings back the extra row with no part_code form the ol table:

Select  ol.entry_date,
    ol.part_code,
    ol.order_qty,
    pr.weight_qty,
    pr.cubic_qty,
    pr.area_qty,
    pr.length_qty,
    ol.back_qty
from    orderline ol
LEFT JOIN   product AS pr   ON pr.part_code = ol.part_code
LEFT JOIN   category    AS c    ON c.cat_code = pr.cat_code
where   (pr.class_code IS NULL OR pr.class_code IN ('BRTILE', 'TAGT', 'TILE', 'TLAB', 'LABQLD', 'TRES'))
and ol.order_num = '6033349'

Now I need to work out the difference between the first query and the second to return this data in the first one.

RESOLVED:

The issue was that the delivdetl table also needed to be a left outer join LEFT OUTER JOIN delivdetl AS dd ON oh.order_num = dd.order_num AND ol.line_num = dd.order_line_num AND ol.part_code = dd.part_code AND dh.pick_num = dd.pick_num

To determine this I commented out all the joins and selects and brought them back one by one until I could reproduce the error. Bringing back the delivdetl join made the extra line from ol dissapear. Updating the join resolved it and brought the elusive 6th line in.

LEFT JOIN ON returns INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After an OUTER JOIN ON, a WHERE or INNER JOIN that requires a right [sic] table column to be not NULL removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. "`where ... and pr.class_code in (...)`" This is a faq. — philipxy, Feb 12 '20 at 02:54
`What do "have a null value against the order_num" & "make this query return rows from" mean? What rows do you want in all? Use enough words, sentences & references to parts of examples. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. — philipxy, Feb 12 '20 at 02:59
Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) [ask] For SQL that includes DBMS & DDL, which includes constraints & indexes & tabular initialization. Please chop this code way down. PS "know what INNER JOIN you want as part of an OUTER JOIN" — philipxy, Feb 12 '20 at 04:37
PS Pardon me, I was able to make out the rest of your original & edited post. PS Your question involves 1 specific problem re null ol.prod rows. Well the duplicate explains why you didn't get rows with null ol.prod (& pr.prod). That answered your question. The fact that you don't then know overall correct code & give new bad overall code does not mean your question was not answered unless you think your question was "what is a correct query" which it isn't. Since there are no reasonable answers posted, go ahead & edit your post freely; if there were answers, a new post would be apropos. — philipxy, Feb 12 '20 at 05:24
ol rows with null part exit the left join paired with pr.* null. Do you understand that pr.class in () is null when pr.class is null, so the original where filters those rows out? Also the left join in your edit is not the one you want. You don't want your ol-pr left join's associated inner join to be on "... or ol.part is null"--that makes every null-part ol row match every pr row! I expect you want an associated inner join on "..."; the left join then adds null-extended unmatched ol rows (presumably exactly those with null part) to those inner join rows. PS See how I "used enough words"? — philipxy, Feb 12 '20 at 05:38
The condition `AND pr.class_code IN ('BRTILE', 'TAGT', 'TILE', 'TLAB', 'LABQLD', 'TRES')` rules out the cases where `pr.class_code` is NULL. You need something like: `AND (pr_class_code IS NULL OR pr.class_code IN ('BRTILE', 'TAGT', 'TILE', 'TLAB', 'LABQLD', 'TRES'))` so as not to eliminate the rows without a matching product. Similar issues might apply elsewhere in the query, but that's a starting point. — Jonathan Leffler, Feb 12 '20 at 06:59
Please remember to create an MCVE ([Minimal, Complete, Verifiable Example](https://stackoverflow.com/help/mcve)) (or MRE or whatever name SO now uses) or an SSCCE ([Short, Self-Contained, Correct Example](http://sscce.org/)). You could eliminate some of the columns; you could probably eliminate a fair number of the tables too. In general, for SQL questions, it's a good idea to provide minimal schemas for the tables, plus sample data that shows the problem you're experiencing. — Jonathan Leffler, Feb 12 '20 at 07:01
@JonathanLeffler Replacing the where conjunct IN by your IS NULL OR IN gives the same result as adding the IN as a conjunct in the left join ON to give the "..." as I suggested. — philipxy, Feb 12 '20 at 09:29
Hi @JonathanLeffler. Thanks for your help. With your comment: `rules out the cases where pr.class_code is NULL` pr isn't null, a record doesn't exist in that table for that part_code, the null part code only exists in the ol table. ol can have blank part codes but pr can't — Dan Donoghue, Feb 12 '20 at 21:19
This is not resolved, the answers don't give a resolution or if they do they don't explain it enough (probably because everything is posted as a comment). Please don't be so quick to close a question just because someone linked something. Also sarcasm like "See how I used enough words" doesn't add anything towards finding a resolution. — Dan Donoghue, Feb 12 '20 at 21:26
@DanDonoghue: Bad wording on my part — sorry! The gist of what I said is sort of OK, but it isn't accurate. Outer joins are painful to describe accurately. — Jonathan Leffler, Feb 12 '20 at 21:35
One thing to simplify the query would be to lose the customer, delivhead and delivdetl tables from the FROM list and the corresponding entries in the select-list. Get rid of as many columns as possible from the select-list (but keep at least one from each table). For each of the 6 tables left, provide a schema with the used column names and maybe one other to act as a surrogate for the other columns in the table — create table statements are good. For each table, provide a few rows of data (5-10 rows, say). Then say what's expected from that data, and show what you're getting instead. — Jonathan Leffler, Feb 12 '20 at 22:18
Thank you, this has given me enough direction to hopefully figure it out. — Dan Donoghue, Feb 12 '20 at 23:21
I was not sarcastic, I was pointing out an example of doing something I described earlier. (You do not give a clear entire query specification.) You are jumping to (wrong) conclusions about my attitude. After you said you did not see how the question is a duplicate I commented on why it nevertheless was. I didn't post an answer summarizing my comments then--despite JonathanLeffler's suggestion--because the question should still neverthesless have been closed for other reasons that I had clearly explained & I can't vote to close per those when I have already voted to close as a duplicate. — philipxy, Feb 12 '20 at 23:55
@JonathanLeffler The asker here thanked you but forgot the @. PS "Outer joins are painful to describe accurately." I don't disagree but the problem is not difficulty of expressing or understanding once an accurate description has been found but that people just don't persevere at being clear. Also see my first comment here. "LEFT JOIN ON returns INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs" — philipxy, Feb 13 '20 at 00:27
@philipxy Thank you to both of you. Regardless of the path taken the destination is where I needed to be. Without BOTH of your help I would not have managed to resolve this. As a self taught coder using a 25 year old database with no schema, no DBA's in the company and the only indication of any column being used for joins is that they are named similar (ie not always even the same name). it's hard to understand things that may be second nature to people with a higher skill level. I thank you both for your perseverance, we got there in the end. — Dan Donoghue, Feb 13 '20 at 00:33

Can't seem to get an outer join working on informix

0 Answers0