使用IBM iSeries Access从AS400里提取BOM数据

柳生静云

Table name:F4101 & F4102
Field name: Multiple areas for extraction: MFG items on the list, please provide the components needed to create the item in review with effectivity dates (EFFT field) greater than 11-17-2008. On the list of all items, need an extraction of the following parts of JDE:

F4101 ITEM MASTER DATA
DSC1 (description 1) for both FG level and componets of each bom please.  
DSC2 (description 2) for both FG level and componets of each bom please.  
STKT (stocking type)
IFLA (it mg) for both FG level and componets of each bom please.  
GLPT (G/L code)

F4102 ITEM BRANCH DATA
STKT (stocking type)
GLPT (gl code)

我们使用的是IBM iSeries Access去提取的这些数据，先倒入了一部分数据进AS400，然后倒出来发给用户：具体的查询语句如下：
P.S.：我以前是使用Hyperion提取的，很傻瓜很方便，现在用这种东西，感觉不是很好用，不过也没办法，就当练习语法了，执行顺序：TF1，TF2，TF2-1，TF3。

TF is short for transfer, TF1 and TF2-1 are dtt (data transfer to) files; whereas TF2 and TF3 are dtf (data transfer to) files.

TF1: Data transfer from local (an excel file) to iSeries.
System: S400E.PSAMER.PS.GE.COM
Library: GALAXYCONV/TF1

TF2:
System: S400E.PSAMER.PS.GE.COM
File Name: GALAXYCONV/TF1, PRODDTA/F3002, PRODDTA/F4102, PRODDTA/F4101
Joined by: T1.LKITL = T2.IXKITL AND T2.IXITM = T3.IBITM AND T1.LMMCU = T2.IXMMCU AND T2.IXCMCU = T3.IBMCU AND T3.IBITM = T4.IMITM
Select:
DISTINCT T2.IXKITL,T2.IXMMCU,T2.IXLITM,T4.IMDSC1,T4.IMDSC2,T4.IMSTKT,T4.IMGLPT,T4.IMIFLA,T3.IBMCU,T3.IBSTKT,T3.IBGLPT,T2.IXEFFT
Where:
(T2.IXEFFT > 108322)
Order by:
T2.IXKITL,T2.IXMMCU,T2.IXLITM,T3.IBMCU,T2.IXEFFT

TF2-1: Data transfer from local (an excel file) to iSeries.
System: S400E.PSAMER.PS.GE.COM
Library: GALAXYCONV/TF2

TF3:
System: S400E.PSAMER.PS.GE.COM
File Name: GALAXYCONV/TF2, PRODDTA/F4102, PRODDTA/F4101
Joined by: T1.LKITL = T2.IBLITM AND T2.IBITM = T3.IMITM AND T1.LMMCU = T2.IBMCU
Select:
DISTINCT T2.IBLITM,T3.IMDSC1,T3.IMDSC2,T3.IMSTKT,T3.IMGLPT,T3.IMIFLA,T2.IBMCU,T2.IBSTKT,T2.IBGLPT,T1.LLITM,T1.LDSC1,T1.LDSC2,T1.LSTKT,T1.LGLPT,T1.LIFLA,T1.LMCU,T1.LSTKT1,T1.LGLPT1,T1.LEFFT
Order by:
T2.IBLITM,T2.IBMCU,T1.LLITM,T1.LMCU,T1.LEFFT

最后我附上一个模板供大家比对，因为保密原因，里面的数据我已经删除了，只留下了表头，不好意思。