Can you Combine" Where xxx In File" with wildcards?

I am trying to filter on a field that is 260 characters and is filled with concantenated values of a code. There can be one or many codes in this 260 character field.

I have used a statement of:

*WHERE ( SBDIS LIKE '%D30600%' OR '%D30602%' OR '%D30614%' OR '%D30616%')

to find specific intances of codes within this field.

and I have seprately used a statment of:

WHERE B593KEY_13 IN FILE 13BTN

to find a match between the field and a file that has a collection of values I am looking for.

Is there anyway to do a combined "in file" with wildcards? Something like:

WHERE %SBDIS% LIKE IN FILE 13BTN

What I need to find is a set of records where SBDIS contain any of a list of 150 specific codes. I would like to use a file to lookup those values instead of hardcoding them into a statement like the first one.

Any suggestions?

PROD: WebFOCUS 7.1.4 on Win 2003/Microsoft-IIS 6.0/ServletExec 4.2/JAVA version 1.5.0_09
DEV: WebFOCUS 7.6.1 on Win 2003/Microsoft-IIS 6.0/ServletExec 4.2/JAVA version 1.5.0_09
LOCAL: WebFOCUS 7.6.2 on Win XP SP2/Apache Tomcat 5.5.17/JAVA version 1.5.0_09

Posts: 31 | Location: Denver | Registered: April 11, 2005

Francis Mariani

Expert

posted

Hide Post

I would use Dialog Manager to read the values in the file and create the selection criteria. Here's a working example:

-SET &ECHO=ALL;

FILEDEF FMTEST1 DISK EQP/FMTEST1.TXT
-RUN

TABLE FILE CAR
PRINT COUNTRY CAR MODEL BODYTYPE 

WHERE
-READ FMTEST1 NOCLOSE &SELECTION.A03.

-REPEAT ENDREP1 WHILE &IORETURN EQ 0;
-TYPE &SELECTION
BODYTYPE LIKE '%&SELECTION.%' OR
-READ FMTEST1 NOCLOSE &SELECTION.A03.

-ENDREP1
BODYTYPE EQ ''
WHERE BODYTYPE NE ''
END
-RUN

Change the FILEDEF to point to a file in one of your app directories. My selection file has these two values in it:
SED
ROA

The line BODYTYPE EQ '' is there to complete the series of OR statements generated by Dialog Manager.
The line WHERE BODYTYPE NE '' is to negate the previous selection (BODYTYPE EQ '').

Run the code and then view the HTML source to see what's generated.

Cheers,

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

Matthew

Silver Member

posted

Hide Post

Thanks for the idea. I am still working on getting this to work for my situation, but your suggestion did point me in the right direction. If I ever get the code to do what I need I will post it.

Posts: 31 | Location: Denver | Registered: April 11, 2005

Ignored post by Matthew posted

Show Post

Please Wait. Your request is being processed...