Patterned like in Derived Field

[christimess]

I am trying to create a derived field that looks like this:
DECODE(SUBSTR("PS_EMPLOYMENT"."CUSTOM_AREA1",1,4),
LIKE '1%%%', 'FEDERAL',
LIKE '2%%%', 'STATE',
'UNKNOWN')
However, it does not like my statement. I know I can do it the long way:
DECODE(SUBSTR("PS_EMPLOYMENT"."CUSTOM_AREA1",1,4),
'1000', 'FEDERAL',
'1001', 'FEDERAL',
and so on, but the list would be very long and would take so long to do I though there has to be a better way, which is why I thought of using the LIKE statement.
Thanks for your help.

 

[CharlesCook]

You can't use Like in a Derived Field. The reason it shows up in the Derived Field editor is beause the same editor is used for SQL Selection Critera and Like is valid there.

Try this:

DECODE(LEFT("PS_EMPLOYMENT"."CUSTOM_AREA1",1),
'1', 'FEDERAL',
'2', 'STATE',
'UNKNOWN')

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting