Re: Problem in escaping the single quote in DECODE

On May 21, 6:58 pm, Anto <antos..._at_gmail.com> wrote:
> Hi all,
>
> I have the following query.
>
> SELECT MAX(emptype_flg)
> FROM employee
> AND emptype_flg IN (DECODE ('emptype_flg', '*' , emptype_flg,
> 'S' , '*,S'));
>
> The emptype_flg can contain values '*' or 'S'.
>
> The problem is that when the emptype_flg contains value S, the SQL
> does not give any output. This is because the IN block contains
> ('*,S')
>
> How can I get ('*', 'S') in the IN block so that the above query
> works???
>
> Any pointers will be highly appreciated!!
>
> Regards,
> Antoshin Lazar.

Why do you use DECODE here in the first place? What's exact behavior of the query you're after? What can emptype_flg contain? Is it a single character column which can contain either '*' or 'S'? If so, then simple IN ('*','S') will do, no DECODE necessary (unless you try to use * as a wildcard character, which is isn't.) Otherwise, please explain the logic you're trying to implement. And which Oracle version you run?

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Mon May 21 2007 - 10:31:28 CDT