1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Dynamic where clause based on parameter value

Discussion in 'Oracle Apps Technical' started by club, May 28, 2019.

  1. club

    club Active Member

    Messages:
    37
    Likes Received:
    1
    Trophy Points:
    185
    Hi All,

    I am facing challenge in writing where clause.

    Requirement: Based on parameter value from procedure , where clause condition needs to be build.
    for eg. let say we have to get status of concurrent program . Here p_status is parameter.

    if p_status := 'WARNING' then where clause should have condition status_code = 'G'
    if p_status:='ERROR' then where clause should have condition status_code= 'E'
    if p_status:='ALL' then where clause should have condition status_code IN ('G','E')

    I have tried using CASE statement in where clause but it is not working with IN clause.

    Can someone pls help me writing logic as per above requirement.

    Thanks in Advance.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,682
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post the code you've written and someone should be able to assist you.
     
  3. club

    club Active Member

    Messages:
    37
    Likes Received:
    1
    Trophy Points:
    185
    Please find code below.

    SELECT fcr.request_id,
    fcpt.user_concurrent_program_name,
    to_char(fcr.ACTUAL_START_DATE,'mm/dd/yyyy hh:mm:ss AM')ACTUAL_START_DATE ,
    to_char(fcr.ACTUAL_COMPLETION_DATE,'mm/dd/yyyy hh:mm:ss AM') ACTUAL_COMPLETION_DATE,
    decode(fcr.status_code,'E','ERROR','G','WARNING',fcr.status_code) status_code
    FROM apps.fnd_concurrent_requests fcr,
    apps.fnd_concurrent_programs_tl fcpt
    where 1=1
    AND fcpt.user_concurrent_program_name = :p_program_name
    AND fcpt.LANGUAGE = 'US'
    AND fcpt.concurrent_program_id = fcr.concurrent_program_id
    AND fcr.status_code = (CASE WHEN :p_status = 'ERROR' THEN 'E'
    WHEN :p_status = 'WARNING' THEN 'G'
    WHEN :p_status = 'ALL' THEN 'E' OR 'G'
    END)
    AND fcr.ACTUAL_COMPLETION_DATE >= sysdate-5/1440;

    I am facing issue with highlighted condition . Can we use IN clause in Case status.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,682
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not for an equality. Consider the WHERE clause you are trying to create and realize that writing

    WHERE A= 6 or 7

    is at the very least a syntax error. You should also realize that since you are wanting to create two DIFFERENT types of WHERE clauses it might be better to use PL/SQL and a reference cursor so you can create dynamic SQL statements that make sense:

    create or replace procedure p_concurrent_status (p_status in varchar2) is
    v_sql. varchar2(4000);
    ...
    if p_status = 'ERROR' then
    v_sql:= v_sql||' and fcr.status_code = '''E''';
    elsif p_status = 'WARNING" then
    v_sql:= v_sql||' and fcr.status_code = '''G''';
    elsif p_status = 'ALL' then
    v_sql := v_sql || 'and for.status_code in (''G''.''E'')';
    end if;
    ...