CODEWITHSUNDEEP
ibm-midrangedb2-400
user2734217
user2734217

Reputation: 121

Need to add a couple of items to this sql on IBM i

In the first part we selected some records and created an 'outfile' using the Session options for file output. I would like both parts to be one SQL. In Part 2. I have to include column of the last table. ad.IARCC9 the data result currently is this: data is showing the part# sold according to dates we just need that last col included which is the referral to the vendor.

QUANTITY       WWDOCD   PART NO.           
    1.00   20,121,205   MYAABC             
    1.00   20,130,619   MYAABC             
    1.00   20,130,619   MYAABC             
    1.00   20,130,726   MYAABC             
   25.00   20,120,629   AC-GL9             
   20.00   20,120,502   AC-GL9             
   30.00   20,120,425   AC-GL9             
   30.00   20,120,419   AC-GL9             
   20.00   20,120,411   AC-GL9             
   30.00   20,120,321   AC-GL9

Part 1.

SELECT *
FROM astccdta.Oeinh48 a
JOIN astdta.IcDet1 b ON b.Itcom# = a.IhCom# AND b.ItTrn# = a.IhIdc#
WHERE A.IHORDD > 20120101 AND b.ItTscc = 'I'
--------

Part 2.

SELECT ItQty#,IhDocd,ItPRT#
FROM pklib.Fileout1 a
JOIN astdta.icEct1 b ON b.CtCom# = a.ItCom# AND b.CtCtr# = ' ' AND b.CtPrt# =a.ItPrt#
JOIN astdta.Audia ad ON ad.IAprt# = a.ItPrt#
WHERE ad.IARCC9 > ''
ORDER BY ad.IARCC9

Upvotes: 1

Views: 52

Answers (1)

WarrenT
WarrenT

Reputation: 4532

You are on the right track in trying to avoid a temporary intermediate outfile.

The tool you want in your arsenal is called a common table expression [CTE]. This a powerful technique that allows you build a complex SELECT statement as a series of logical building blocks.

Yours can be put together like this:

with f as
(
  SELECT *
    FROM astccdta.Oeinh48  a
    JOIN astdta.IcDet1     b   ON   b.Itcom# = a.IhCom#
                               AND b.ItTrn# = a.IhIdc#
    WHERE A.IHORDD > 20120101
      AND b.ItTscc = 'I'
)
SELECT ItQty#,IhDocd,ItPRT#
  FROM f     -- this refers to the result set of the CTE above
  JOIN astdta.icEct1  b   ON  b.CtCom# = f.ItCom#
                          AND b.CtPrt# = f.ItPrt#
                          AND b.CtCtr# = ' ' 
  JOIN  astdta.Audia  d   ON  d.IAprt# = f.ItPrt#
  WHERE a.IARCC9 > ''
  ORDER BY d.IARCC9;

Additional note: Rather than use the STRSQL session options, you can easily use SQL itself to create an outfile. Just put a little wrapper around your query like this:

CREATE TABLE fileout as
(
   -- your query goes here
)
WITH DATA;

Or if you wanted to add to an existing file

INSERT INTO fileout
   -- your query goes here
;

By using SQL to write your results, rather than session settings, you have created a solution that can be run from other interfaces.

Upvotes: 2

Related Questions