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
This returns no records when I run it against my datamart but runs perfectly when I run it against the CAR database with approriate field name changes. What am I missing?

TABLE FILE DIST_DM
BY CATEGORY_DESC
WHERE CATEGORY_DESC CONTAINS 'C';
ON TABLE HOLD AS 'CHECK1'
END

TABLE FILE DIST_DM
PRINT DIST_DM.DIM_DIST_PARENT.DIST_PARENT_NAME
BY CATEGORY_DESC
WHERE CATEGORY_DESC IN FILE 'CHECK1';
END

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 74 | Registered: December 23, 2013Report This Post
Expert
posted Hide Post
TABLE FILE DIST_DM
SUM
CATEGORY_DESC

BY CATEGORY_DESC NOPRINT

WHERE CATEGORY_DESC CONTAINS 'C';

ON TABLE HOLD AS CHECK1

ON TABLE SET HOLDLIST PRINTONLY

END

TABLE FILE DIST_DM
PRINT 
DIST_DM.DIM_DIST_PARENT.DIST_PARENT_NAME

BY CATEGORY_DESC

WHERE CATEGORY_DESC IN FILE CHECK1;

END

This, of course, will return results only if you have CATEGORY_DESC rows that contain "C"...


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
Master
posted Hide Post
There is a limit to the amount of values that can be used like this.

Simple matter of the maximum length of a SQL-statement.

Have you tried your version with the datamart when only a few values are returned from the first query?

G'luck


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Member
posted Hide Post
Yeah There is a limit on file size when you use IN FILE.

For WHERE, the file can be approximately 16,000 bytes. If the file is too large, an error
message displays.


Supriya
WF 7.7.02/8.0
 
Posts: 18 | Registered: November 16, 2011Report This Post
Silver Member
posted Hide Post
Changing your statement to the following will give you a little more breathing room but it too has its limits.
IF CATEGORY_DESC IS (CHECK1)

For IF, the total of all files can be up to 32,767 literals, including new line and other
formatting characters. Lower limits apply to fixed sequential and other non-relational data
sources.


WebFocus 7.7.03 - 7703_hotfix - Gen: 284
Dev Studio 7.7.03 - Gen: 06062011
Mainframe Focus 7.7.03
Windows WF Client Server
zOS Reporting server (hub-sub config)
Windows Reporting server
Windows RC Client Server
 
Posts: 30 | Location: Portland, Oregon, USA | Registered: September 11, 2009Report This Post
Gold member
posted Hide Post
This one is going to drive me to drink. It's not the number of items in the file as I picked "C" to reduce it to 3 hits. Something about the WHERE is not working. It runs fine but returns 0 hits.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 74 | Registered: December 23, 2013Report This Post
Expert
posted Hide Post
If what you posted is your real code, then you don't need to create a HOLD file of values:

TABLE FILE DIST_DM
PRINT 
DIST_DM.DIM_DIST_PARENT.DIST_PARENT_NAME
BY CATEGORY_DESC
WHERE CATEGORY_DESC CONTAINS 'C';
END

