Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SQL Code conversion in Focus
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] SQL Code conversion in Focus
 Login/Join
 
Guru
posted
Can this function be written in FOcus?
ALTER FUNCTION [dbo].[GetPhoneClean]
(
@strAlphaNumeric VARCHAR(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT

SET @intAlpha = Patindex('%[^0-9]%', @strAlphaNumeric)

BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = Stuff(@strAlphaNumeric, @intAlpha, 1, '')
SET @intAlpha = Patindex('%[^0-9]%', @strAlphaNumeric)
END
END

RETURN SUBSTRING(Isnull(@strAlphaNumeric, 0),1,10)
END

This function gets first 10 numeric charecters of from phone number filed. How can I accomplish this in webfocus.

My phone number field has some bad data and I want to show it in a standard format only 10 digit phone number and remove ext, person name etc.

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


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Though a similar functionality can be implemented in WebFOCUS, it won't necessarily be "elegant".

Wouldn't it be better to just cleanse the values in your database -either permanently in a new field or exposed as a calculated value in a view- and use that in your report?

As you already have a stored function that does that in your database it should be relatively easy to reuse in a view, shouldn't it?

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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
There does not appear to be any way to strip undesired characters from a string, so all I could come up with is a process to verify each character in the string - keep if numeric, reject if not.

This is an example of a "DEFINE FUNCTION" - a method of creating reusable code that can be called by Dialogue Manager or reporting. It uses the SUBSTR function to isolate one character at a time and then CHKFMT to check the format of the character. If it's numeric, keep, if it's not, reject. (I use the -REPEAT Dialogue Manager command to avoid writing 20 lines of code).

First, create the function:

DEFINE FUNCTION EXTRACTNUMS(INSTRING/A20)
-REPEAT ENDREP FOR &CTR FROM 1 TO 20
S&CTR/A1 = IF CHKFMT(1, SUBSTR(20, INSTRING, &CTR, &CTR, 1, 'A1'), '9', 'I1') EQ 0 THEN SUBSTR(20, INSTRING, &CTR, &CTR, 1, 'A1') ELSE '';
-ENDREP
EXTRACTNUMS/A20 = S1||S2||S3||S4||S5||S6||S7||S8||S9||S10||S11||S12||S13||S14||S15||S16||S17||S18||S19||S20;

Then call the function:

-SET &INPUTPHONE = 'F85T27E515-D3T87';
-SET &GOODPHONE = EXTRACTNUMS(&INPUTPHONE);

-TYPE BEFORE: &INPUTPHONE / AFTER: &GOODPHONE


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, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Now, that's actually elegant Francis!

All there is for ABT to do is to get the first 10 characters from the final result which a simple EDIT(EXTRACTNUMS(PHONE), '9999999999') will do.

Good One



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
I can improve this slightly by making the input string variable in length, but I would have to rely on a Dialogue Manager variable. I could also change this to extract either the alpha characters or the numeric characters...


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, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
Francis and Njsden thank you so much for you help. It will work in my environment. I am going to test it today.

The reason I can not implement cleanup in my DB is because on the front end it is free form entry at the moment and to change the free form entry or impacting DB can create a new project for me.

Arif


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SQL Code conversion in Focus

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.