While I was trying to figure out the best way to use across fields with ASNAMES on I encountered a weird error where it did not recognize the field name from a compute. I can get it to work using a typical repeat and read but I was wondering if anyone had any idea why the compute isn't working?
-SET &ECHO=ALL;
SET ASNAMES = ON
-SET &TABLE_FILE='HOLDDATA';
-SET &FIELD_AS_NAME='R_COST_';
TABLE FILE CAR
SUM RETAIL_COST AS '&FIELD_AS_NAME'
BY COUNTRY
BY BODYTYPE
ACROSS SEATS
ON TABLE HOLD AS HOLDDATA
END
TABLE FILE SYSCOLUM
SUM COMPUTE COL_LIST/A2000V=IF NAME NE LAST NAME THEN (IF ARGLEN(2000,LAST COL_LIST,'I7') GT 0 THEN COL_LIST || ':' | NAME ELSE NAME) ELSE NAME;
BY NAME
WHERE TBNAME EQ '&TABLE_FILE';
WHERE NAME LIKE '&FIELD_AS_NAME.%';
ON TABLE HOLD AS HOLD_COL
END
-RUN
-SET &COL_NAME='';
-SET &COL_NAMES='';
-REPEAT :LOOP FOR &I FROM 1 TO &RECORDS
-READ HOLD_COL &COL_NAME.A512
-SET &COL_NAMES= &COL_NAMES || ':' || &COL_NAME;
-:LOOP
-SET &COL_NAMES=STRREP(2000,&COL_NAMES,1,':',1,' ',&COL_NAMES.LENGTH,'A&COL_NAMES.LENGTH');
TABLE FILE HOLD_COL
SUM LST.COL_LIST
ON TABLE HOLD AS HOLD_COL
END
-RUN
-READ HOLD_COL &COL_LIST.A2000
-SET &COL_LIST_LEN=ARGLEN(2000,&COL_LIST,'I7');
-SET &COL_LIST=STRREP(2000,&COL_LIST,1,':',1,' ',&COL_LIST_LEN,'A&COL_LIST_LEN.EVAL');
TABLE FILE HOLDDATA
PRINT
&COL_LIST
&COL_NAMES
BODYTYPE
BY COUNTRY
END
This message has been edited. Last edited by: Crymsyn,
WF: 8201, OS: Windows, Output: HTML, PDF, Excel
April 25, 2014, 04:46 AM
Alan B
Not sure what you are trying to achieve, but:
-READ HOLD_COL &COL_NAME.A512
should be
-READ HOLD_COL &COL_NAME.A66
which is the length of NAME from SYSCOLUM.
And, because you are using A2000V
-READ HOLD_COL &COL_LIST.A2000
should be
-READ HOLD_COL &dummy.6 &COL_LIST.A2000
to remove the length portion of the output.
Alan. WF 7.705/8.007
April 25, 2014, 05:57 AM
Tewy
Like Alan I'm not really sure what you are trying to do. If you are trying to get the column names from your holdfile, add an asname and print the resulting columns the below would be easier.
If it's something else your after we'll need more details
TABLE FILE CAR SUM RETAIL_COST AS '&FIELD_AS_NAME' BY COUNTRY BY BODYTYPE ACROSS SEATS ON TABLE HOLD AS HOLDDATA END -RUN DEFINE FILE SYSCOLUM AS_NAME/A50=' AS '|'Field'|GETTOK(NAME,66,-1,'_',2,'A2'); END TABLE FILE SYSCOLUM PRINT NAME AS_NAME WHERE TBNAME EQ '&TABLE_FILE'; WHERE NAME LIKE '&FIELD_AS_NAME.%'; ON TABLE HOLD AS HOLD_COL END -RUN TABLE FILE HOLDDATA PRINT -INCLUDE HOLD_COL BY COUNTRY BY BODYTYPE END
WF 7.6.11 Output: HTML, PDF, Excel
April 25, 2014, 11:40 AM
Crymsyn
Basically what I was trying to do was find a good way to use the across asnamed fields from a hold file without using PRINT * or manually entering the field names. It isn't for anything currently just trying it out and testing. It was during that testing that I found that when I got the list of field names using the compute COL_LIST it was saying it didn't recognize the field.
The repeat is just there to show that it works using just &COL_NAMES.
Alan The reason why I was using A512 on that is because that is the format I was getting with ? HOLD. I tried adding the &dummy but then it wasn't storing any values in &COL_LIST unless I changed it to &dummy.2 instead of &dummy.6. Changing it to a -READFILE also allowed it to work most likely because it does that for me.
Tewy I tried yours as well but when I use -INCLUDE it is looking for a fex and changing it to the fex name would make a neverending recursive call.
WF: 8201, OS: Windows, Output: HTML, PDF, Excel
April 25, 2014, 02:18 PM
Doug
Don't forget the closing period on the "&COL_NAME.A66", Needs to be "&COL_NAME.A66.", at least in WebFOCUS 8
quote:
-READ HOLD_COL &COL_NAME.A512
should be
-READ HOLD_COL &COL_NAME.A66
And, while you're at it, truncate the &variables that you read in...
April 25, 2014, 02:52 PM
Doug
Also, consider using asnames and holdlist when holding files that you want to read and truncating the results of your read, and so much more...
April 25, 2014, 04:00 PM
Crymsyn
Doug Hmm didn't know about the read needing a closing period after the format that is good to know.
Also I am not quite sure I am understanding your second post. What are you meaning by truncating the results of my read? I am taking out any trailing spaces before using. Also I didn't include the sets that I normally include but is the following kind of what you were talking about?
SET ASNAMES = ON
SET BYDISPLAY = ON
SET FIELDNAME = NOTRUNC
SET EMPTYREPORT = ANSI
SET HOLDLIST = PRINTONLY
SET DATETIME = NOW
WF: 8201, OS: Windows, Output: HTML, PDF, Excel
April 25, 2014, 05:19 PM
Doug
SET ASNAMES = ON SET BYDISPLAY = ON - N/A to this issue SET FIELDNAME = NOTRUNC - N/A to this issue SET EMPTYREPORT = ANSI - N/A to this issue SET HOLDLIST = PRINTONLY SET DATETIME = NOW - N/A to this issue
The truncate command is better then this: -SET &COL_NAMES=STRREP(2000,&COL_NAMES,1,':',1,' ',&COL_NAMES.LENGTH,'A&COL_NAMES.LENGTH'); imho...
April 25, 2014, 06:32 PM
Crymsyn
Doug Ah thanks I didn't know about the truncate function, hence my confusion, but that is quite useful with dialogue manager commands.
I will put solved on this though since the main issue seemed to have been my -READ was storing the length portion into the variable but wasn't displaying that.
Thanks Alan, Tewy, and Doug learned a bit more from all of your posts.