Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need SQL for my requirements

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Mon Aug 18, 2008 7:15 pm    Post subject: Need SQL for my requirements
Reply with quote

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
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10211
Location: italy

PostPosted: Mon Aug 18, 2008 7:17 pm    Post subject: Reply to: Need SQL for my requirements
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Aug 18, 2008 8:49 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Tue Aug 19, 2008 9:52 am    Post subject:
Reply with quote

Till now i don't have any code...waiting for some suggestion..
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Aug 19, 2008 12:38 pm    Post subject:
Reply with quote

SUGGESTION: don't hold your breath.
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Tue Aug 19, 2008 2:47 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts OMVSKERN Access Requirements namdrino TSO/ISPF 0 Thu Dec 05, 2013 8:10 pm
This topic is locked: you cannot edit posts or make replies. Requirements in Accenture enikhilk Mainframe Jobs 0 Tue Apr 30, 2013 2:31 pm
This topic is locked: you cannot edit posts or make replies. Requirements in Infy patil.0782 Mainframe Jobs 0 Fri Sep 28, 2012 1:18 pm
No new posts New job to run in production with som... Karthik85 JCL & VSAM 1 Tue Aug 24, 2010 8:59 am
No new posts REXX requirements. expat CLIST & REXX 4 Thu Aug 19, 2010 4:21 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us