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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] WHERE IN FILE problem

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[Solved] WHERE IN FILE problem
 Login/Join
 
Gold member
posted
I'm trying to get something to work in v7.7.02 that used to work in 7.6.1
I had used a subquery in 1st step to use as criteria in 2nd step:
Step on was to get a list of possible values
TABLE FILE BRIEF
PRINT COMM
BY CAMP
WHERE CAMP CONTAINS 'NA0009';
ON TABLE HOLD AS FACF
END
Step 2 was to use this HOLD file as WHERE ...EQ FACF

TABLE FILE CHANNEL
PRINT
COMM
CAMP
WHERE CAMP EQ (FACF)
END

That no longer seems to work in 7.7.02. So I'm trying to follow some other post examples like this:
TABLE FILE BRIEF
BY CAMP
WHERE CAMP CONTAINS 'NA0009';
ON TABLE SAVE AS FACF FORMAT ALPHA
END
TABLE FILE CHANNEL
PRINT
COMM
CAMP
WHERE CAMP IN FILE FACF;
END

When I look at trace, the SQL criteria expands possible values in 1st step. In the 1st step, I know that the only value is 'NA0009MC01'. But the SQL trace shows value of '000010NA0009MC01'
Question is, why are those extra characters 000010 in the SQL WHERE?

thanks

BobV

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


WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
 
Posts: 93 | Registered: February 20, 2008Report This Post
Virtuoso
posted Hide Post
Bob, 2 things:

1) If your FACF hold is to contain a list of values you will probe against CHANNEL.CAMP, the the HOLD file should only contain the values for one field ... but I can see in your TABLE FILE BRIEF statement that you are saving values for 2 fields: CAMP and COMM into the HOLD file. This may impact the results later when you try to WHERE CAMP EQ (FACF) ... only one field should be in the HOLD file.

2) The 000010 seems to be the length of 'NA0009MC01'. Is BRIEF.CAMP an AnV field? That's the way they behave. If you want 'NA0009MC01' only you will have to convert it to a "regular" An field before saving it to the HOLD file.

Try this;

TABLE FILE BRIEF
SUM
COMPUTE A_CAMP/A30 = CAMP;
BY CAMP NOPRINT
ON TABLE HOLD AS FACF FORMAT ALPHA
END


and then test if your WHERE .. (FACF) in the coming statements work now.



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, 2005Report This Post
Expert
posted Hide Post
The syntax for a WHERE in a file is:

WHERE column-name IN FILE file-name

Here's an example:

TABLE FILE CAR
PRINT
COUNTRY
WHERE COUNTRY EQ 'ENGLAND' OR 'FRANCE'
ON TABLE HOLD AS SEL1 FORMAT ALPHA
END

TABLE FILE CAR
PRINT *
WHERE COUNTRY IN FILE SEL1
END

The syntax you describe is for IF:

IF column-name EQ (file-name)


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, 2005Report This Post
Gold member
posted Hide Post
Thanks njsden. Changing format to A30 worked. Yes, CAMP field is an A30v


quote:
Originally posted by njsden:
Bob, 2 things:

1) If your FACF hold is to contain a list of values you will probe against CHANNEL.CAMP, the the HOLD file should only contain the values for one field ... but I can see in your TABLE FILE BRIEF statement that you are saving values for 2 fields: CAMP and COMM into the HOLD file. This may impact the results later when you try to WHERE CAMP EQ (FACF) ... only one field should be in the HOLD file.

2) The 000010 seems to be the length of 'NA0009MC01'. Is BRIEF.CAMP an AnV field? That's the way they behave. If you want 'NA0009MC01' only you will have to convert it to a "regular" An field before saving it to the HOLD file.

Try this;

TABLE FILE BRIEF
SUM
COMPUTE A_CAMP/A30 = CAMP;
BY CAMP NOPRINT
ON TABLE HOLD AS FACF FORMAT ALPHA
END


and then test if your WHERE .. (FACF) in the coming statements work now.


WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
 
Posts: 93 | Registered: February 20, 2008Report 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] WHERE IN FILE problem

Copyright © 1996-2020 Information Builders