Brett Baloun wrote:
> anacedent <anacedent_at_hotmail.com> wrote in message news:<328wc.39213$oi5.14145_at_fed1read07>...
>
>>Brett Baloun wrote:
>>
>>>Hello,
>>>
>>>I am trying to build the Where clause w/in my function to complete the
>>>SQL statement. I want to build the clause based on parameters passed
>>>to the function and some other logic. The where_clause example in
>>>this function is just a basic example of what I ultimately want to do.
>>>
>>>I get the following error when I create the function:
>>>13/2 PL/SQL: SQL Statement ignored
>>>16/9 PLS-00382: expression is of wrong type
>>>
>>>CREATE OR REPLACE function GET_COUNT10(begin_dt IN VARCHAR2, end_dt IN
>>>VARCHAR2, status IN VARCHAR2)
>>>
>>>RETURN NUMBER
>>>
>>>IS
>>> total_count NUMBER;
>>> where_clause varchar2(100):= NULL;
>>>BEGIN
>>> IF begin_dt != null AND end_dt != null THEN
>>> where_clause := 'status = COMPLETE';
>>> END IF;
>>>
>>> select count(*)
>>> INTO total_count
>>> from GENERAL_ROSTER
>>> where where_clause;
>>>
>>> RETURN(total_count);
>>>END;
>>>/
>>>
>>>Thank you in advance for you assistance.
>>>Brett
>>
>>To do what you want to do, you'll need to use EXECUTE IMMEDIATE.
>>I suggest that you construct the whole SQL statement into a
>>single VARCHAR2 variable & 1st use DBMS_OUTPUT to display it.
>>The test it using SQL*Plus before proceeding.
>>
>>FWIW - your "WHERE CLAUSE" is FUBAR; if my assumption is correct.
>>It appears that "status" the the name of a VARCHAR2 column.
>>If so, then what you need is
>>where_clause := 'status = ' || CHR(39) || 'COMPLETE' || CHR(39);
>>
>>THT & YMMV
>
>
>
> Hello THT & YMMV,
>
> Thank you very much for your response - I really appreciate it.
>
> However, I am new to PL/SQL. Could you please be a little more
> detailed in your response?
>
> Yes, 'status' is a VARCHAR2 column. We would want to include multiple
> columns for the where clause - if passed to our function. I just
> wanted to include a sample of what we were doing. Ultimately, we want
> to build a where clause by adding additional clauses dynamically. IE
> where_clause := where_clause + status = 'COMPLETE' - is this possible?
>
> Thank you in advance for your assistance.
>
> Brett
For this type of problem the most usual way it is done is by having
WHERE 1 = 1 || <varchar_string>
For example
VARCHAR_STRING := ' AND VALUE1 = 2 AND VALUE2 = 7';
You just need to be careful WRT single quote marks when comparing to
VARCHAR2 datatypes.
Received on Mon Jun 07 2004 - 21:12:38 CDT
