RE: RPG/SQL

["Erhardt, Bill" <berhardt@xxxxxxxxxxxxxxxx>]

You can build the select statement dynamically then you don't have to worry about host variables.  On the other hand, in past lives I've written SQL views that summed values by Year, Year To Date, Qtr to Date, etc... depending on how much typing you can endure.  The view is dynamic, that is the contents change as the date changes and rather than replicating select logic in many places you just process the view instead of the based on files.  Here's one that always holds current year and prior year sales dollars by Ship To Customer Number (INDSHP).
CREATE VIEW MYLIBRARY/CURPRISHP$  AS                             
SELECT                                                            
 INDSHP,                                                          
 DECIMAL(SUM(CASE WHEN                                            
  SUBSTR(CHAR(YEAR(CURRENT DATE)),3,2) = DIGITS(INDYR)            
  THEN ROUND(INPRC + INTDSC,2) ELSE 0 END),11,2) AS CUR_YR_$$$,   
 DECIMAL(SUM(CASE WHEN                                            
  SUBSTR(CHAR(YEAR(CURRENT DATE - 1 YEAR)),3,2)                   
  = DIGITS(INDYR) AND                                             
  DATE(DIGITS(INIMO)||'/'||DIGITS(INIDA)||'/'||DIGITS(INIYR)) <=  
  CURRENT DATE - 1 YEAR                                           
  THEN ROUND(INPRC + INTDSC,2) ELSE 0 END),11,2) AS LAST_YR_$$$   
FROM FHINVDET INNER JOIN FHINVHDR ON                              
 INDNUM = INHNUM INNER JOIN FKITMSTR ON                           
 INDCO = IMCO AND INDPN = IMPN                                    
WHERE                                                             
INDNUM <> 0 AND                                             
 INSRC = 'SOM' AND INDSOL <> 0  AND INDCT = 1 AND            
 DIGITS(INDYR) >= SUBSTR(CHAR(CURRENT DATE - 1 YEAR),7,2)    
GROUP BY                                                     
 INDSHP                 
                                     
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Vern Hamberg
Sent: Wednesday, June 27, 2012 1:44 PM
To: RPG programming on the IBM i / System i
Subject: Re: RPG/SQL

Bill

It's not clear that he wants it for the current year and previous - it could be any year and its previous, right?

So the you need to use parameter markers/host variables - can those be used in these CASE clauses? Recent posts suggest not.

Now if it IS current year and previous, then where you have "prior year" 
it could have "current year - 1"

This is what I was looking for - maybe simpler than the CTE solution I proffered.

Vern

On 6/27/2012 12:39 PM, Erhardt, Bill wrote:
> I'd use a case statement for example
> Select
>   Sum(case when year = prior year then sales else 0 end) as Prior_Year_sls,
>   Sum(case when year = current Year then sales else 0 end) as 
> Current_year_sls
>
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx 
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Vern Hamberg
> Sent: Wednesday, June 27, 2012 1:28 PM
> To: RPG programming on the IBM i / System i
> Subject: Re: RPG/SQL
>
> Chuck
>
> I think there are a couple typos in my reply - where I refer to tysales and lysales in the final SELECT. Check it out.
>
> Vern
>
> On 6/27/2012 12:00 PM, Graves, Chuck wrote:
> > What type of select statement do I need to produce a data set containing:
> >
> > Customer#
> > 2011 sales
> > 2012 sales
> >
> > Generated from a file containing sales from several years.
> >
> > I am using this statement:
> >
> > exec SQL
> >       declare stor_sls cursor for
> >             select sdan8,
> >                    sum(sdaexp/100)
> >             from f4211l9
> >             where sdivd>=:j_start and sdivd<=:j_end and
> >                   sdlnty not in('T ', 'TN', '% ') and
> >                   sdmcu=:mcu group by sdan8
> >                      order by sum(sdaexp) desc;
> >
> > This statement produces what I want for the given date range..i.e.
> >
> > Customer#
> > 2012 sales
> >
> >
> > but I need a separate field for the prior years sales
> >
> > Do I declare a separate cursor for the prior year and then join the two???
> >
> > Thanks in advance...
> > Chuck
> >
> >
> >
> >
> >
> >
> > [Rodda Paint Turns 80!] Chuck Graves
> >     Director of Information Systems
> >     Rodda Paint Co.<http://www.roddapaint.com>
> >     6107 N. Marine Drive
> >     Portland, Oregon 97203
> >     (503) 737-6042
>
> --
> This is the RPG programming on the IBM i / System i (RPG400-L) mailing 
> list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, 
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a 
> moment to review the archives at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.