Should I use an SQL full outer join for this?

Question

Consider the following tables:

Table A:

DOC_NUM
DOC_TYPE
RELATED_DOC_NUM
NEXT_STATUS
...

Table B:

DOC_NUM
DOC_TYPE
RELATED_DOC_NUM
NEXT_STATUS
...

The DOC_TYPE and NEXT_STATUS columns have different meanings between the two tables, although a NEXT_STATUS = 999 means "closed" in both. Also, under certain conditions, there will be a record in each table, with a reference to a corresponding entry in the other table (i.e. the RELATED_DOC_NUM columns).

I am trying to create a query that will get data from both tables that meet the following conditions:

A.RELATED_DOC_NUM = B.DOC_NUM
A.DOC_TYPE = "ST"
B.DOC_TYPE = "OT"
A.NEXT_STATUS < 999 OR B.NEXT_STATUS < 999

A.DOC_TYPE = "ST" represents a transfer order to transfer inventory from one plant to another. B.DOC_TYPE = "OT" represents a corresponding receipt of the transferred inventory at the receiving plant.

We want to get records from either table where there is an ST/OT pair where either or both entries are not closed (i.e. NEXT_STATUS < 999).

I am assuming that I need to use a FULL OUTER join to accomplish this. If this is the wrong assumption, please let me know what I should be doing instead.

UPDATE (11/30/2021):

I believe that @Caius Jard is correct in that this does not need to be an outer join. There should always be an ST/OT pair.

With that I have written my query as follows:

SELECT <columns>
FROM A LEFT JOIN B
ON
    A.RELATED_DOC_NUM = B.DOC_NUM
WHERE
    A.DOC_TYPE IN ('ST') AND
    B.DOC_TYPE IN ('OT') AND
    (A.NEXT_STATUS < 999 OR B.NEXT_STATUS < 999)

Does this make sense?

UPDATE 2 (11/30/2021):

The reality is that these are DB2 database tables being used by the JD Edwards ERP application. The only way I know of to see the table definitions is by using the web site http://www.jdetables.com/, entering the table ID and hitting return to run the search. It comes back with a ton of information about the table and its columns.

Table A is really F4211 and table B is really F4311.

Right now, I've simplified the query to keep it simple and keep variables to a minimum. This is what I have currently:

SELECT CAST(F4211.SDDOCO AS VARCHAR(8)) AS SO_NUM,
       F4211.SDRORN AS RELATED_PO,
       F4211.SDDCTO AS SO_DOC_TYPE,
       F4211.SDNXTR AS SO_NEXT_STATUS,
       CAST(F4311.PDDOCO AS VARCHAR(8)) AS PO_NUM,
       F4311.PDRORN AS RELATED_SO,
       F4311.PDDCTO AS PO_DOC_TYPE,
       F4311.PDNXTR AS PO_NEXT_STATUS
FROM   PROD2DTA.F4211 AS F4211
       INNER JOIN PROD2DTA.F4311 AS F4311
         ON F4211.SDRORN = CAST(F4311.PDDOCO AS VARCHAR(8))
WHERE  F4211.SDDCTO IN ( 'ST' )
       AND F4311.PDDCTO IN ( 'OT' )

The other part of the story is that I'm using a reporting package that allows you to define "virtual" views of the data. Virtual views allow the report developer to specify the SQL to use. This is the application where I am using the SQL. When I set up the SQL, there is a validation step that must be performed. It will return a limited set of results if the SQL is validated.

When I enter the query above and validate it, it says that there are no results, which makes no sense. I'm guessing the data casting is causing the issue, but not sure.

UPDATE 3 (11/30/2021):

One more twist to the story. The related doc number is not only defined as a string value, but it contains leading zeros. This is true in both tables. The main doc number (in both tables) is defined as a numeric value and therefore has no leading zeros. I have no idea why those who developed JDE would have done this, but that is what is there.

So, there are matching records between the two tables that meet the criteria, but I think I'm getting no results because when I convert the numeric to a string, it does not match, because one value is, say "12345", while the other is "00012345".

Can I pad the numeric -> string value with zeros before doing the equals check?

UPDATE 4 (12/2/2021):

Was able to finally get the query to work by converting the numeric doc num to a left zero padded string.

