Need SQL for my requirements

ibmmainframesyntel · Posted: Mon Aug 18, 2008 7:15 pm

Hi,

Table: E.EPRTMST
Field: I_Part

Select all part numbers in this table

Using the above part number,match with the

Table: S.SMXELEM
the first 10 digit in the FCST_MAST_KEY field is the part number

The volume data is separated by month. You will have to sum the volume for 12 months and divide the number by 12 to get average monthly demand. Monthly volumes are in fields LTF_RSLT_FCST_001, LTF_RSLT_FCST_002, LTF_RSLT_FCST_003, LTF_RSLT_FCST_004, LTF_RSLT_FCST_005, LTF_RSLT_FCST_006, LTF_RSLT_FCST_007, LTF_RSLT_FCST_008, LTF_RSLT_FCST_009, LTF_RSLT_FCST_010, LTF_RSLT_FCST_011, LTF_RSLT_FCST_012.

Using the same part number
Table: P.PPOITEM
Field: A_PART_UNIT_PRC
Part Number Field: I_PART
Model Year: I_MOD_YR

There are multiple part number in this table. Grab the price with model year M. If M does not exist, select current model year. If current model year does not exist, select future model year and if that does not exist, select past model year.

The output should be

Part number
Volume
Price

Need a SQl for the above requirements..

enrico-sorichetti · Posted: Mon Aug 18, 2008 7:17 pm

please, please do not double post, it will lower people willingness to help You

the same post in JCL will be deleted

dick scherrer · Posted: Mon Aug 18, 2008 8:49 pm

Hello,

What code do you have so far? Hopefully, you are not waiting for some solution to be published here. . .

Post what you have and what is not working as you want and someone may have a suggestion.

ibmmainframesyntel · Posted: Tue Aug 19, 2008 9:52 am

Till now i don't have any code...waiting for some suggestion..

dbzTHEdinosauer · Posted: Tue Aug 19, 2008 12:38 pm

SUGGESTION: don't hold your breath.

ibmmainframesyntel · Posted: Tue Aug 19, 2008 2:47 pm

For the requirements,
we have a UNLOAD JCL to retreive the data from DB2 table to flat file

1)Table: E.EPRTMST
Field: I_Part
Select all part numbers in this table

I got the part number by UNLOAD JCL using the query

SELECT I_PART FROM E.EPRTMST

2)Volume:
Table: S.SMXELEM

Since everything is part number driven, the first 10 digit in the FCST_MAST_KEY field is the part number.

The volume data is separated by month. You will have to sum the volume for 12 months and divide the number by 12 to get average monthly demand. Monthly volumes are in fields LTF_RSLT_FCST_001, LTF_RSLT_FCST_002, LTF_RSLT_FCST_003, LTF_RSLT_FCST_004, LTF_RSLT_FCST_005, LTF_RSLT_FCST_006, LTF_RSLT_FCST_007, LTF_RSLT_FCST_008, LTF_RSLT_FCST_009, LTF_RSLT_FCST_010, LTF_RSLT_FCST_011, LTF_RSLT_FCST_012.

I got the Volume by UNLOAD JCL using the query

SELECT SUBSTR(FCST_MAST_KEY ,1,10),
(LTF_RSLT_FCST_001 + LTF_RSLT_FCST_002 + LTF_RSLT_FCST_003 + LTF_RSLT_FCST_004 + LTF_RSLT_FCST_005 + LTF_RSLT_FCST_006 + LTF_RSLT_FCST_007 + LTF_RSLT_FCST_008 + LTF_RSLT_FCST_009 + LTF_RSLT_FCST_010 + LTF_RSLT_FCST_011 + LTF_RSLT_FCST_012 ) / 12
from S.SMXELEM

3) I don't know how to write a SQL for the below requiremets
Table: P.PPOITEM
Field: A_PART_UNIT_PRC -- D(11,4)
Part Number Field: I_PART -- C(10)
Model Year: I_MOD_YR -- C(4)

There are multiple part number in this table. Grab the price with model year M. If M does not exist, select current model year. If current model year does not exist, select future model year and if that does not exist, select past model year.

I need a SQL for this requirements to get the data from P.PPOITEM