Let's Get Social!
 Go New Search Notify Tools Reply
 Removing digits
Platinum Member
 posted December 13, 2007 02:37 PM
OK...I'm sure this is a very easy thing to do but I have no clue...I have a field that has 2 and 4 numbers in it. I need to trim it to only the 2 first numbers. In otherwords, I need to drop the last 2 numbers even though some only have 2 numbers in it in which case I don't want to do anything to those.

Thank you,
Ana

WEBFOCUS 7.6.4
Server: WINXP

 Posts: 121 | Registered: September 20, 2007 IP
Expert
 posted December 13, 2007 02:50 PM Hide Post
If it's a left-justified alpha field, you can do the following:
`COMPUTE FIELD2/A2 = EDIT(FIELD1,'99\$\$');`

or the following preferable method:
`COMPUTE FIELD2/A2 = SUBSTR(4, FIELD1, 1, 2, 2, 'A2');`

If it's a numeric field, you would first have to convert it to Integer (if it isn't already), and then use the EDIT or SUBSTR function:
`COMPUTE FIELD1I/I4 = FIELD1;`

Then
`COMPUTE FIELD2/A2  = EDIT(FIELD1I,'\$\$99');`

or:
`COMPUTE FIELD2/A2  = SUBSTR(4, FIELD1I, 3, 4, 2, 'A2');`

In the EDIT function, the mask character '9' means copy the corresponding character in the field, '\$' means ignore.

Syntax for substring function:
`SUBSTR(inlength, parent, start, end, sublength, outfield)`

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

 Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005 IP
Expert
 posted December 13, 2007 03:00 PM Hide Post

COMPUTE or DEFINE
NUMFLD/I4=IF NUMFLD LT 100 THEN NUMFLD ELSE IF NUMFLD LT 1000 THEN NUMFLD/10 ELSE NUMFLD/1000;

If the field is not integer to start with, you can use an equality to make it so.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11

 Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006 IP
Platinum Member
 posted December 13, 2007 03:03 PM Hide Post
Thanks Francis..That did it!!! BUT I need it to be a define now that i think of it because once I do this I need to exclude all that equal 50. :-( The compute did strip it down to 2 but now I can't use it in my where clause.

This message has been edited. Last edited by: Latigresa,

WEBFOCUS 7.6.4
Server: WINXP

 Posts: 121 | Registered: September 20, 2007 IP
Guru
 posted December 13, 2007 03:23 PM Hide Post
You can also use these COMPUTE's in a define, but keep in mind that this will only cause effect on record level while COMPUTE works on aggregated data.

Do you only use this for your where statement? If so, post you're WHERE statement here.

Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.

 Posts: 455 | Location: Europe | Registered: February 05, 2007 IP
Platinum Member
 posted December 13, 2007 03:29 PM Hide Post
I did a define using the same data you gave me Francis and it worked great!!!

Thanks A lot!!!

WEBFOCUS 7.6.4
Server: WINXP

 Posts: 121 | Registered: September 20, 2007 IP
Expert
 posted December 13, 2007 03:35 PM Hide Post
Don't forget that this kind of manipulation in a DEFINE on a dbms table will not generate efficient SQL.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

 Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005 IP
Platinum Member
 posted December 13, 2007 03:44 PM Hide Post
I'm not using SQL thank goodness..

WEBFOCUS 7.6.4
Server: WINXP

 Posts: 121 | Registered: September 20, 2007 IP
Expert
 posted December 13, 2007 03:47 PM Hide Post
BTW, does Latigresa mean "The tigress"?

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

 Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005 IP
Platinum Member
 posted December 13, 2007 03:58 PM Hide Post
Yes it does...

Ana

WEBFOCUS 7.6.4
Server: WINXP

 Posts: 121 | Registered: September 20, 2007 IP
Expert
 posted December 13, 2007 04:26 PM Hide Post
Are you dangerous?

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

 Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005 IP
Guru
 posted December 13, 2007 06:58 PM Hide Post
quote:
If it's a left-justified alpha field, you can do the following:

COMPUTE FIELD2/A2 = EDIT(FIELD1,'99\$\$');

or the following preferable method:

COMPUTE FIELD2/A2 = SUBSTR(4, FIELD1, 1, 2, 2, 'A2');

I would us EDIT over SUBSTR in most cases. EDIT is an Internal function built into WebFOCUS. SUBSTR is an External Function stored in an external library that must be accessed. The only time I use SUBSTR is if the string I want is very long.

ttfn, kp

 Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003 IP
Expert
 posted December 14, 2007 02:42 AM Hide Post
Bucket of cold water for Francis!

 Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004 IP
Expert
 posted December 14, 2007 09:34 AM Hide Post
Tony, good morning!

 Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005 IP
Platinum Member
 posted December 14, 2007 02:28 PM Hide Post
My preference is GinnyJakes for the sake of easier to maintain. Although the other recommendations are equally as nice.
Guess Latigresa is roaring now

Ira wf 5.3.6 aix533

aix-533,websphere 5.1.1,apache-2.0,
wf 538(d), 537 (p),
==============
7.6.11 (t) aix 5312
websphere 6.1.19
apache 2.0

 Posts: 195 | Registered: October 27, 2006 IP
Platinum Member
 posted December 17, 2007 11:08 AM Hide Post
Sorry...I just got logged in again and read all the messages...Not dangerous at all..lol... ...and YES I was ROARING and didn't want to stop till I got done with all of it...I'm done now for the most part...

THANKS to all of you that helped me out!!!

WEBFOCUS 7.6.4
Server: WINXP

 Posts: 121 | Registered: September 20, 2007 IP
Expert
 posted December 17, 2007 01:59 PM Hide Post
I was asking if you were dangerous because "Removing digits" sounded like you wanted to bite somebody's fingers off

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

 Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005 IP
Platinum Member
 posted December 17, 2007 04:16 PM Hide Post
Well usually by the time I place a question on here it is because I am extremely frustrated from not being able to get it to work on my own and that's probably why I use some of the words I do cause I am thinking bad thoughts. but tryingto place a question on here without letting everyone know at the same time. Hahaha...

Anyhows...

Merry Christmas and Happy New Year to all!!!

WEBFOCUS 7.6.4
Server: WINXP

 Posts: 121 | Registered: September 20, 2007 IP