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

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

T

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

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

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;

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

T

Thanks for the suggestion Tony. I'll give it a try.