I created hold files to hold values for future filtering. The hold files are created using the hierarchy so I could pull all values for a specific parent. I checked the hold files and the data is there and the formats match. In the same fex, when I query the transaction table and use the above syntax (subject line), I get zero records. At this point, I am only using one filter. There is data that would match. In file, should not have to use tics to enclose the value. Any suggestions or ideas?

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

Is the field you are matching the first one in the hold file? When you multiply the field length by the number of rows in the hold file, is it less than 16000 bytes for a test against a relational table?

Your syntax is correct. I use this technique a lot and have been very successful.

It is the only field in my hold and it currently has only one value. I tabled it to check it. When I table my transaction file with this filter, I get zero records. I am in 7.6.8 This is very frustrating.

Are you using "HOLD AS filename FORMAT ALPHA"?

Yes.

TABLE FILE TM1JOBS
PRINT JOBPARENT AS 'PARENT' NOPRINT
FOR JOBNAME
&JOB
WITH CHILDREN ALL
ON TABLE HOLD AS HOLDJOBS FORMAT ALPHA
END
...
I added the noprint after the parent since I only wanted one field in the hold file. I remove the noprint for debugging purposes.
...
TABLE FILE FCST
PRINT AMOUNT
BY BUSINESS_UNIT
BY SCENARIO
BY CURRENCY
BY SOURCE
BY JOB
BY DEPARTMENT
BY ACCOUNT
BY YEAR
-*WHERE JOB EQ (HOLDJOBS)
WHERE JOB IN FILE HOLDJOBS;

Can you do a ? HOLD HOLDJOBS followed by a -EXIT and post the results?

After than, can you ON TABLE SET HOLDLIST PRINTONLY and do the first part again?

-exit
0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1
0DEFINITION OF HOLD FILE: HOLDJOBS
0FIELDNAME ALIAS FORMAT
E01 A 50

I already have the set holdlist = printonly at the top of the fex.

Kathy, Keep in mind that the file produced will have leading blanks for children of the primary JOBNAME, this may be the cause.

If this works the same as in legacy Focus's
IF fieldname EQ (ddname)
then blanks are delimiters, so any value with imbedded blanks will essentially be truncated...

e.g.,

   "Widgets Department   "

would be treated as

   "Widgets              "

...which would lead to your symptoms.


Suggestion: use HOLD as the hold-file name; then the MFD controls parsing into fields.

Are you sure there is an equivalent job in FCST? Is JOB the same format? Have you tried a JOIN?

Hi, thank you everyone for your responses.
1. I issued a SET BLANKINDENT=OFF. that did not help.
2. I did notice in the ? hold holdjobs that the V is dropped when doing format alpha.
3. I issued a join both ways, hold file to transaction file and then the transaction file to the hold. On the second one, trans to hold file join, I did get a foc236 linked file does not have a matching key field or segment: job bypassing to end of command. Thus I feel the V is the problem. Because I have to join to a rather large transaction file that has V's, I'm committed to the V's.
4. The transaction file is an Oracle table.

Basically, I have six fields that are prompted for and I need to pull all the children and all the grandchildren for each of those six fields which is why I use the hierarchy and then hold the results to be used for future filtering when doing the last report using a hierarchy again. For debugging purposes I have used a value where only one row is returned. I use the last hierarchy as part of the actual report. Does anyone have any other ideas? Thanks again.

Oops, one more thing, I have also tried all the below lines separately....

IF JOB EQ (HOLDJOBS)
WHERE JOB EQ (HOLDJOBS);
IF JOB IN FILE HOLDJOBS
WHERE JOB IN FILE HOLDJOBS;

Are you using a master file description to access the Oracle table? If yes, you could regen the master with the setting

ENGINE SQLORA SET VARCHAR OFF

before recreating the master. That will get rid of all the V formats in the master. That command has proved to be a life saver.

Kathy,

Maybe it's an idea to post your master file(s) here (the tm1jobs andd the fcst). This may allow us to try and reproduce your situation and possibly come up with a solution. It would also help if you could post one or two records from the tables.
If you do post, please put it between the code tags (the red '< / >' button).

Thanks Ginny! My next test was to try that. Useful command will save me lots of time. Interesting it did not give me an error when I tried joining the hold file to the oracle table. I will try the command this afternoon and let you know.

The problem is resolved. The fields in the mfd were changed from variable to fixed. Thank you everyone.