Hi All,

I created a hold table(HOLD1) from an existing table(TABLE1). I used an across field called 'YEAR' -- its format is 2005/06, 2006/07, etc. Then in the HOLD1, I'd like to sum the field '2005/06'. But I got an error message as shown below.

It's okay to use other fields, and it's also okay to use "PRINT *". So I'm guessing it's because of the special character '/'. Is there anyway to use '2005/06' explicitly?

Thanks in advance.

----- CODE

SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
TABLE FILE TABLE1
SUM
COUNT AS ''
BY DEPARTMANT
ACROSS LOWEST YEAR AS ''
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END


TABLE FILE HOLD1
SUM
2005/06
BY DEPARTMANT
ON TABLE NOTOTAL
END

----- ERROR Message

0 NUMBER OF RECORDS IN TABLE= 601 LINES= 78
0 ERROR AT OR NEAR LINE 24 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: 2005/06
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

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

Try removing the "AS ''" from the code - because you have (and need) ASNAMES=ON, those AS statements cause fields with no names in the HOLD file. Without "AS ''", the ACROSS statement will take the first three letters of the COUNT field name and create proper ACROSS field names, eg. COU200504.

Jenny,

1. I wouldn't want to have a field named COUNT
2. Check the HOLD1.mas and see what are the field names

Thank you for your reply. I tried three ways, and it still doesn't work.

1. Francis: I removed all those 'AS', but the generated across field names still contain '/', eg: COU2005/06, and it causes the same error message.

2. Danny: I changed the COUNT to HEADCNT, and here is the output of HOLD1.mas and HOLD1.ftm:

0 NUMBER OF RECORDS IN TABLE= 83 LINES= 11
FILENAME=HOLD1 , SUFFIX=FIX , IOTYPE=STREAM, $
SEGMENT=HOLD1, SEGTYPE=S1, $
FIELDNAME=DEPARTMENT, ALIAS=E01, USAGE=A255V, ACTUAL=A255W,
MISSING=ON, $
FIELDNAME=HEA2005/06, ALIAS=E02, USAGE=D12, ACTUAL=A12,
MISSING=ON, $
FIELDNAME=HEA2006/07, ALIAS=E03, USAGE=D12, ACTUAL=A12,
MISSING=ON, $
FIELDNAME=HEA2007/08, ALIAS=E04, USAGE=D12, ACTUAL=A12,
MISSING=ON, $
FIELDNAME=HEA2008/09, ALIAS=E05, USAGE=D12, ACTUAL=A12,
MISSING=ON, $
FIELDNAME=HEA2009/10, ALIAS=E06, USAGE=D12, ACTUAL=A12,
MISSING=ON, $
FIELDNAME=HEA2010/11, ALIAS=E07, USAGE=D12, ACTUAL=A12,
MISSING=ON, $
FIELDNAME=HEA2011/12, ALIAS=E08, USAGE=D12, ACTUAL=A12,
MISSING=ON, $
FIELDNAME=HEA2012/13, ALIAS=E09, USAGE=D12, ACTUAL=A12,
MISSING=ON, $
000064Department1
2891 3792 2679 2671 2889 3455 4308 5680

3. I also tried to use escape character(%2F for SLASH) OR CTRAN function to include a special character as follows:

SUM 2005%2F06

or

SPACE/A1 = HEXBYT(32, 'A1');
SLASH/A1 = CTRAN(10, SPACE, 32, 47, 'A1');
SUM 2005SLASH06

Any other ideas?

This is interesting, I was going to suggest wrapping the fields in quotes. but it also does not work

Is it possible that your "YEAR" field is not a "smart-date" field, but an alpha field where you concatenated the Year and Month with a slash in between?

What is the format of YEAR or what is the format of the field you derived YEAR from?

Hi Francis,

YEAR is a SQL DB field that has the type char(7), and its values in DBs are '2005/06', '2006/07' etc, where '2005/06' means fiscal year 2005 to 2006. When I across the YEAR in the first table, I got all its values in the HOLD file, so I guess it's not a derived field.

Jenny

Define a newyear field

NEWYEAR/A6=EDIT(YEAR,'9999$99');

Can you change the field to something else like '2005 to 06'.

e.g.

SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
DEFINE FILE CAR
FINYEAR/A7 = DECODE SEATS(2 '2006/07' 4 '2007/08' ELSE '2008/09') ;
FinYear/A10 = EDIT(FINYEAR,'9999$ to 99') ;
END
TABLE FILE CAR
SUM
SALES AS ''
BY COUNTRY
ACROSS FinYear
ON TABLE HOLD AS TEST
END
-RUN
! type test.mas
TABLE FILE TEST
PRINT
'2006 to 07'
'2007 to 08'
'2008 to 09'
BY COUNTRY
END

quote:

Define a newyear field

Too Quick.....

This solution worked perfectly. Thank you very much for all your answers!

Just one more question, I thought both space and slash are special characters, why PRINT '2006 to 2007' works but PRINT '2006/07' doesn't work?

This is something that IBI may have to answer.
But my guess is that it is related to the order of processing somewhere in WebFOCUS.

It is because the / is used to separate a field from its format... and a number is not a valid format...

Its a pity that it doesn't ignore the slash when you wrap the field in quotes.

Waz,

quote:

TABLE FILE TEST
PRINT
'2006 to 07'
'2007 to 08'
'2008 to 09'
BY COUNTRY
END

If the quotes would disable the /format what would you do with your example if you wanted to change the format? I know, use COMPUTE or DEFINE. Still...

I'd say that if there is a field name that matches the bit after the slash, then WF shouldn't interpret that bit as a format. But that's not watertight either.

I think the real issue is with how WF does quoting of fields. If it would expect quote around separate parts of a field path, errors like these couldn't occur. You'd get

PRINT
   'TABLE1'.'SEG01'.'2006/07'/A7
   'TABLE1'.'SEG01'.'2006/07'

-* or maybe
   'TABLE1.SEG01.2006/07'/A7
   'TABLE1.SEG01.2006/07'

-* instead of (ambiguous)
   'TABLE1.SEG01.2006/07/A7'
   'TABLE1.SEG01.2006/07'

Doesn't that make more sense than the current implementation?
Better yet, in SQL it's customary to use double-quotes (") to quote identifiers, so the distinction is immediately clear. It's a standard there, it would make sense to copy that over.

Yes, the quotes should deliniate the field, then normal coding follows.