I've inherited some <cough>ugly</cough> code that contains a rather unwieldy SQL statement that hits an Informix database. This code computes various parts of the SQL, and does lots of string concatenation, but I had it spit out what it actually sends to Informix and tried using that in my SQL query tool. It is functional when it runs on the server, but when I try running it in FlySpeed SQL (which is an "approved" tool for use at the office), it complains about outer as an unknown keyword. Googling got me nowhere, so I guessed this was an Informix shortcut for LEFT OUTER JOIN, but replacing it with that did nothing to fix the problem, so I'm guessing it's just a bug in FlySpeed.
The SQL is completely unformatted, which I'm guessing is part of what is tripping up FlySpeed.
SELECT A.CRSPD_SETID, A.CRSPD_CUST_ID, F.ST_ID_NUM as STMT_NBR, F.ST_DT, A.BUSINESS_UNIT, H.ASOF_DT, H.DUE_DT, A.ITEM, H.CONTRACT_NUM, D.DESCR as ENTRY_TYPE_DESCR, G.DESCR as ENTRY_REASON_DESCR, SUM(A.BAL_AMT) as ORIG_BAL_AMT, SUM(H.BAL_AMT), C.NAME1, B.ADDRESS1, B.ADDRESS2, B.ADDRESS3, B.ADDRESS4, B.CITY, B.STATE, B.POSTAL, A.BAL_CURRENCY, A.CUST_ID, A.ENTRY_TYPE as ORIG_ENTRY_TYPE, CASE WHEN A.ENTRY_TYPE = 'PY' OR A.ENTRY_TYPE = 'BGB' THEN 'AA' || substr(A.ENTRY_TYPE,1,3) ELSE substr(A.ENTRY_TYPE,1,3) END, A.ENTRY_REASON as ORIG_ENTRY_REASON, C.CUSTOMER_TYPE, K.AR_LVL, H.ORDER_NO FROM PS_STMT_CUST_DTL A, PS_CUST_ADDRESS B, PS_CUSTOMER C, PS_SET_CNTRL_REC E, PS_STMT_CUST F, PS_ENTRY_TYPE_TBL D, PS_ENTRY_REASN_TBL G, outer PS_ITEM H, outer PS_BI_TYPE K WHERE A.ADDRESS_SEQ_NUM = B.ADDRESS_SEQ_NUM AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_CUST_ADDRESS B_ED Where B.SETID = B_ED.SETID AND B.CUST_ID = B_ED.CUST_ID AND B.ADDRESS_SEQ_NUM = B_ED.ADDRESS_SEQ_NUM AND B_ED.EFFDT <= '06/19/2013') AND A.CRSPD_SETID = B.SETID AND A.CRSPD_CUST_ID = B.CUST_ID AND B.SETID = C.SETID AND B.CUST_ID = C.CUST_ID AND B.ADDRESS_SEQ_NUM = C.ADDRESS_SEQ_NUM AND G.ENTRY_TYPE = A.ENTRY_TYPE AND G.ENTRY_REASON = A.ENTRY_REASON AND G.SETID = E.SETID AND G.SETID = D.SETID AND G.ENTRY_TYPE = D.ENTRY_TYPE AND E.SETCNTRLVALUE = A.BUSINESS_UNIT AND E.RECNAME = 'ENTRY_TYPE_TBL' AND A.CRSPD_SETID = F.CRSPD_SETID AND A.CRSPD_CUST_ID = F.CRSPD_CUST_ID AND A.ST_ID_NUM = F.ST_ID_NUM AND A.BUSINESS_UNIT = H.BUSINESS_UNIT AND A.CUST_ID = H.CUST_ID AND A.ITEM = H.ITEM AND A.ITEM_LINE = H.ITEM_LINE AND substr(A.ENTRY_TYPE,1,3) = K.BILL_TYPE_ID AND K.SETID = 'SPN' AND K.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_BI_TYPE C_ED WHERE K.SETID = C_ED.SETID AND K.BILL_TYPE_ID = C_ED.BILL_TYPE_ID AND C_ED.EFFDT <= '06/19/2013') AND K.EFF_STATUS = 'A' AND A.CRSPD_CUST_ID = '000331' AND A.CRSPD_SETID = 'SPN' AND F.ST_DT = '' AND F.ST_ID_NUM = '' GROUP BY A.CRSPD_SETID, A.CRSPD_CUST_ID, F.ST_ID_NUM, F.ST_DT, A.BUSINESS_UNIT ,H.ASOF_DT, H.DUE_DT, A.ITEM, H.CONTRACT_NUM, D.DESCR, G.DESCR, C.NAME1 ,B.ADDRESS1, B.ADDRESS2, B.ADDRESS3, B.ADDRESS4, B.CITY, B.STATE ,B.POSTAL, A.BAL_CURRENCY, A.CUST_ID, A.ENTRY_TYPE ,25, A.ENTRY_REASON, C.CUSTOMER_TYPE, K.AR_LVL, H.ORDER_NO UNION ALL SELECT A.CRSPD_SETID, A.CRSPD_CUST_ID, F.ST_ID_NUM as STMT_NBR, F.ST_DT, A.BUSINESS_UNIT, H.ASOF_DT, H.DUE_DT, A.ITEM, H.CONTRACT_NUM, D.DESCR as ENTRY_TYPE_DESCR, G.DESCR as ENTRY_REASON_DESCR, SUM(A.BAL_AMT) as ORIG_BAL_AMT, SUM(H.BAL_AMT), C.NAME1, B.ADDRESS1, B.ADDRESS2, B.ADDRESS3, B.ADDRESS4, B.CITY, B.STATE, B.POSTAL, A.BAL_CURRENCY, A.CUST_ID, A.ENTRY_TYPE as ORIG_ENTRY_TYPE, CASE WHEN A.ENTRY_TYPE = 'PY' OR A.ENTRY_TYPE = 'BGB' THEN 'AA' || substr(A.ENTRY_TYPE,1,3) ELSE substr(A.ENTRY_TYPE,1,3) END, A.ENTRY_REASON as ORIG_ENTRY_REASON, C.CUSTOMER_TYPE, K.AR_LVL, H.ORDER_NO FROM PS_AR_STMT_CUSDT_H A, PS_CUST_ADDRESS B, PS_CUSTOMER C, PS_SET_CNTRL_REC E, PS_AR_STMT_CUST_H F, PS_ENTRY_TYPE_TBL D, PS_ENTRY_REASN_TBL G, outer PS_AR_ITEM_H H, outer PS_BI_TYPE K WHERE A.ADDRESS_SEQ_NUM = B.ADDRESS_SEQ_NUM AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_CUST_ADDRESS B_ED Where B.SETID = B_ED.SETID AND B.CUST_ID = B_ED.CUST_ID AND B.ADDRESS_SEQ_NUM = B_ED.ADDRESS_SEQ_NUM AND B_ED.EFFDT <= '06/19/2013') AND A.CRSPD_SETID = B.SETID AND A.CRSPD_CUST_ID = B.CUST_ID AND B.SETID = C.SETID AND B.CUST_ID = C.CUST_ID AND B.ADDRESS_SEQ_NUM = C.ADDRESS_SEQ_NUM AND G.ENTRY_TYPE = A.ENTRY_TYPE AND G.ENTRY_REASON = A.ENTRY_REASON AND G.SETID = E.SETID AND G.SETID = D.SETID AND G.ENTRY_TYPE = D.ENTRY_TYPE AND E.SETCNTRLVALUE = A.BUSINESS_UNIT AND E.RECNAME = 'ENTRY_TYPE_TBL' AND A.CRSPD_SETID = F.CRSPD_SETID AND A.CRSPD_CUST_ID = F.CRSPD_CUST_ID AND A.ST_ID_NUM = F.ST_ID_NUM AND A.BUSINESS_UNIT = H.BUSINESS_UNIT AND A.CUST_ID = H.CUST_ID AND A.ITEM = H.ITEM AND A.ITEM_LINE = H.ITEM_LINE AND substr(A.ENTRY_TYPE,1,3) = K.BILL_TYPE_ID AND K.SETID = 'SPN' AND K.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_BI_TYPE C_ED WHERE K.SETID = C_ED.SETID AND K.BILL_TYPE_ID = C_ED.BILL_TYPE_ID AND C_ED.EFFDT <= '06/19/2013') AND K.EFF_STATUS = 'A' AND A.CRSPD_CUST_ID = '000331' AND A.CRSPD_SETID = 'SPN' AND F.ST_DT = '' AND F.ST_ID_NUM = '' GROUP BY A.CRSPD_SETID, A.CRSPD_CUST_ID, F.ST_ID_NUM, F.ST_DT, A.BUSINESS_UNIT ,H.ASOF_DT, H.DUE_DT, A.ITEM, H.CONTRACT_NUM, D.DESCR, G.DESCR, C.NAME1 ,B.ADDRESS1, B.ADDRESS2, B.ADDRESS3, B.ADDRESS4, B.CITY, B.STATE ,B.POSTAL, A.BAL_CURRENCY, A.CUST_ID, A.ENTRY_TYPE ,25, A.ENTRY_REASON, C.CUSTOMER_TYPE, K.AR_LVL, H.ORDER_NO ORDER BY 2,3,4,25,8,11,24
Am I correct that OUTER is just shorthand (maybe Informix-flavored shorthand) for LEFT OUTER JOIN?
UPDATE: I've found here that Informix did in fact use OUTER, but I've yet to find any explanation of just how. Obviously search results overwhelmingly favor things containing the normal syntax. Even this very useful RDBMS comparison contains almost nothing about Informix.
Here's the formatted SQL (sorry about the size!):
SELECT A.CRSPD_SETID,
A.CRSPD_CUST_ID,
F.ST_ID_NUM AS STMT_NBR,
F.ST_DT,
A.BUSINESS_UNIT,
H.ASOF_DT,
H.DUE_DT,
A.ITEM,
H.CONTRACT_NUM,
D.DESCR AS ENTRY_TYPE_DESCR,
G.DESCR AS ENTRY_REASON_DESCR,
SUM(A.BAL_AMT) AS ORIG_BAL_AMT,
SUM(H.BAL_AMT),
C.NAME1,
B.ADDRESS1,
B.ADDRESS2,
B.ADDRESS3,
B.ADDRESS4,
B.CITY,
B.STATE,
B.POSTAL,
A.BAL_CURRENCY,
A.CUST_ID,
A.ENTRY_TYPE AS ORIG_ENTRY_TYPE,
CASE
WHEN A.ENTRY_TYPE = 'PY'
OR A.ENTRY_TYPE = 'BGB' THEN 'AA' || substr(A.ENTRY_TYPE,1,3)
ELSE substr(A.ENTRY_TYPE,1,3)
END,
A.ENTRY_REASON AS ORIG_ENTRY_REASON,
C.CUSTOMER_TYPE,
K.AR_LVL,
H.ORDER_NO
FROM PS_STMT_CUST_DTL A,
PS_CUST_ADDRESS B,
PS_CUSTOMER C,
PS_SET_CNTRL_REC E,
PS_STMT_CUST F,
PS_ENTRY_TYPE_TBL D,
PS_ENTRY_REASN_TBL G,
OUTER PS_ITEM H,
OUTER PS_BI_TYPE K
WHERE A.ADDRESS_SEQ_NUM = B.ADDRESS_SEQ_NUM
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT)
FROM PS_CUST_ADDRESS B_ED
WHERE B.SETID = B_ED.SETID
AND B.CUST_ID = B_ED.CUST_ID
AND B.ADDRESS_SEQ_NUM = B_ED.ADDRESS_SEQ_NUM
AND B_ED.EFFDT <= '06/19/2013')
AND A.CRSPD_SETID = B.SETID
AND A.CRSPD_CUST_ID = B.CUST_ID
AND B.SETID = C.SETID
AND B.CUST_ID = C.CUST_ID
AND B.ADDRESS_SEQ_NUM = C.ADDRESS_SEQ_NUM
AND G.ENTRY_TYPE = A.ENTRY_TYPE
AND G.ENTRY_REASON = A.ENTRY_REASON
AND G.SETID = E.SETID
AND G.SETID = D.SETID
AND G.ENTRY_TYPE = D.ENTRY_TYPE
AND E.SETCNTRLVALUE = A.BUSINESS_UNIT
AND E.RECNAME = 'ENTRY_TYPE_TBL'
AND A.CRSPD_SETID = F.CRSPD_SETID
AND A.CRSPD_CUST_ID = F.CRSPD_CUST_ID
AND A.ST_ID_NUM = F.ST_ID_NUM
AND A.BUSINESS_UNIT = H.BUSINESS_UNIT
AND A.CUST_ID = H.CUST_ID
AND A.ITEM = H.ITEM
AND A.ITEM_LINE = H.ITEM_LINE
AND substr(A.ENTRY_TYPE,1,3) = K.BILL_TYPE_ID
AND K.SETID = 'SPN'
AND K.EFFDT =
(SELECT MAX(C_ED.EFFDT)
FROM PS_BI_TYPE C_ED
WHERE K.SETID = C_ED.SETID
AND K.BILL_TYPE_ID = C_ED.BILL_TYPE_ID
AND C_ED.EFFDT <= '06/19/2013')
AND K.EFF_STATUS = 'A'
AND A.CRSPD_CUST_ID = '000331'
AND A.CRSPD_SETID = 'SPN'
AND F.ST_DT = ''
AND F.ST_ID_NUM = ''
GROUP BY A.CRSPD_SETID,
A.CRSPD_CUST_ID,
F.ST_ID_NUM,
F.ST_DT,
A.BUSINESS_UNIT ,
H.ASOF_DT,
H.DUE_DT,
A.ITEM,
H.CONTRACT_NUM,
D.DESCR,
G.DESCR,
C.NAME1 ,
B.ADDRESS1,
B.ADDRESS2,
B.ADDRESS3,
B.ADDRESS4,
B.CITY,
B.STATE ,
B.POSTAL,
A.BAL_CURRENCY,
A.CUST_ID,
A.ENTRY_TYPE ,
25,
A.ENTRY_REASON,
C.CUSTOMER_TYPE,
K.AR_LVL,
H.ORDER_NO
UNION ALL
SELECT A.CRSPD_SETID,
A.CRSPD_CUST_ID,
F.ST_ID_NUM AS STMT_NBR,
F.ST_DT,
A.BUSINESS_UNIT,
H.ASOF_DT,
H.DUE_DT,
A.ITEM,
H.CONTRACT_NUM,
D.DESCR AS ENTRY_TYPE_DESCR,
G.DESCR AS ENTRY_REASON_DESCR,
SUM(A.BAL_AMT) AS ORIG_BAL_AMT,
SUM(H.BAL_AMT),
C.NAME1,
B.ADDRESS1,
B.ADDRESS2,
B.ADDRESS3,
B.ADDRESS4,
B.CITY,
B.STATE,
B.POSTAL,
A.BAL_CURRENCY,
A.CUST_ID,
A.ENTRY_TYPE AS ORIG_ENTRY_TYPE,
CASE
WHEN A.ENTRY_TYPE = 'PY'
OR A.ENTRY_TYPE = 'BGB' THEN 'AA' || substr(A.ENTRY_TYPE,1,3)
ELSE substr(A.ENTRY_TYPE,1,3)
END,
A.ENTRY_REASON AS ORIG_ENTRY_REASON,
C.CUSTOMER_TYPE,
K.AR_LVL,
H.ORDER_NO
FROM PS_AR_STMT_CUSDT_H A,
PS_CUST_ADDRESS B,
PS_CUSTOMER C,
PS_SET_CNTRL_REC E,
PS_AR_STMT_CUST_H F,
PS_ENTRY_TYPE_TBL D,
PS_ENTRY_REASN_TBL G,
OUTER PS_AR_ITEM_H H,
OUTER PS_BI_TYPE K
WHERE A.ADDRESS_SEQ_NUM = B.ADDRESS_SEQ_NUM
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT)
FROM PS_CUST_ADDRESS B_ED
WHERE B.SETID = B_ED.SETID
AND B.CUST_ID = B_ED.CUST_ID
AND B.ADDRESS_SEQ_NUM = B_ED.ADDRESS_SEQ_NUM
AND B_ED.EFFDT <= '06/19/2013')
AND A.CRSPD_SETID = B.SETID
AND A.CRSPD_CUST_ID = B.CUST_ID
AND B.SETID = C.SETID
AND B.CUST_ID = C.CUST_ID
AND B.ADDRESS_SEQ_NUM = C.ADDRESS_SEQ_NUM
AND G.ENTRY_TYPE = A.ENTRY_TYPE
AND G.ENTRY_REASON = A.ENTRY_REASON
AND G.SETID = E.SETID
AND G.SETID = D.SETID
AND G.ENTRY_TYPE = D.ENTRY_TYPE
AND E.SETCNTRLVALUE = A.BUSINESS_UNIT
AND E.RECNAME = 'ENTRY_TYPE_TBL'
AND A.CRSPD_SETID = F.CRSPD_SETID
AND A.CRSPD_CUST_ID = F.CRSPD_CUST_ID
AND A.ST_ID_NUM = F.ST_ID_NUM
AND A.BUSINESS_UNIT = H.BUSINESS_UNIT
AND A.CUST_ID = H.CUST_ID
AND A.ITEM = H.ITEM
AND A.ITEM_LINE = H.ITEM_LINE
AND substr(A.ENTRY_TYPE,1,3) = K.BILL_TYPE_ID
AND K.SETID = 'SPN'
AND K.EFFDT =
(SELECT MAX(C_ED.EFFDT)
FROM PS_BI_TYPE C_ED
WHERE K.SETID = C_ED.SETID
AND K.BILL_TYPE_ID = C_ED.BILL_TYPE_ID
AND C_ED.EFFDT <= '06/19/2013')
AND K.EFF_STATUS = 'A'
AND A.CRSPD_CUST_ID = '000331'
AND A.CRSPD_SETID = 'SPN'
AND F.ST_DT = ''
AND F.ST_ID_NUM = ''
GROUP BY A.CRSPD_SETID,
A.CRSPD_CUST_ID,
F.ST_ID_NUM,
F.ST_DT,
A.BUSINESS_UNIT ,
H.ASOF_DT,
H.DUE_DT,
A.ITEM,
H.CONTRACT_NUM,
D.DESCR,
G.DESCR,
C.NAME1 ,
B.ADDRESS1,
B.ADDRESS2,
B.ADDRESS3,
B.ADDRESS4,
B.CITY,
B.STATE ,
B.POSTAL,
A.BAL_CURRENCY,
A.CUST_ID,
A.ENTRY_TYPE ,
25,
A.ENTRY_REASON,
C.CUSTOMER_TYPE,
K.AR_LVL,
H.ORDER_NO
ORDER BY 2,
3,
4,
25,
8,
11,
24
The Informix-style OUTER join is not simply a short cut for LEFT OUTER JOIN, but it is a moderate approximation to consider it as such. There are a number of details why it is not that simple.
There's an online explanation for Informix OUTER Joins, some of which are considerably more complex than the query in your example.
Translating the first half of your UNION query into a more modern notation, you'd get:
SELECT A.CRSPD_SETID,
A.CRSPD_CUST_ID,
F.ST_ID_NUM AS STMT_NBR,
F.ST_DT,
A.BUSINESS_UNIT,
H.ASOF_DT,
H.DUE_DT,
A.ITEM,
H.CONTRACT_NUM,
D.DESCR AS ENTRY_TYPE_DESCR,
G.DESCR AS ENTRY_REASON_DESCR,
SUM(A.BAL_AMT) AS ORIG_BAL_AMT,
SUM(H.BAL_AMT),
C.NAME1,
B.ADDRESS1,
B.ADDRESS2,
B.ADDRESS3,
B.ADDRESS4,
B.CITY,
B.STATE,
B.POSTAL,
A.BAL_CURRENCY,
A.CUST_ID,
A.ENTRY_TYPE AS ORIG_ENTRY_TYPE,
CASE
WHEN A.ENTRY_TYPE = 'PY'
OR A.ENTRY_TYPE = 'BGB' THEN 'AA' || substr(A.ENTRY_TYPE,1,3)
ELSE substr(A.ENTRY_TYPE,1,3)
END,
A.ENTRY_REASON AS ORIG_ENTRY_REASON,
C.CUSTOMER_TYPE,
K.AR_LVL,
H.ORDER_NO
FROM PS_STMT_CUST_DTL A
JOIN PS_CUST_ADDRESS B
ON A.ADDRESS_SEQ_NUM = B.ADDRESS_SEQ_NUM AND
A.CRSPD_SETID = B.SETID AND
A.CRSPD_CUST_ID = B.CUST_ID
JOIN PS_CUSTOMER C
ON B.SETID = C.SETID AND
B.CUST_ID = C.CUST_ID AND
B.ADDRESS_SEQ_NUM = C.ADDRESS_SEQ_NUM
JOIN PS_SET_CNTRL_REC E
ON E.SETCNTRLVALUE = A.BUSINESS_UNIT AND
E.RECNAME = 'ENTRY_TYPE_TBL'
JOIN PS_STMT_CUST F
ON A.CRSPD_SETID = F.CRSPD_SETID AND
A.CRSPD_CUST_ID = F.CRSPD_CUST_ID AND
A.ST_ID_NUM = F.ST_ID_NUM
JOIN PS_ENTRY_REASN_TBL G
ON G.ENTRY_TYPE = A.ENTRY_TYPE AND
G.ENTRY_REASON = A.ENTRY_REASON AND
G.SETID = E.SETID AND
JOIN PS_ENTRY_TYPE_TBL D
ON G.SETID = D.SETID AND
G.ENTRY_TYPE = D.ENTRY_TYPE
LEFT OUTER JOIN PS_ITEM H
ON A.BUSINESS_UNIT = H.BUSINESS_UNIT AND
A.CUST_ID = H.CUST_ID AND
A.ITEM = H.ITEM AND
A.ITEM_LINE = H.ITEM_LINE
LEFT OUTER JOIN PS_BI_TYPE K
ON SUBSTR(A.ENTRY_TYPE,1,3) = K.BILL_TYPE_ID
WHERE B.EFFDT =
(SELECT MAX(B_ED.EFFDT)
FROM PS_CUST_ADDRESS B_ED
WHERE B.SETID = B_ED.SETID
AND B.CUST_ID = B_ED.CUST_ID
AND B.ADDRESS_SEQ_NUM = B_ED.ADDRESS_SEQ_NUM
AND B_ED.EFFDT <= '06/19/2013')
AND K.SETID = 'SPN'
AND K.EFFDT =
(SELECT MAX(C_ED.EFFDT)
FROM PS_BI_TYPE C_ED
WHERE K.SETID = C_ED.SETID
AND K.BILL_TYPE_ID = C_ED.BILL_TYPE_ID
AND C_ED.EFFDT <= '06/19/2013')
AND K.EFF_STATUS = 'A'
AND A.CRSPD_CUST_ID = '000331'
AND A.CRSPD_SETID = 'SPN'
AND F.ST_DT = ''
AND F.ST_ID_NUM = ''
GROUP BY A.CRSPD_SETID,
A.CRSPD_CUST_ID,
F.ST_ID_NUM,
F.ST_DT,
A.BUSINESS_UNIT ,
H.ASOF_DT,
H.DUE_DT,
A.ITEM,
H.CONTRACT_NUM,
D.DESCR,
G.DESCR,
C.NAME1 ,
B.ADDRESS1,
B.ADDRESS2,
B.ADDRESS3,
B.ADDRESS4,
B.CITY,
B.STATE,
B.POSTAL,
A.BAL_CURRENCY,
A.CUST_ID,
A.ENTRY_TYPE,
25,
A.ENTRY_REASON,
C.CUSTOMER_TYPE,
K.AR_LVL,
H.ORDER_NO
That's fairly complex because of the sheer number of tables and multi-column join conditions. However, the outer join structure is straight-forward — both outer joins are related directly to the primary table, PS_STMT_CUST_DTL, which is given the alias A.
I'm fairly sure that the SQL could be simplified. For example, when it was written, Informix probably didn't support 'sub-queries in the FROM clause'. But the two SUM values could probably be put into a sub-query, and that would simplify the GROUP BY clause (eliminate it from the main query).