Home » SQL & PL/SQL » SQL & PL/SQL » CASE Statement on a Cursor (R12)
CASE Statement on a Cursor [message #681025] Mon, 08 June 2020 05:16 Go to next message
ajayhareesh
Messages: 24
Registered: September 2016
Junior Member
Is it possible to use case statement in a cursor?

Just a sample thought:
CURSOR cur IS
CASE WHEN ledger_id = 1000
SELECT emp_name FROM employees
ELSE
SELECT emp_name FROM managers
END
;
Re: CASE Statement on a Cursor [message #681026 is a reply to message #681025] Mon, 08 June 2020 07:40 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
no but you can do the following

  CURSOR Cur (P_ledger_id IN NUMBER)
    IS
        SELECT Emp_name
          FROM Employees
         WHERE P_ledger_id = 1000
        UNION ALL
        SELECT Emp_name
          FROM Managers
         WHERE P_ledger_id <> 1000;
Re: CASE Statement on a Cursor [message #681036 is a reply to message #681026] Mon, 08 June 2020 19:02 Go to previous messageGo to next message
ajayhareesh
Messages: 24
Registered: September 2016
Junior Member
Thank you very much for your response.
Re: CASE Statement on a Cursor [message #681045 is a reply to message #681036] Tue, 09 June 2020 06:37 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
I'd use cursor variables:

DECLARE
    V_CUR SYS_REFCURSOR;
    V_LEDGER_ID NUMBER;
BEGIN
    IF V_LEDGER_ID = 1000
      THEN
        OPEN V_CUR FOR 'SELECT emp_name FROM employees';
      ELSE
        OPEN V_CUR FOR 'SELECT emp_name FROM managers';
    END IF;

Using UNION ALL will scan both tables. Also, UNION ALL Michel posted isn't equivalent to your CASE statement when LEDGER_ID is NULL. In general, it should be:

  CURSOR Cur (P_ledger_id IN NUMBER)
    IS
        SELECT Emp_name
          FROM Employees
         WHERE P_ledger_id = 1000
        UNION ALL
        SELECT Emp_name
          FROM Managers
         WHERE P_ledger_id <> 1000
            OR P_ledger_id IS NULL;
SY.
Re: CASE Statement on a Cursor [message #681048 is a reply to message #681045] Tue, 09 June 2020 07:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67368
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For once, it is was not me but Bill. Smile

Quote:
Using UNION ALL will scan both tables.

This is not true.
As the WHERE clauses are exclusive and static, the optimizer knows (since version 7) that only one part of the UNION ALL needs to be executed (this is how we could emulate partitions in version 7).

But I agree with you about NULL which has to be specified and the use of cursor variables for code readability.

[Updated on: Tue, 09 June 2020 11:26]

Report message to a moderator

Re: CASE Statement on a Cursor [message #681049 is a reply to message #681048] Tue, 09 June 2020 07:39 Go to previous message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
Agreed:

SQL> EXPLAIN PLAN FOR
  2          SELECT deptno
  3            FROM emp
  4           WHERE :P_ledger_id = 1000
  5          UNION ALL
  6          SELECT deptno
  7            FROM dept
  8           WHERE :P_ledger_id <> 1000
  9              OR :P_ledger_id IS NULL;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 3683458442

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |    18 |    54 |     4   (0)| 00:00:01 |
|   1 |  UNION-ALL          |         |       |       |            |          |
|*  2 |   FILTER            |         |       |       |            |          |
|   3 |    TABLE ACCESS FULL| EMP     |    14 |    42 |     3   (0)| 00:00:01 |
|*  4 |   FILTER            |         |       |       |            |          |
|   5 |    INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER(:P_LEDGER_ID)=1000)
   4 - filter(TO_NUMBER(:P_LEDGER_ID)<>1000 OR :P_LEDGER_ID IS NULL)

18 rows selected.

SQL>

SY.

[Updated on: Tue, 09 June 2020 07:41]

Report message to a moderator

Previous Topic: Commands in SQl
Next Topic: Creating a not well-formed XML
Goto Forum:
  


Current Time: Wed Sep 23 00:13:39 CDT 2020