Second time I've bumped into this in 8.2

I'm doing a big complicated select and aggregation, and am using Hold files (format xfocus) to preprocess interim results. When I do joins to bring the data together I'm finding that I cannot use the fieldnames in the temporary master files.

I have to reference the alias tags, which are set to E01, E02, etc., instead of the name of the field.

This is fraught with peril for me, and I'm using Acrosses to generate columns so the fields are not in dependable positions.

Did they tighten up the compiler on something? Is there a setting that will fix this?

This message has been edited. Last edited by: FP Mod Chuck,

I've not seen any change in behavior in hold. Are these reports that used to run but fail now in 8.2? Do you have ASNAME and HOLDLIST set?

Both of those set, yes.

I'm in the process of pulling the source back to 8.1. I really have a mess on my hands. The compiler is throwing really strange things at me.

So I'm having to "pre-process" any hold file that I want to use in a join. The join seems to be the issue.

If I take the hold file and then perform an immediate "PRINT *" on it and put it into a new hold file, that new hold file works. The 2 hold files look the same, but the second one works.

Somehow the first hold file is wonked, due to it being a SUM perhaps? Had the same problem a while back with a multi-verb, it appears that it wasn't just due to the complexity. I've never run into this before and I have 20 years in.

I am curios John, are you able to reproduce that using legacy files ?

At least, can you show us your code ?

TABLE FILE ALL_CATS
SUM SERVICE_DT
    CNT.CATEGORY AS 'ALL_DLS'
    DLS_DOCS_ONLY
    DLS_RENEWAL_DOC_SUBMISSION
    DLS_RENEWAL
    DLS_NEW_DOC_SUBMISSION AS 'DLS_NEW_DOC_SUBMISSION'
    DLS_NEW
BY SERVICE_DT NOPRINT
BY DLS_UNIT_NAME 
WHERE APPLICATIONDATETIME GE '&START_DATE_TIME' ;
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS DLS_COLUMNS FORMAT XFOCUS INDEX SERVICE_DT DLS_UNIT_NAME
END
-RUN
? HOLD DLS_COLUMNS


TABLE FILE DLS_COLUMNS
PRINT ALL_DLS
      DLS_DOCS_ONLY
      DLS_RENEWAL_DOC_SUBMISSION
      DLS_RENEWAL
      DLS_NEW_DOC_SUBMISSION
      DLS_NEW
BY SERVICE_DT
BY DLS_UNIT_NAME
ON TABLE HOLD AS DLS_COLUMNS_2 FORMAT XFOCUS INDEX SERVICE_DT DLS_UNIT_NAME
END
? HOLD DLS_COLUMNS_2

The first one does not work properly as the child of a join, the second one does. The two hold file definitions --

04/16/2019 13:59:11    LOCAL_8121            0DEFINITION OF HOLD FILE: DLS_COLU
04/16/2019 13:59:11    LOCAL_8121            0FIELDNAME                         ALIAS         FORMAT
04/16/2019 13:59:11    LOCAL_8121            DLS_UNIT_NAME                     E01           A30
04/16/2019 13:59:11    LOCAL_8121            SERVICE_DT                        E02           YYMD
04/16/2019 13:59:11    LOCAL_8121            ALL_DLS                           E03           I5
04/16/2019 13:59:11    LOCAL_8121            DLS_DOCS_ONLY                     E04           I9C
04/16/2019 13:59:11    LOCAL_8121            DLS_RENEWAL_DOC_SUBMISSION        E05           I9C
04/16/2019 13:59:11    LOCAL_8121            DLS_RENEWAL                       E06           I9C
04/16/2019 13:59:11    LOCAL_8121            DLS_NEW_DOC_SUBMISSION            E07           I9C
04/16/2019 13:59:11    LOCAL_8121            DLS_NEW                           E08           I9C
04/16/2019 13:59:11    LOCAL_8121            1
04/16/2019 13:59:11    LOCAL_8121            0 NUMBER OF RECORDS IN TABLE=    19869  LINES=  19869
04/16/2019 13:59:11    LOCAL_8121            0DEFINITION OF HOLD FILE: DLS_COLU
04/16/2019 13:59:11    LOCAL_8121            0FIELDNAME                         ALIAS         FORMAT
04/16/2019 13:59:11    LOCAL_8121            SERVICE_DT                        E01           YYMD
04/16/2019 13:59:11    LOCAL_8121            DLS_UNIT_NAME                     E02           A30
04/16/2019 13:59:11    LOCAL_8121            FOCLIST                           E03           I5
04/16/2019 13:59:11    LOCAL_8121            ALL_DLS                           E04           I5
04/16/2019 13:59:11    LOCAL_8121            DLS_DOCS_ONLY                     E05           I9C
04/16/2019 13:59:11    LOCAL_8121            DLS_RENEWAL_DOC_SUBMISSION        E06           I9C
04/16/2019 13:59:11    LOCAL_8121            DLS_RENEWAL                       E07           I9C
04/16/2019 13:59:11    LOCAL_8121            DLS_NEW_DOC_SUBMISSION            E08           I9C
04/16/2019 13:59:11    LOCAL_8121            DLS_NEW                           E09           I9C

