CODEWITHSUNDEEP
ibm-midrangedb2-400
user2734217
user2734217

Reputation: 121

need to code for one format of a logical

One of the files here is 'multi format'. How do I code the one I need? the file is OEIND1, this code is created by the IBM I tool from the query. I see you cannot use format in SQL. So the only way is to use the Physical File?

SELECT
   ALL       T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
             T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#
   FROM      ASTDTA/OEORHDOH T01 LEFT OUTER JOIN
             ASTDTA/OEIND1 T02
   ON        T01.OHORD# = T02.IDORD# LEFT OUTER JOIN
             ASTDTA/OEINHDIH T03
   ON        T01.OHORD# = T03.IHORD#
   WHERE     T01.OHOSTC = 'CL'
     AND     T01.OHORDD >= 20120101
   ORDER BY  T01.OHORD# ASC

Upvotes: 0

Views: 60

Answers (2)

WarrenT
WarrenT

Reputation: 4542

Multi-format logical files are created by references to several physical files, where each contributes records in it's own format. You only want one format, so you want to use the physical file that contains that format.

If you actually did want information from multiple formats, then join the relevant physical files, or perhaps UNION or such. In SQL you should be referring to the physicals anyway. If you have specified the physical, the optimizer will evaluate the logicals, and automatically use one if it works out to be a good match for whay you have requested, and the most efficient way to get your results. Trust the SQE optimizer.

Upvotes: 1

Buck Calabro
Buck Calabro

Reputation: 7648

Multi-format logicals are DDS-only. SQL does not understand how to process these. Use the underlying PF.

Upvotes: 1

Related Questions