[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

Re: [sqr-users] problems with using length(field)

Just my usual wet-blanket post: tell your DBAs that this is a gross
violation of 1st Normal Form, i.e. thou shalt not encode information
into a portion of a field. Unfortunately it's something you tend to pay
for down the road.


>>> jej1216@hotmail.com 2/28/2005 6:39:08 PM >>>
Thanks to all for your help - it lead me to the correct approach.

I changed the code to the following so that I was working with the
numeric 
length, and the code now is correct:

==================================================

begin-SELECT
ET.DESCR
ET.GL_EXPENSE

   let $Erncd_Descr      = rtrim(&ET.DESCR,' ')
   let $GL_Expense       = rtrim(&ET.GL_EXPENSE,' ')
   let #GL_Expense_Lngth = length(rtrim(&ET.GL_Expense, ' '))

     evaluate #GL_Expense_Lngth
       when = 12
       when > 12
         show 'length 12+'
         if substr($GL_Expense,1,3) = '900'
            let $Acct_Cd = substr($GL_Expense,7,6)
            let $DeptID  = '0000'
         end-if
         if substr($GL_Expense,1,2) = '10'
            let $DeptID  = substr($GL_Expense,3,4)
            let $Acct_Cd = substr($GL_Expense,7,6)
         end-if
         if substr($GL_Expense,1,2) = '08'
            let $DeptID  = substr($GL_Expense,3,4)
            let $Acct_Cd = substr($GL_Expense,7,6)
         end-if
         if substr($GL_Expense,1,3) = '000     '
            let $Acct_Cd = substr($GL_Expense,7,6)
            let $DeptID  = '0000'
         end-if
       break
       when = 6
            show 'length 6'
            let $Acct_Cd = substr($GL_Expense,1,6)
       break
     end-evaluate

from
   PS_EARNINGS_TBL ET

where
   ET.ERNCD = $Erncd
  and ET.EFF_STATUS = 'A'
  and ET.EFFDT =
    (select max(ET1.EFFDT)
     from PS_EARNINGS_TBL ET1
     where ET.ERNCD       = ET1.ERNCD
       and ET1.EFF_STATUS = 'A'
       and ET1.EFFDT     <= $AsOfToday)

group by ET.DESCR, ET.GL_EXPENSE

end-SELECT

==========================================

- Joe

----Original Message Follows----
From: Don Mellen <donm@ontko.com>
Reply-To: "This list is for discussion about the SQR database 
reportinglanguage from Hyperion Solutions." <sqr-users@sqrug.org>
To: "This list is for discussion about the SQR database reporting 
languagefrom Hyperion Solutions." <sqr-users@sqrug.org>
Subject: Re: [sqr-users] problems with using length(field)
Date: Mon, 28 Feb 2005 17:25:11 -0500 (EST)
MIME-Version: 1.0
Received: from seldon.sqrug.org ([199.164.165.113]) by
mc1-f37.hotmail.com 
with Microsoft SMTPSVC(6.0.3790.211); Mon, 28 Feb 2005 14:28:23 -0800
Received: from localhost([127.0.0.1] helo=seldon.sqrug.org
ident=list)by 
seldon.sqrug.org with esmtp (Exim 3.35 #1 (Debian))id
1D5tNB-0004el-00for 
<jej1216@hotmail.com>; Mon, 28 Feb 2005 17:28:17 -0500
Received: from shire.ontko.com ([199.164.165.1] ident=root)by 
seldon.sqrug.org with esmtp (Cipher TLSv1:DES-CBC3-SHA:168) (Exim 3.35
#1 
(Debian))id 1D5tKC-0004VS-00for <sqr-users@sqrug.org>; Mon, 28 Feb 2005

17:25:13 -0500
Received: from shire.ontko.com (donm@localhost [127.0.0.1])by 
shire.ontko.com (8.12.3/8.12.3/Debian-7.1) with ESMTP
idj1SMPCl3018353for 
<sqr-users@sqrug.org>; Mon, 28 Feb 2005 17:25:12 -0500
Received: from localhost (donm@localhost)by shire.ontko.com 
(8.12.3/8.12.3/Debian-7.1) with SMTP idj1SMPC3Z018349for 
<sqr-users@sqrug.org>; Mon, 28 Feb 2005 17:25:12 -0500
X-Message-Info: JGTYoYF78jEHjJx36Oi8+Z3TmmkSEdPtfpLB7P/ybN8=
X-BeenThere: sqr-users@sqrug.org 
X-Mailman-Version: 2.1.4
Precedence: list
List-Id: "This list is for discussion about the SQR database 
reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
List-Unsubscribe: 
<http://www.sqrug.org/mailman/listinfo/sqr-users>,<mailto:sqr-users-request@sqrug.org?subject=unsubscribe>
List-Post: <mailto:sqr-users@sqrug.org>
List-Help: <mailto:sqr-users-request@sqrug.org?subject=help>
List-Subscribe: 
<http://www.sqrug.org/mailman/listinfo/sqr-users>,<mailto:sqr-users-request@sqrug.org?subject=subscribe>
Errors-To: sqr-users-bounces+jej1216=hotmail.com@sqrug.org 
Return-Path: sqr-users-bounces+jej1216=hotmail.com@sqrug.org 
X-OriginalArrivalTime: 28 Feb 2005 22:28:23.0956 (UTC) 
FILETIME=[D09BFD40:01C51DE4]

The logic works perfectly, but you have to realize that you're doing
string comparison, not number comparison.

Try something like...

SELECT
ET.DESCR
ET.GL_EXPENSE
LENGTH(ET.GL_EXPENSE) &ET.GL_EXPENSE_LNGTH=number
FROM TABLE ET

... and ...

   evaluate &ET.GL_EXPENSE_LNGTH
     when >= 12
       show 'Length 12+'
       break
     when = 6
       show 'Length 6'
       break

    ... etc

HTH,
Don


On Mon, 28 Feb 2005, Joe Johnson wrote:

 > I have a requirment to process data according to the length of one
field.
 >
 > I am getting the length this way:
 >
 > SELECT
 > ET.DESCR
 > ET.GL_EXPENSE
 > LENGTH(ET.GL_EXPENSE) &ET.GL_EXPENSE_LNGTH
 > FROM TABLE ET
 >
 > The result of &ET.GL_EXPENSE_LNGTH is then pared down to:
 >
 >    let $GL_Expense_Lngth = &ET.GL_EXPENSE_LNGTH
 >    let $GL_Expense_Lngth = rtrim($GL_Expense_Lngth, '0')
 >    let $GL_Expense_Lngth = rtrim($GL_Expense_Lngth, '.')
 >
 > which changes "6.0000000000000000000000" to "6"
 >
 > I have show statements to verify that $GL_Expense_Lngth is
identifying 
the
 > correct length.
 >
 > However, when I try to apply some of the programming logic according
to
 > length, the logic fails.  The code:
 >      evaluate $GL_Expense_Lngth
 >        when = '12'
 >        when > '12'
 >          show 'Length 12+'
 >        break
 >        when = '6'
 >          show 'Length 6'
 >        break
 >        when = '0'
 >          show 'Length 0'
 >        break
 >      end-evaluate
 >
 > There are two problems:
 >
 > 1) when the field is blank, it's being shows as $GL_Expense_Lngth=1
 >
 > 2) when the field is 6, it's applying the logic for
$GL_Expense_Lngth => 
12
 >
 > Any help would be appreciated.
 >
 > Thanks,
 >
 > Joe Johnson
 >
 > ~ JEJ ;{) ~
 >
 >
 >
 > _______________________________________________
 > sqr-users mailing list
 > sqr-users@sqrug.org 
 > http://www.sqrug.org/mailman/listinfo/sqr-users 
 >

-----------------------------------------------------------------------
Donald Mellen  | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/

donm@ontko.com |  "In the beginning, there was nothing, which
exploded"


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users 



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users