[SOLVED] Decode not producing correct result

DEFINE FILE CAT
CONTACT/A40=EDIT(CATALPHABETIC,'9999999999999999999999999999999999999999');
END

TABLE FILE CAT
PRINT
CONTACT
BY CONSTANT AS SECTOR
IF CODE EQ BUS
IF NUM EQ 11
ON TABLE HOLD AS FCONTACT
END

DEFINE FILE TEMP
CONTACTNUMBER/A40 = DECODE SECTOR(FCONTACT ELSE ' ');
END

TABLE FILE TEMP
PRINT
SECTOR
CONTACTNUMBER
END

What this should produce a different set of number per sector so for example:
SECTOR CONTACTNUMBER
SECTOR1 TEL: 2222 222 2222 FAX: 2222 222 2222
But instead it produces:
SECTOR CONTACTNUMBER
SECTOR1 TEL:
So it seems to be truncating data for some odd reason.
Typically we have used the code below to correct this problem, but this code is not working either, I get a format error in the decode:

 
DEFINE FILE CAT
CONTACT/A40=EDIT(CATALPHABETIC,'9999999999999999999999999999999999999999');
QUOTE/A5=' '' ';
END

TABLE FILE CAT
PRINT
QUOTE
CONTACT
QUOTE
BY CONSTANT AS SECTOR
IF CODE EQ BUS
IF NUM EQ 11
ON TABLE HOLD AS FCONTACT
END

(FOC272) FORMAT ERROR IN DECODE OR FILE ELEMENT

Is there a certain way one needs to handle string data with spaces for use within a decode?

Thanks!

J.

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

7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2

Posts: 78 | Location: UK | Registered: February 07, 2008

GamP

Virtuoso

posted

Hide Post

You should code as follows:

DEFINE FILE CAT
SKIP/A1 = ' ';
CONTACT/A42='''' | EDIT(CATALPHABETIC,'9999999999999999999999999999999999999999') | '''';
END
TABLE FILE CAT
PRINT SKIP CONTACT
BY CONSTANT AS SECTOR
IF CODE EQ BUS
IF NUM EQ 11
ON TABLE HOLD AS FCONTACT FORMAT ALPHA
END

DECODE requires values with embedded spaces to be embraced by single quotes.
If your field CONSTANT is also capable of holding spaces you should also provide code to encapsulate the values of CONSTANT in quotes. And, it may also be possible that your resulting hold file contains the fields without any spacing between them, so I put in an extra field (SKIP) to ensure at least one space is between the two fields.

GamP