Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Exclude Alpha Records in Selection Criteria

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Exclude Alpha Records in Selection Criteria
 Login/Join
 
kadkins
Silver Member
posted
Hello Foc Wizards - I have an easy one for you here.

What is the best/most efficient way to exclude records in selection criteria that end with a certain alpha letter.

For example I have an A22 field that can contain a mixture of numbers/letters but I want to exclude records ending in 'P' or 'V'.

From the list below, the field length may vary (max 22) but I would want to exclude the 1st two records, but keep the third:

ABC12345P
PVC123V
123PV123456A

Thanks!

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


Linux x86
Apache
Excel, CSV, HTML, PDF
 
Posts: 31 | Registered: September 29, 2010Report This Post
Severus.snape
Platinum Member
posted Hide Post
Hi,


This should translate well into any SQL and should be ok with performance..
  
TABLE FILE CAR
PRINT
CAR
WHERE NOT (CAR LIKE '%A' OR CAR LIKE '%N')

END
-EXIT


WF 7.7.03/Windows/HTML,PDF,EXL
POC/local Dev Studio 7.7.03 & 7.6.11
 
Posts: 103 | Registered: June 12, 2009Report This Post
kadkins
Silver Member
posted Hide Post
Thanks. Works like a charm within Oracle.


Linux x86
Apache
Excel, CSV, HTML, PDF
 
Posts: 31 | Registered: September 29, 2010Report This Post
George Patton
Master
posted Hide Post
Aaaah ... One of my old favourites!

Use REVERSE to reverse the order of the string and then EDIT to find that pesky ending character, which will now be the first character. It doesn't matter how long the string is.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
DavSmith
Platinum Member
posted Hide Post
Hi George,
I was remembering a method I used in the past when I saw you mention REVERSE which, I haven't used before. I tested it and found it works but, you must perform an extra step. After the REVERSE, any spaces that were padding the original string to the right are now moved to the left. An LJUST or TRIM will fix that.

The good news is both methods work. Actually, the method Severus.snape offered also works so, KADKINS has 3 choices to play with to determine which is the most efficient in their environment.

Here's a CAR table example showing my old method using ARGLEN and PARAG and your method using REVERSE, LJUST, and EDIT:

TABLE FILE CAR
PRINT CAR
COMPUTE CARA1/I3=ARGLEN(16,CAR,CARA1);AS ARGLEN
COMPUTE CARA2/A1=SUBSTR(16,CAR,CARA1,CARA1,1,CARA2);AS SUBSTR
COMPUTE CARA3/A16=REVERSE(16,CAR,CARA3);AS REVERSE
COMPUTE CARA4/A16=LJUST(16,CARA3,CARA4);AS LJUST
COMPUTE CARA5/A1=EDIT(CARA4,'9');AS EDIT
ON TABLE PCHOLD FORMAT DHTML
END



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Report This Post
kadkins
Silver Member
posted Hide Post
I tried each solution offered with success using each technique. Once again, Focal Point proves to be a treasure trove of info and... there's more than 3 ways to skin a cat.

Thanks again!


Linux x86
Apache
Excel, CSV, HTML, PDF
 
Posts: 31 | Registered: September 29, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Exclude Alpha Records in Selection Criteria

Copyright © 1996-2020 Information Builders