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>,

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"...

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

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.

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.

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.

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.

Did you try printing the result of the first report?
/*
TABLE FILE CHECK1
PRINT *
END
-EXIT

Case sensitive?

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.

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 ?

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

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.

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.

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.

Winner winner, chicken dinner

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.

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.