IF DIST_DM is a SQL table, turn SQL traces on to see what gets passed to the SQL engine.


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
Guru
posted Hide Post
Did you try printing the result of the first report?
/*
TABLE FILE CHECK1
PRINT *
END
-EXIT


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Expert
posted Hide Post
Case sensitive?


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
Sorry that I have not explained the entire situation very well. I was trying to pull the selected catagories from a different file. I was not able to get that to work so I switched it to the same file so I could figure out what I was doing wrong. It is not a size issue. I've tabled the file and the selected catagories are there as expected. I pulled this code from a report I wrote last year that runs against a cube and it works perfectly. Thanks for everyone's suggestions.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 74 | Registered: December 23, 2013Report This Post
Master
posted Hide Post
j42p11

( nice name ! :-S )

quote:
IF CATEGORY_DESC IS (CHECK1)


That's intresting.
Could you elaborate on this?

Above code gives an error:

(FOC003) THE FIELDNAME IS NOT RECOGNIZED: CHECK1


Any prerequisites ?


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Silver Member
posted Hide Post
quote:

That's intresting.
Could you elaborate on this?

Above code gives an error:

(FOC003) THE FIELDNAME IS NOT RECOGNIZED: CHECK1


Any prerequisites ?


I took a the info straight from the Creating Reports With WebFOCUS Language 7.6.10 manual, page 182 & 183 (in that manual, page 219 & 220 in the 7.7.02 manual). The synax is:

IF fieldname operator (file)

I used check1 because that is what was in the code above, but it can be whatever the hold file is you are referencing.

Here is some sample code using PERSINFO and EMPDATA:
TABLE FILE PERSINFO
SUM
PIN
BY PIN NOPRINT
IF STATE EQ 'NY'
ON TABLE HOLD AS LST1
ON TABLE SET HOLDLIST PRINTONLY
END

TABLE FILE EMPDATA 
PRINT
FIRSTNAME
LASTNAME
DEPT
SALARY
IF PIN IS (LST1)
END




One thing to note is that if you are using a variable length text field (i.e. A30V) the dynamic list lookup does not get passed. But if you format the field on the fly to A30 it will. Just add the /A30 or whatever the length is of your field after the printed field.

TABLE FILE DIST_DM
SUM
CATEGORY_DESC/A30
BY CATEGORY_DESC NOPRINT
WHERE CATEGORY_DESC CONTAINS 'C';
ON TABLE HOLD AS CHECK1
ON TABLE SET HOLDLIST PRINTONLY
END


WebFocus 7.7.03 - 7703_hotfix - Gen: 284
Dev Studio 7.7.03 - Gen: 06062011
Mainframe Focus 7.7.03
Windows WF Client Server
zOS Reporting server (hub-sub config)
Windows Reporting server
Windows RC Client Server
 
Posts: 30 | Location: Portland, Oregon, USA | Registered: September 11, 2009Report This Post
Virtuoso
posted Hide Post
Note that the syntax specifies IF; if you code it with WHERE ...
WHERE fieldname operator (ddname)

... then "(ddname)" will be interpreted as a parenthesized expression, and could well earn a FOC003.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
Just a thought -

1. Did you try SAVE AS CHECK1 rather than HOLD for your temporary file?

2. Is the field variable length format (AnV), rather than fixed (An)? This might cause a problem.
 
Posts: 15 | Location: New York | Registered: April 02, 2004Report This Post
Master
posted Hide Post
Okay, thanks guys.

unfortunately I currently have no access to CAR, PERSINFO etc. right now. ( due to migrations )

I've tested it with our own database.
I've got it. It doesn't seem to work with 'numbers'. We use HOLD FORMAT ALPHA to fix this.

But with TRACEON I can see the exact same SQL generated by both:
• IF MYFIELD IS (LST1)
• WHERE MYFIELD IN FILE LST1

both hitting the buffer limit at the same point.
(FOC1400) SQLCODE IS 302 (HEX: 0000012E)
: CLI2: BADBUFRQ(302): Invalid buffer size.

i.e. as the help states:
_____________________________________________________
Choosing a Filtering Method

There are two phrases for selecting records: WHERE
and IF. It is recommended that you use WHERE to
select records. IF offers a subset of the
functionality of WHERE. Everything that you can
accomplish with IF, you can also accomplish with
WHERE. WHERE can accomplish things that IF cannot.

If you used IF to select records in the past,
remember that WHERE and IF are two different phrases,
and may require different syntax to achieve the same
result.

WHERE syntax is described and illustrated throughout
this topic. For details on IF syntax, see Selecting
Records Using IF Phrases.
_____________________________________________________

Hence my amazement when it was stated that IF could handle up to 32,767 literals.



Only thing is I do remember someone mentioning IF is handled by the ReportingServer ( as in : don't use this, it's less efficient, let de DBMS do the crunching ).
But my TRACE contradicts this.



...and I've heard WF8 has a solution for this.


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Gold member
posted Hide Post
Winner winner, chicken dinner Smiler

It was the variable lenght. I'm not familiar with it. When I defined the category as an a100 and used the define variable rather than the field name it works perfectly. Thank to everyone as this one had me completley stumped.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 74 | Registered: December 23, 2013Report This Post
Virtuoso
posted Hide Post
quote:
But with TRACEON I can see the exact same SQL generated by both:• IF MYFIELD IS (LST1)• WHERE MYFIELD IN FILE LST1


The "32K values" capability applies when the Focus engine is applying the filter to a local data source (e.g., reporting against local Focus files). When the filter criterion is to be passed (via generated SQL) to and applied by a database server, either way it is expressed as a WHERE ... IN clause and the limitations imposed by that server apply.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report 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