CODEWITHSUNDEEP
sqlibm-midrangedb2-400
Adams
Adams

Reputation: 195

Stored Procedure on IBM i seems

I need guidance in the best way to do this. I ran a report that shows the part#'s that had no sales for the 'past 2 months'. Let's assume for now that this will run at the beginning of each month. I did this as hard coded and this needs to be automated to run in a crystal report. The way I did this was 4 queries. the first picks the items which are 'Available to sell' and inventoried. It seems we can easily make this into a view.

this second one here picks the items which had sales in the 2 months in question. Should this be a stored procedure? if so, I have no previous exp. with SP on the IBM i. But it seems to me the sp is best.

Here we are selecting records when there has been sales in the 4th or 5th months. this is hard coded. We need to do this for the past 2 months at all times. if the program is being run in June then we need to look at May and April. The year should be accordingly. Current year unless we are running this in Jan-Feb. then we want Nov-Dec 2013 etc.

SELECT                                                                  
  ALL       T01.IAPRT#, T01.IA101, T01.IAPRLC, T01.IARCC1, T01.IARCC2,  
            T01.IARCC3, T01.IARCC4, T01.IARCC5, T01.IARCC6, T01.IARCC7, 
            T01.IARCC8, T01.IARCC9, T01.IARC10, T01.IARC11, T01.IARC12, 
            T01.IARC13, T01.IARC14, T01.IARC15, T02.IQYER#, T02.IQA04,  
            T02.IQA05, (T02.IQA04+T02.IQA05) AS TOT45                   
  FROM      ASTDTA.ICPRT1 T01 LEFT OUTER JOIN                           
            ASTDTA.ICSUM1 T02                                           
  ON        T01.IAPRT# = T02.IQPRT#                                     
  WHERE     IAORDF = '1'                                                
    AND     IARCC6 = 'INV'                                              
    AND     T02.IQYER# = 2014                                           
    AND     T02.IQTSCC = 'I'                                            
    AND     (T02.IQA04+T02.IQA05) <> 0                                  
  ORDER BY  T01.IAPRT# ASC

Upvotes: 0

Views: 200

Answers (1)

Buck Calabro
Buck Calabro

Reputation: 7648

Creating an SQL stored procedure on IBM i is very similar to creating a stored procedure on any other platform. CREATE PROCEDURE is the SQL command. You can issue that command via the green screen STRSQL command, the GUI IBM i Navigator or IBM i Access for Web. You can also use an ODBC or JDBC connection if you prefer.

All of the IBM i reference material can be located in the Knowledge Center For v7.1, the direct link to the stored procedures is http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_71/sqlp/rbafysproeg.htm?lang=en The navigation path is IBM i 7.1 > Database > Programming > SQL programming > Routines > Stored procedures.

Upvotes: 3

Related Questions