[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]
Re: selecting a substring of a field

Subject: Re: selecting a substring of a field
From: Victor Loghin <vgl_ca@HOTMAIL.COM>
Date: Wed, 6 Dec 2000 16:55:25 -0500
James,

I looked over your query and was puzzled by the statement that it worked in
COBOL. I doubt that it worked as you intended and perhaps you misinterpreted
the results.
When you specify GROUP BY, you must list only column names and you cannot
uses the renamed        name from AS.
If you used "substr(A.Financing_CD,1,3)  AS Financing_CD" and then grouped
by Financing_CD you will find that the results were grouped by the entire
Financing_CD and not just the substring. You results would look like
A    10
A    13
A    22
B    12
B    34
If that's what you want use "substr(A.Financing_CD,1,3)  &Financing_CD"

If you want your results to look like
A    45
B    46
then the following should work

begin-select
X.Business_Unit
X.Asset_Class
substr_Financing_CD
X.In_Service_FY
X.Book
X.Category
SUM(Cost) &sum_Cost

  let $financing_cd = &substr_Financing_CD
  let #sum_cost = &sum_Cost

FROM
( SELECT A.Business_Unit
   ,      A.Asset_Class
   ,      substr(A.Financing_CD,1,3)  AS substr_Financing_CD
   ,      B.In_Service_FY
   ,      B.Book
   ,      C.Category
   ,      C.Cost
   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  <= $date
   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   = $unit
   AND    A.Asset_Class    in
('212510','212520','212530','212540','212550','212560')
) AS X
GROUP BY
    X.Business_Unit ,
    X.Asset_Class,
    X.substr_Financing_CD ,
    X.In_Service_FY ,
    X.Book,
    X.Category
ORDER BY
    X.Business_Unit ,
    X.Asset_Class,
    X.substr_Financing_CD ,
    X.In_Service_FY ,
    X.Book,
    X.Category

HTH,
Victor Loghin
VGL Consulting Inc.
vgl_ca@hotmail.com



----Original Message Follows----
From: James Wilcher <james.m.wilcher@MAIL.SPRINT.COM>
Subject: Re: selecting a substring of a field
Date: Wed, 6 Dec 2000 11:45:46 -0600

Thanks for trying however it appears DB2 will not accept the Substr as
part of the Group By clause. It returns an invalid syntax error -104 on
the first left paren "(" of the statement. I can not get it to work with
any variation. I tried using the alias in the group by however this
returns -206 error (Column not part of a table listed in a from clause.

So, I guess its back to the old fashioned pgm roll up logic.


Again Thank you to all of you who responded.

James Wilcher



-----Original Message-----
From: vandberg [mailto:vandberg@VAXA.CIS.UWOSH.EDU]
Sent: Wednesday, December 06, 2000 8:50 AM
To: SQR-USERS
Cc: vandberg
Subject: Re: selecting a substring of a field


When you use a function in a select, you should explicitly assign it an
alias.  You do this by following it with a name that begins with an
ampersand.  I usually make this name  resemble the function, but you can
use any name you want.

For example:

begin-select
A.Business_Unit
A.Asset_Class
substr(A.Financing_CD,1,3)  &substr_Financing_CD
B.In_Service_FY
B.Book
C.Category
SUM(C.Cost) &sum_Cost
       let $financing_cd = &substr_Financing_CD
       let #sum_cost = &sum_Cost
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 <= $date
     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  = $unit
     AND A.Asset_Class    in ('212510','212520','212530','212540'
,'212550','212560')
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
end-select

At 07:50 AM 12/06/2000 -0600, you wrote:
 >Thank You for the responses. I still have a problem however. I tried
 >using the "substr(A.Financing_CD,1,3) " in the group by and then
 >referencing it as &substr(A.Financing_CD,1,3) and received the
following
 >error.
 >
 >It appears that it will let me select the substr however I do not have
a
 >way to reference it. Is the value in &Financing_CD? If so why would you
 >need to reference it in the group by as substr(A.Financing_CD,1,3)? If
 >the substr value selected is not in an &********* type field then how
do
 >you reference the value selected?
 >
 >Any Ideas will be welcome.
 >
 >SQR V4.1.7.3
 >
 >Copyright (C) SQRIBE Technologies, 1994-98.  All Worldwide Rights
 >Reserved.
 >
 >
 >(SQR 5528) DB2 SQL PREPARE/DECLARE error -104 in cursor 3:
 >
 >     DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "(". SOME SYMBOLS
 >THAT MIGHT
 >                          BE LEGAL ARE: FOR WITH ORDER UNION EXCEPT
 >              QUERYNO OPTIMIZE
 >
 >     DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE
 >
 >     DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR
 >
 >     DSNT416I SQLERRD    = 0  0  0  -1  596  0 SQL DIAGNOSTIC
INFORMATION
 >
 >     DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'
 >X'FFFFFFFF'
 >                              X'00000254'  X'00000000' SQL DIAGNOSTIC
 >              INFORMATION
 >
 >
 >
 >
 >
 >SQL: select A.Business_Unit, A.Asset_Class, substr(A.Financing_CD,1,3),
 >
 >      B.In_Service_FY, B.Book, C.Category, SUM(C.Cost)  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 <= ? 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  = ?
AND
 >
 >       A.Asset_Class    in ('212510','212520','212530','212540'
 >
 >       ,'212550','212560') 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
 >
 >
 >
 >  Error on line 242:
 >
 >     (SQR 3716) Error in SQL statement.
 >
 >
 >
 >
 >
 >  Errors were found in the program file.
 >
 >
 >
 >  SQR: Program Aborting.
 >
 >
 >
 >
 >
 >
 >
 >-----Original Message-----
 >From: ninachou [mailto:ninachou@UMICH.EDU]
 >Sent: Monday, December 04, 2000 9:21 AM
 >To: SQR-USERS
 >Cc: ninachou
 >Subject: Re: selecting a substring of a field
 >
 >
 >James,
 >
 >Change the group by variable "A.Financing_CD"  to
 >"substr(A.Financing_CD,1,3) "
 >
 >Nina
 >
 >
 >
 >
 >James Wilcher <james.m.wilcher@MAIL.SPRINT.COM> on 12/01/2000 05:08:29
 >PM
 >
 >Please respond to sqr-users@list.iex.net
 >
 >To:   SQR-USERS@list.iex.net
 >cc:    (bcc: Nina Chou/Faculty-Staff/SPA/University of Michigan/US)
 >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


------------------------------------------------------------------------
----
----
Daniel Vandenberg                               | Phone:  (920) 424-3002
Applications Programming                        | Fax:  (920) 424-7317
University of Wisconsin Oshkosh         |
800 Algoma Boulevard                            | Email :
vandberg@uwosh.edu
Oshkosh, WI 54901                               |


_____________________________________________________________________________________
Get more from the Web.  FREE MSN Explorer download : http://explorer.msn.com
Prev by Date: Truncates in a SQR loop
Next by Date: Re: File numbering
Prev by thread: Re: selecting a substring of a field
Next by thread: Calling COBOL Subroutine from SQR
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page