DECODE wildcard

[jwray100]

Is it possible to use wildcards in a SQL derived field with a DECODE statement to approximate a "LIKE". For example:

DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWHOMEDEPT", '%AH', ( NVL( SUM(DISTINCT "REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWGROSSPAYA") , 0 )), 0 )

to return the gross pay value for all records with a homedept ending in 'AH'? I tried this exact syntax and the DECODE is returning the default value for all records even though some match the criteria.

I'm wondering if my syntax is wrong for the wildcarded section?

Thanks,


Joel Ray
Ronile, Inc.

 

[RSHelp]

Instead of using a wildcard for the value I think you are going to have to parse the Home Department first and then compare to a literal.

-----

If you are using an SQLBase database try this:

@DECODE(@RIGHT("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWHOMEDEPT",2), 'AH'…

-----

If you are using an Oracle database and six digit departments try this:

DECODE(SUBSTR("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWHOMEDEPT",5,2), 'AH'…

-----

If you are using an Oracle database and three digit departments try this:

DECODE(SUBSTR("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWHOMEDEPT",2,2), 'AH'…

-----

Hope that helps.

 

[jwray100]

RSHelp,

Now why didn't I think of that!

Sometimes you need a fresh set of eyes to help you see the forest past the trees.

Thanks for the help.

Joel Ray
Ronile, Inc.