Date:         Tue, 9 Mar 1999 14:21:59 -0500
Reply-To:     "Mendicino, Tom" <TMendicino@FLEETCC.COM>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         "Mendicino, Tom" <TMendicino@FLEETCC.COM>
Subject:      Oracle Pass-through SQL
Content-Type: text/plain; charset="iso-8859-1"

This is really more of an Oracle question but I'll try to slide it in
because it is within PROC SQL.  (Besides, the solution may lay in some macro
processing up front.)  In the following pass-through code, the variable
st_date is stored on an oracle table as a number in yymmdd format. (e.g.
990128  981218  981102)
 
I need to query the field to determine if it has a value in a n month time
frame and increment a counter (rsp_n12m) if it does.
 
The following DECODE works, but requires me to update each of the n months
I'm querying when what I really want is the span of months.
 
Any suggestions as to how I can automate the selection?
 
TIA
 
 
 
create table rsp.bt_vars as
   select * from connection to oracle
(
   select
        b.pid
       ,count(a.key)        as btct_ltd
       ,sum(a.amount/100)   as btam_ltd
       ,sum(
           decode(
               trunc(a.st_date/100),9901,1,
                                     9812,1,
                                      9811,1,
                                       9810,1,
                                        9809,1,
                                         9808,1,
                                          9807,1,
                                           9806,1,
                                            9805,1,
                                             9804,1,
                                              9803,1,
                                               9802,1,0)) as rsp_n12m
   from
        tom.leadtable  b
       ,oracle_table   a
   where
       b.key = a.key
   group by
       b.pid
);
 
Tom Mendicino
Fleet Credit Card Services
Horsham, PA
(215) 444-3774