In this final form they are just a shade bit different (field ordering, and a FOCLIST entry), but earlier they had identical ordering.

A FOCLIST field is added when you use PRINT instead of SUM.
This to insure that each line is unique and this even if your BY fields make them unique.

Also in your second TABLE file you force to have SERVICE_DT and DLS_UNIT_NAME as the two BY fields in that order when in the first TABLE file SERVICE_DT is a SUM field.
The BY SERVICE_DT NOPRINT, even if it is used to order the data it is not part of the output at first position (E01) but at second one (E02).

Based on that, you cannot have the same output for sure.

Now, to tell that something has changed from 8.2 previous version I can't say. For me all seems to work as it should based on the way the TABLE file are coded.

The combination NOPRINT and ON TABLE SET HOLDLIST PRINTONLY makes sure that the field will not be in the outputfile. Maybe there was a bug before that if you mentioned the field in an index that the field still showed up in the outputfile.

Oh the fields are there, they just don't get their names attached to them.

If it were me, I would have this:

quote:

TABLE FILE ALL_CATS
SUM CNT.CATEGORY AS 'ALL_DLS'
DLS_DOCS_ONLY
DLS_RENEWAL_DOC_SUBMISSION
DLS_RENEWAL
DLS_NEW_DOC_SUBMISSION AS 'DLS_NEW_DOC_SUBMISSION'
DLS_NEW
BY SERVICE_DT
BY DLS_UNIT_NAME
WHERE APPLICATIONDATETIME GE '&START_DATE_TIME' ;
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS DLS_COLUMNS FORMAT XFOCUS INDEX SERVICE_DT DLS_UNIT_NAME
END
-RUN

I also notice that you have an AS for DLS_NEW_DOC_SUBMISSION. Why? Does it have a TITLE value in the master? Do any of the other fields without the AS phrase have TITLE values in the master? If you have ASNAMES ON, the fields without the AS phrase will use the TITLE values as the new field names.

That source code has the remnants of attempts from me to correct the problem I was having. That's why you see AS entries that make no change, the Service Date included twice and NOPRINTed once, etc.

In the long run the only way I could fix it was to let it save the hold file, and then immediately do a PRINT * from that hold file to a new hold file, which miraculously rediscovered the names of the fields.

I'm seeing a couple of odd behaviors in 8.2, when 8.1 seems rock-solid. Don't know if it's just that I've been programmed to avoid the soft spots in 8.1. That may be the case.

This reminds me of an answer I recently got from Tech Support for column titles when storing HOLD data in an RDBMS table.

This is what they said:

quote:

TABLE HOLD FORMAT dbms transfers to the created underlying table the following MFD attributes:
- field TITLE;
- field DESCRIPTION;
- segment DESCRIPTION;
- file REMARKS.
CREATE SYNONYM on the underlying table gets the attributes values back.

SET HOLDATTR controls which attributes are transferred.
SET HOLDATTR=ON(default): only field attributes are transferred;
SET HOLDATTR=CUBE : all these attributes are transferred;
SET HOLDATTR=OFF : none of these attributes are transferred

(...)

This was Implemented as of 7708m/8203M for the major adapters: SQLMSS/MSODBC, DB2, SQLIIA, SQLDBC, SQLORA, SQLMYSQL, SQLHYPG

Perhaps this applies to your case as well?