Re: selecting a substring of a field

[Jon Alsager <JAlsager@ISIF.STATE.ID.US>]

I'm not sure, but I think your group by and order by would need to look like
this:

GROUP BY A.Business_Unit
         ,A.Asset_Class
        ,substr(A.Financing_CD,1,3)
        ,B.In_Service_FY
        ,B.Book
        ,C.Category
ORDER BY A.Business_Unit
        ,A.Asset_Class
        ,B.In_Service_FY
        ,substr(A.Financing_CD,1,3)
        ,B.Book
        ,C.Category

-----Original Message-----
From: James Wilcher [mailto:james.m.wilcher@MAIL.SPRINT.COM]
Sent: Friday, December 01, 2000 3:08 PM
To: SQR-USERS@list.iex.net
Subject: selecting a substring of a field


Hi All, I am trying to select a substring of a column along with a sum
of a column from a table and then use the substring value selected in
the groupby and orderby clauses. Currently DB2 v6 allows this
functionality. (i.e. I can do it in Cobol no problem) However I have not
been able to make it work in a begin-select in sqr. Have any of you ever
attempted any thing like this and if so will it work?  Note this
variation does not run. If I remove the & it does not see the rest of
the column names as it will not insert the comma, If I leave it as is it
puts the comma behind as ignores the name and yields an error.

Any help would be appreciated.

here is an example

begin-SELECT
A.Business_Unit
A.Asset_Class
substr(A.Financing_CD,1,3) as &Financing_CD        Originally this line
was just  "A.Financing_CD"
B.In_Service_FY
B.Book
C.Category
SUM(C.Cost) &Total_Cost

   move &Financing_CD        to $A.Financing_CD
   here take each record and go do some process etc...

FROM PS_Asset         A
    ,PS_Book          B
    ,PS_Cost          C
WHERE A.Business_Unit  = B.Business_Unit
  AND A.Business_Unit  = C.Business_Unit
  AND C.ACCOUNTING_DT <= $PERIOD_END_DATE !5/8/2000 DON HORTON
  AND A.Asset_ID       = B.Asset_ID
  AND A.Asset_ID       = C.Asset_ID
  AND A.Acquisition_CD <> 'L'
  AND B.Book           = 'GAAP'
  AND C.Book           = B.Book
  AND A.Business_Unit  = $BU.Business_Unit
  AND A.Asset_Class    in ('212510','212520','212530','212540'
                          ,'212550','212560')
GROUP BY A.Business_Unit
         ,A.Asset_Class
        ,A.Financing_CD
        ,B.In_Service_FY
        ,B.Book
        ,C.Category
ORDER BY A.Business_Unit
        ,A.Asset_Class
        ,B.In_Service_FY
        ,A.Financing_CD
        ,B.Book
        ,C.Category
end-SELECT

James M Wilcher