SELECT <columns>
FROM   PROD2DTA.F4211 AS F4211
       INNER JOIN PROD2DTA.F4311 AS F4311
         ON F4211.SDRORN = RIGHT(CONCAT('00000000', CAST(F4311.PDDOCO AS VARCHAR(8))), 8)
WHERE  F4211.SDDCTO IN ( 'ST' )
       AND F4311.PDDCTO IN ( 'OT' )
       AND ( F4211.SDNXTR < 999
              OR F4311.PDNXTR < 999 )

Seems like inner join to me; you haven't indicated that A or B could be missing and it be valid, you've only indiated that the valid condition is "A present with status < 999 and B present with status < 999". If it truly is valid that there will be a transfer-out (A record) without any receive-in (B record) then that's A LEFT JOIN B; i'm struggling how a "receive record with no transfer record" would ever be valid; who ships an item and marks it as received without any paperwork to start the shipping in the first place? — Caius Jard, Nov 29 '21 at 17:03
@Caius Jard - I believe you are right. I checked and there should always be an ST/OT pair. — Joseph Gagnon, Nov 30 '21 at 14:32
It's pretty rare that you should need a Full Outer Join for something. The most common use that I know of is for looking for missing relations and/or finding mismatched record values between tables. — RBarryYoung, Nov 30 '21 at 15:03
*it says that there are no results* - that's not a data conversion error then ? If there are no results either one ot both tables lack data, or no table data matches the join., Execute separate queries for F4211 and F4311 to check related data exists — Caius Jard, Nov 30 '21 at 20:27
I was able to figure out what I needed to do and it works. I've updated the posting. — Joseph Gagnon, Dec 02 '21 at 19:46

score 2 · Accepted Answer · answered Nov 30 '21 at 14:55

2

You should write your query as follows:

SELECT <columns>
FROM A INNER JOIN B
ON
    A.RELATED_DOC_NUM = B.DOC_NUM
WHERE
    A.DOC_TYPE IN ('ST') AND
    B.DOC_TYPE IN ('OT') AND
    (A.NEXT_STATUS < 999 OR B.NEXT_STATUS < 999)

LEFT join is a type of OUTER join; LEFT JOIN is typically a contraction of LEFT OUTER JOIN). OUTER means "one side might have nulls in every column because there was no match". Most critically, the code as posted in the question (with a LEFT JOIN, but then has WHERE some_column_from_the_right_table = some_value) runs as an INNER join, because any NULLs inserted by the LEFT OUTER process, are then quashed by the WHERE clause

answered Nov 30 '21 at 14:55

Caius Jard

72,509
5
49
80

I have modified the SQL as suggested. When I run the query, I'm getting a "Data conversion or data mapping error". I think the problem might be related to the DOC_NUM field is defined as a numeric, while the RELATED_DOC_NUM field is defined as a string. What is the best way to convert one or the other so that they match types? – Joseph Gagnon Nov 30 '21 at 16:54
Well.. the number field will definitely convert to a string, but a string doesn't definitely convert to a number. You haven't said what DB is in use but I guess from the error it's DB2; `CAST(B.DOC_NUM as VARCHAR)` - that said if it's a mistake that related_doc_num is a string, and it actually only contains numeric strings/is related to DOC_NUM you should convert it to number – Caius Jard Nov 30 '21 at 17:42
It is DB2 as a backing data store for JDEdwards ERP. I have been trying to cast the string value to a numeric, but still getting the error. – Joseph Gagnon Nov 30 '21 at 17:48
But what if one of the rows has "helloworld" as the doc num - how will you cast that to a numeric? This why I say that a number will always cast to a string, but a stirng won't always cast as a number – Caius Jard Nov 30 '21 at 17:54
Makes sense. So, I switched it around to convert the numeric doc number to the string related doc number. Still getting a data conversion or mapping error. – Joseph Gagnon Nov 30 '21 at 19:25
Post the table definitions – Caius Jard Nov 30 '21 at 19:31
I've updated the posting with more information. – Joseph Gagnon Nov 30 '21 at 20:02

score 0 · Answer 2 · answered Dec 02 '21 at 19:51

0

See Update 4 for details of how I resolved the "data conversion or mapping" error.

answered Dec 02 '21 at 19:51

Joseph Gagnon

1,731
3
30
63

Should I use an SQL full outer join for this?

2 Answers2