CODEWITHSUNDEEP
sqloracle-database
user1019072
user1019072

Reputation: 299

SQL query with decoding and comparison in where clause

I have following requirement for writing a query in oracle.

I need to fetch all the records from a Table T1 (it has two date columns D1 and D2)based on two dynamic values V1 and V2. These V1 and V2 are passed dynamically from application. The possible values for V1 are 'Less than' or 'Greater than'. The possible value for V2 is a integer number.

Query i need to write:

If V1 is passed as 'Less than' and V2 is passed as 5, then I need to return all the rows in T1 WHERE D1-D2 < 5.

If V1 passed as 'Greater than' and V2 passed as 8, then I need to return all the rows in T1 WHERE D1-D2 > 8;

I could think that this can be done using a CASE statement in where clause. But not sure how to start.

Any help is greatly appreciated. Thanks

Upvotes: 1

Views: 818

Answers (3)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

PL/SQL Solution, not straight forward, but can be customized for complex logics!

Create nested table sql objects 

    CREATE TYPE MY_RECORD_TYPE IS OBJECT
    ( 
      D1 VARCHAR2(100),
      D2 VARCHAR2(100),
      D3 VARCHAR2(100),
      D4 VARCHAR2(100)
    );
    /

    CREATE TYPE MY_TABLE_TYPE IS TABLE OF MY_RECORD_TYPE;
    /

Here is the Function that returns a nested table.

   create or replace function check_me(v1 varchar2,v2 number)
    return MY_TABLE_TYPE
    is
    v_query varchar2(4000);
    my_cur sys_refcursor;

    v_record MYTABLE%ROWTYPE; -- Create a local record of table's row type

    v_nested MY_TABLE_TYPE := MY_TABLE_TYPE();

    I NUMBER;
    begin
     v_query := 'SELECT * FROM MYTABLE WHERE D1 - D2 ';
     IF( UPPER(v1) = UPPER('Less Than')) THEN
       v_query := v_query||' > :V2';
     ELSE
       v_query := v_query||' < :V2';
     END IF;

     DBMS_OUTPUT.PUT_LINE(v_query);
     OPEN my_cur FOR v_query USING V2;

     I:=0;
     LOOP
       FETCH my_cur INTO v_record;
       EXIT WHEN my_cur%NOTFOUND;

       v_nested.EXTEND(1);
       I := I + 1;

       /* Copy it into the nested table - Painful!!*/ 
       v_nested(I) := MY_RECORD_TYPE(
                                      v_record.D1,
                                      v_record.d2,
                                      v_record.D3,
                                      v_record.D4
                                     ); 
      END LOOP;

    CLOSE my_cur;--CLose the cursor
    return v_nested;--return the nested table
    end;
    /

And you can run as,

select * FROM TABLE(check_me(',',5));

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You could write this as:

select *
from t1
where (v1 = 'Less Than' and D1 - D2 < v2) or
      (v1 = 'Greater Than' and D1 - D2 > v2)

A case statement isn't needed.

Upvotes: 3

Vulcronos
Vulcronos

Reputation: 3456

Try this:

select *
from T1
where case when V1 = 'LESS THAN' THEN D1 - D2 < V2 ELSE D1 - D2 > V2

This assume if V1 is not LESS THAN the only other value is greater than. If necessary you can use more than one case statement but this should get you started.

Upvotes: 1

Related Questions