Results 1 to 8 of 8

Thread: CASE to DECODE

  1. #1
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62

    Smile Unanswered: CASE to DECODE

    Hi all ...
    I have procedure which uses CASE statment.
    I need to re write it in DECODE..

    I have no idea how to write EXECUTE IMMEDIATE inside DECODE..
    Hope anyone will give suggestions to me..

    Here is the procedure

    CREATE OR REPLACE FUNCTION emp_count (
    p_job IN emp.job%TYPE DEFAULT NULL,
    p_deptno IN emp.deptno%TYPE DEFAULT NULL)
    RETURN NUMBER AS

    l_sql VARCHAR2(32767);
    l_number NUMBER;
    BEGIN

    l_sql := 'SELECT COUNT(*) INTO :l_number FROM emp WHERE 1=1 ';

    IF p_job IS NOT NULL THEN
    context_api.set_parameter('job', p_job);
    l_sql := l_sql || 'AND job = SYS_CONTEXT(''parameter'',''job'') ';
    END IF;

    IF p_deptno IS NOT NULL THEN
    context_api.set_parameter('deptno', p_deptno);
    l_sql := l_sql || 'AND deptno = SYS_CONTEXT(''parameter'',''deptno'') ';
    END IF;

    DBMS_OUTPUT.PUT_LINE(l_sql);

    EXECUTE IMMEDIATE l_sql INTO l_number;

    RETURN l_number;
    END emp_count;
    /


    It works fine.

    I rewrote it in DECODE as follows

    CREATE OR REPLACE FUNCTION emp_count (
    p_job IN emp.job%TYPE DEFAULT NULL,
    p_deptno IN emp.deptno%TYPE DEFAULT NULL) RETURN NUMBER AS

    l_sql VARCHAR2(32767);
    l_number NUMBER;
    BEGIN

    l_sql := 'SELECT COUNT(*) INTO :l_number FROM emp WHERE 1=1 ';

    IF p_job IS NOT NULL THEN
    l_sql := l_sql || 'AND job = :job ';
    END IF;

    IF p_deptno IS NOT NULL THEN
    l_sql := l_sql || 'AND deptno = :deptno ';
    END IF;

    DBMS_OUTPUT.PUT_LINE(l_sql);

    select decode ( p_job, null,
    decode (p_deptno, null,
    'EXECUTE IMMEDIATE l_sql INTO l_number',
    'EXECUTE IMMEDIATE l_sql INTO l_number USING p_deptno'),
    decode (p_deptno, null,
    'EXECUTE IMMEDIATE l_sql INTO l_number USING p_job',
    'EXECUTE IMMEDIATE l_sql INTO l_number USING p_job, p_deptno'))
    into l_number
    from emp;


    RETURN l_number;
    END emp_count;
    /


    Oopz tatz a big blunder i know...
    So anybody can help me??
    thanx....

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    EXECUTE IMMEDIATE is plsql and cant be placed in a select statement.

    1) I dont see any case statement in the orginal?
    2) Replace decode with if..then..else...

    Alan

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    maybe he means change the IF statements to a DECODE statement?

    I am still trying to figure out why he needs to change it and what he is
    trying to do. What if both statements are true?
    ie: both values are NOT NULL
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    He's trying to use bind variables, rather than concatenating them into the sql. How the issue is case vs decode is beyond me.
    It also appears that he wants the where clause to handle null bind variables for the "=".

    So, maybe:
    SELECT COUNT(*)
    INTO l_number
    FROM emp
    WHERE (job = p_job or (job is null and p_job is null))
    and (deptno = p_deptno or (deptno is null and p_deptno is null))

    You know, way before dynamic sql was invented, we had to think about our problems, and come up with complex sql to solve things (although the above sql isn't really complex, now is it?). Also, the "into" clause is not part of the dynamic string passed to "execute immediate", it's a clause of the "execute immediate". Read a manual, and learn sql before you jump into pl/sql and dynamic sql to solve easy problems.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  5. #5
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62
    Im sorry dear pals...

    Here is my real procedure..

    CREATE OR REPLACE FUNCTION emp_count (
    p_job IN emp.job%TYPE DEFAULT NULL,
    p_deptno IN emp.deptno%TYPE DEFAULT NULL)
    RETURN NUMBER AS

    l_sql VARCHAR2(32767);
    l_number NUMBER;
    BEGIN

    l_sql := 'SELECT COUNT(*) INTO :l_number FROM emp WHERE 1=1 ';

    IF p_job IS NOT NULL THEN
    l_sql := l_sql || 'AND job = :job ';
    END IF;

    IF p_deptno IS NOT NULL THEN
    l_sql := l_sql || 'AND deptno = :deptno ';
    END IF;

    DBMS_OUTPUT.PUT_LINE(l_sql);

    CASE
    WHEN p_job IS NOT NULL AND p_deptno IS NULL THEN
    EXECUTE IMMEDIATE l_sql INTO l_number USING p_job;
    WHEN p_job IS NULL AND p_deptno IS NOT NULL THEN
    EXECUTE IMMEDIATE l_sql INTO l_number USING p_deptno;
    WHEN p_job IS NOT NULL AND p_deptno IS NOT NULL THEN
    EXECUTE IMMEDIATE l_sql INTO l_number USING p_job, p_deptno;
    ELSE
    EXECUTE IMMEDIATE l_sql INTO l_number;
    END CASE;

    RETURN l_number;
    END emp_count;
    /

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Just turn the CASE/WHEN/WHEN/ELSE into IF/ELSIF/ELSIF/ELSE like you would see in any PL/SQL program.

  7. #7
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62
    Thanx.
    I was able to do it with IF ELSE, I actually wanted to know whether it can be done by replacing CASE with DECODE.
    Last edited by neema; 12-06-04 at 00:21.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, DECODE only works in SQL, not in PL/SQL.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •