Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Decode Function Question
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Decode Function Question
 Login/Join
 
Member
posted
Hello,

I have a slight problem with the following Decode function:

SSN4/A4V=SUBSTR(9, SYVPER1_SSN, 6, 9, 4, 'A4V');
CardServ/A13V=SOARHLD3.SEG01.SYVSTAL_ID ||DECODE J15.SYVPER1.SYVPER1_SSN ( 'FILE' '9999' '' '9999' ELSE SSN4/A4V=SUBSTR(9, SYVPER1_SSN, 6, 9, 4, 'A4V'));



What this does is check for a SSN. If the SSN is either not there or labeled as 'FILE' the value 9999 is returned and displayed. However, if the SSN is there, then the last four digits of the SSN are concatenated to the the student's Campus ID. The problem that I am having is that this does not seem to work.

I get an error that says : (FOC224) SYNTAX ERROR: DECODE

This error occurs only when I'm adding the default value:
 ELSE SSN4/A4V=SUBSTR(9, SYVPER1_SSN, 6, 9, 4, 'A4V') 


When I do the following it works:



DEFINE FILE SOARHLD3
SSN4/A4=IF J15.SYVPER1.SYVPER1_SSN EQ 'FILE' OR J15.SYVPER1.SYVPER1_SSN EQ '' THEN '9999' ELSE SUBSTR(9, SYVPER1.SYVPER1.SYVPER1_SSN, 6, 9, 4, 'A4V');
CardServ/A15=SOARHLD3.SEG01.SYVSTAL_ID||J15.SYVPER1.SSN4;
END

 



I did not use the decode function in the code sample above.

I'm curious as to why the decode function doesn't work.

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


WebFOCUS 8

Windows, All Outputs
 
Posts: 11 | Registered: May 02, 2018Reply With QuoteReport This Post
Expert
posted Hide Post
SSN4/A4V=SUBSTR(9, SYVPER1_SSN, 6, 9, 4, 'A4V');
CardServ/A13V=SOARHLD3.SEG01.SYVSTAL_ID ||DECODE J15.SYVPER1.SYVPER1_SSN ( 'FILE' '9999' '' '9999' ELSE SSN4/A4V=SUBSTR(9, SYVPER1_SSN, 6, 9, 4, 'A4V'));

This is invalid syntax.

1) In the DEFINE for CardServ, you cannot use DECODE like this - you cannot concatenate the result of the DECODE to another column.

2) You DEFINE CardServ with a format of A4V but you have a different column being defined (SSN4/A4V=SUBSTR...) in the DECODE statement.

3) "If the SSN is ... not there" - does this mean it is null? You probably cannot use the DECODE value of blanl (''). The best way to deal with this is to use IF THEN ELSE.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
SSN4/A4V=SUBSTR(9, SYVPER1_SSN, 6, 9, 4, 'A4V')

You can't use = in the ELSE part of the decode. Try EDIT(SYVPER1_SSN,'$$$$$9999') instead. That'll give you the last 4 digits of SSN.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
I didn't know that you couldn't concatenate the results from Decode. That is very helpful to know. Thank you.

Anguel


WebFOCUS 8

Windows, All Outputs
 
Posts: 11 | Registered: May 02, 2018Reply With QuoteReport This Post
Gold member
posted Hide Post
You can concatenate decode as an expression, but you need to be careful about the left-hand-side format and about strong and weak concatenation. Also, remember that the code/decode pairs and the else must be *literals* and not fields (or expressions). In this case, you really should read the documentation on the DECODE operation.

Try this:
DEFINE FILE CAR
MYA/A25V = '->DECODE: ' |
     DECODE COUNTRY('ENGLAND'  'WOW'  'FRANCE'  'MAN' ELSE 'OK')
     || ' COOL' ;
END
-RUN
TABLE FILE CAR
PRINT COUNTRY MYA
END
-RUN


 COUNTRY     MYA                                                               
 -------     ---                                                               
 ENGLAND     ->DECODE: WOW COOL       
 JAPAN       ->DECODE: OK COOL        
 ITALY       ->DECODE: OK COOL        
 W GERMANY   ->DECODE: OK COOL        
 FRANCE      ->DECODE: MAN COOL       



IBI Development
 
Posts: 61 | Registered: November 15, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Decode Function Question

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.