Can fields/define fields be used as a result in DECODE syntax?

Hi All,

I'm trying to replicate this SQL statement in WF:
SELECT
'CAR'
SUM(DECODE(COUNTRY, 'ITALY', DEALER_COST, 'JAPAN', RETAIL_COST, 0)) COST_TOT
FROM CAR
GROUP BY 'CAR'

Result:
'CAR' COST_TOT
CAR 23400

So, I created a define field called COST_TOT but it gave me an error when I validate it.

Source:
DEFINE FILE CAR
COST_TOT/D7=DECODE COUNTRY( ITALY DEALER_COST JAPAN RETAIL_COST ELSE O );
END

Error:
(FOC272) FORMAT ERROR IN DECODE OR FILE ELEMENT: DEALER_COST

The syntax says
DECODE fieldname(code1 result1 code2 result2...[ELSE default ]);

The result can be alpha or numeric. Can it be a FIELD that is alpha or numeric? Is there a work-around if it's not possible?

Thanks for the help.

Dan

Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE

Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005

Tony A

Expert

posted

Hide Post

Dan,

You could use the following, but it's a bit messy -

DEFINE FILE CAR
COST_TOT/D7 = (DECODE COUNTRY('ITALY' 1 ELSE 0) * DEALER_COST)
            + (DECODE COUNTRY('JAPAN' 1 ELSE 0) * RETAIL_COST);
END

This message has been edited. Last edited by: Tony A,

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004

Ignored post by Tony A posted

Show Post

dballest

Platinum Member

posted

Hide Post

Tony,

Thanks for the reply. For my report, your suggestion worked since DC and RC are numeric. For future reports, how do I do it when the field is ALPHA?

Dan

Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005

Ignored post by dballest posted

Show Post

susannah

Expert

posted

Hide Post

dan, the 'result' is a value, not a variable, in a decode statement.
T's suggestion is ultra cool code,
i would have taken the more pedestrian route
COST_TOT/D7=IF COUNTRY IS ITALY THEN DEALER_COST ELSE
IF COUNTRY IS JAPAN THE RETAIL_COST ELSE
0;

In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

Show Post

Tony A

Expert

posted

Hide Post

Dan,

I prefer to use boolean type logic when the result is numeric but for when the result is alpha then I would be inclined to use an external decode file -

TABLE FILE CAR
SUM CAR
BY COUNTRY
ON TABLE SAVE AS CARDEC
END
-RUN

DEFINE FILE CAR
COST_TOT/D7    = (DECODE COUNTRY('ITALY' 1 ELSE 0) * DEALER_COST)
               + (DECODE COUNTRY('JAPAN' 1 ELSE 0) * RETAIL_COST);
CAR_DECODE/A16 = DECODE COUNTRY(CARDEC ELSE '');
END

TABLE FILE CAR
SUM COST_TOT
BY COUNTRY
BY CAR_DECODE
END
-RUN