CODEWITHSUNDEEP
sqlinformix
JohnL
JohnL

Reputation: 99

How to LEFT JOIN in INFORMIX SQL

I have the following working sql query for an Informix db.

SELECT soi_seq,soi_cusitem,max(soi_product) as ProductCode, 
       max(soi_custprod) as CustProductCode, max(soi_desc) AS ProductDesc, 
       max(soi_comdt2) AS RequiredDate, sum(soi_itqty) as Qty, 
       sum(soi_invval) as Nett, max(soi_discper) as Discount, max(soi_per) 
       as Per, max(soi_price) as ItemPrice 
       from soitem 
       where soi_ordref= ? group by soi_seq,soi_cusitem, soi_lineref 
       order by soi_cusitem asc

I need to join to two other tables but dont know how to do this using informix syntax, basically I need to add to the query above the following joins:

       Left Join cmprod on soi_product = cmp_product
       Left Join cmass on cmp_product = cmass_product

and also, add the following to the WHERE clause: 

      AND cmass_assem Is Null

I can query and get the required results using an old MS Access connection to the Informix db but not in code.

Upvotes: 0

Views: 9067

Answers (2)

Jose Manuel Diaz Perez
Jose Manuel Diaz Perez

Reputation: 11

Former versions of Informix use the outer keyword as follows:

SELECT soi_seq, soi_cusitem, soi_lineref,
       MAX(soi_product) AS ProductCode, 
       MAX(soi_custprod) AS CustProductCode,
       MAX(soi_desc) AS ProductDesc, 
       MAX(soi_comdt2) AS RequiredDate,
       SUM(soi_itqty) AS Qty, 
       SUM(soi_invval) AS Nett,
       MAX(soi_discper) AS Discount,
       MAX(soi_per) AS Per,
       MAX(soi_price) AS ItemPrice 
FROM soitem, -- look, it uses comma to separate tables, even if outer is used
   outer (cmprod CMP, 
   outer cmass CMA)
   -- it requires to order tables with parenthesis
   -- I assume soitem->cmprod->cmass
WHERE 
   -- for good understanding, resolve joins first
   soi_product = CMP.cmp_products 
   AND CMP.cmp_product = CMA.cmass_product 
   -- then match your selection
AND oi_ordref = ?

I'm sorry the answer comes a year late, I'll leave it here for future searchers

Informix outer joins

Upvotes: 1

user2722968
user2722968

Reputation: 16485

This should work

SELECT soi_seq, soi_cusitem, soi_lineref,
       MAX(soi_product) AS ProductCode, 
       MAX(soi_custprod) AS CustProductCode,
       MAX(soi_desc) AS ProductDesc, 
       MAX(soi_comdt2) AS RequiredDate,
       SUM(soi_itqty) AS Qty, 
       SUM(soi_invval) AS Nett,
       MAX(soi_discper) AS Discount,
       MAX(soi_per) AS Per,
       MAX(soi_price) AS ItemPrice 
FROM soitem
LEFT JOIN cmprod ON soi_product = cmp_product
LEFT JOIN cmass ON cmp_product = cmass_product 
WHERE soi_ordref = ?
      AND cmass_assem IS NULL
GROUP BY 1,2,3 
ORDER BY 2

Upvotes: 0

Related Questions