Re: PL/SQL Dynamic where clause Q

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/01/22
Message-ID: <34c79a6e.4020713@www.sigov.si>#1/1

On 22 Jan 1998 15:58:08 GMT, nhughes_at_well.com (Nathan D. Hughes) wrote:

>I've got a procedure that builds a query based on the parameters
>passed in. In the WHERE clause, I'd like to match a status
>code with multiple values using an IN, i.e.
>
>where status in ('OPEN','WAITING')
>
>Assigning a variable the complete list (p_status :=
>'''OPEN'',''WAITING''';, WHERE status IN (p_status);) does not work
>in the where clause - no rows returned. Using seperate variables for
>each status code will work in the where (WHERE status IN (p_status1,
>p_status2); ), but due to the dynamic nature of the query, this
>solution is not optimal. I've played around with the p_status variable
>format (single quotes different places, etc.) but haven't gotten a result
>that works.
>
>Any workarounds? Thanks!

Why not using dynamic SQl (DBMS_SQL)? It seems to me the most natural and elegant solution for your requirements.

>
>Nathan D. Hughes <nhughes_at_well.com>
>Senior Consultant, Custom Business Solutions, Inc.

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Jan 22 1998 - 00:00:00 CST