| IBM MAINFRAME HELP & SUPPORT FORUMS
Technical Forums for IBM Mainframe Applications like COBOL, JCL, CICS, DB2, FileAid, DFSORT, Endevor, Xpediter, CoolGen, CA-7&11, AbendAid, IMS, IDMS, PL/I, MqSeries, SyncSort, Assembler, ChangeMan, Easytrieve, InterTest, REXX, CLIST etc...
|
| View previous topic :: View next topic |
| Author |
Message |
ibmmainframesyntel
Joined: 26 Feb 2007
Posts: 125
Location: Chennai
|
| Posted: Mon Aug 18, 2008 7:15 pm Post subject: Need SQL for my requirements |
|
|
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.. |
|
| Back to top |
|
enrico-sorichetti
Joined: 14 Mar 2007
Posts: 3046
Location: italy
|
| Posted: Mon Aug 18, 2008 7:17 pm Post subject: Reply to: Need SQL for my requirements |
|
|
please, please do not double post, it will lower people willingness to help You
the same post in JCL will be deleted |
|
| Back to top |
|
dick scherrer
Joined: 23 Nov 2006
Posts: 8601
Location: 221 B Baker St
|
| Posted: Mon Aug 18, 2008 8:49 pm Post subject: |
|
|
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. |
|
| Back to top |
|
ibmmainframesyntel
Joined: 26 Feb 2007
Posts: 125
Location: Chennai
|
| Posted: Tue Aug 19, 2008 9:52 am Post subject: |
|
|
| Till now i don't have any code...waiting for some suggestion.. |
|
| Back to top |
|
dbzTHEdinosauer
Joined: 20 Oct 2006
Posts: 1618
Location: germany
|
| Posted: Tue Aug 19, 2008 12:38 pm Post subject: |
|
|
| SUGGESTION: don't hold your breath. |
|
| Back to top |
|
ibmmainframesyntel
Joined: 26 Feb 2007
Posts: 125
Location: Chennai
|
| Posted: Tue Aug 19, 2008 2:47 pm Post subject: |
|
|
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 |
|
| Back to top |
|
| |
THIS IS AN ARCIVE FORUM IN READ ONLY MODE. IF YOU WANT TO ASK YOUR DOUBTS USE THE ACTUAL FORUM
|
