How to select a WHERE condition depending on another cond

[saby]

Hi all,

Can u pls help me on this.

I want to have something like below in procedure.

----------------------------------
FOR CUR_A IN (
SELECT A.AMOUNT , B.PLANID ,B.EVENTCD
FROM ACCUMULATON A, PLAN B
WHERE A.PLANID = B.PLANID
AND B.DEALERID = 1
IF aFlag = 1 THEN
AND A.OFFERING = B.OFFERING
ENDIF
)
LOOP ......
--------------------

The if else statement is what I want given logically. aFlag is a variable to be set dynamically. I want the last AND condition to be used depending on the Flag value. The Cursor Loop must stay.

Can any one tell me how can do this?

saby

[willakawill]

Hi all,

Can u pls help me on this.

I want to have something like below in procedure.

----------------------------------
FOR CUR_A IN (
SELECT A.AMOUNT , B.PLANID ,B.EVENTCD
FROM ACCUMULATON A, PLAN B
WHERE A.PLANID = B.PLANID
AND B.DEALERID = 1
IF aFlag = 1 THEN
AND A.OFFERING = B.OFFERING
ENDIF
)
LOOP ......
--------------------

The if else statement is what I want given logically. aFlag is a variable to be set dynamically. I want the last AND condition to be used depending on the Flag value. The Cursor Loop must stay.

Can any one tell me how can do this?

saby

Try this out:

FOR CUR_A IN (
IF aFlag = 1 THEN
SELECT A.AMOUNT , B.PLANID ,B.EVENTCD
FROM ACCUMULATON A, PLAN B
WHERE A.PLANID = B.PLANID
AND B.DEALERID = 1
AND A.OFFERING = B.OFFERING
ELSE
SELECT A.AMOUNT , B.PLANID ,B.EVENTCD
FROM ACCUMULATON A, PLAN B
WHERE A.PLANID = B.PLANID
AND B.DEALERID = 1
ENDIF
)
LOOP ......

[sonic2k6]

Hi all,

Can u pls help me on this.

I want to have something like below in procedure.

----------------------------------
FOR CUR_A IN (
SELECT A.AMOUNT , B.PLANID ,B.EVENTCD
FROM ACCUMULATON A, PLAN B
WHERE A.PLANID = B.PLANID
AND B.DEALERID = 1
IF aFlag = 1 THEN
AND A.OFFERING = B.OFFERING
ENDIF
)
LOOP ......
--------------------

The if else statement is what I want given logically. aFlag is a variable to be set dynamically. I want the last AND condition to be used depending on the Flag value. The Cursor Loop must stay.

Can any one tell me how can do this?

saby

The first idea I have is to do somthing like this:

DECLARE

CURSOR CUR_A(aFlag IN NUMBER)
IS
SELECT A.AMOUNT , B.PLANID ,B.EVENTCD
FROM ACCUMULATON A, PLAN B
WHERE A.PLANID = B.PLANID
AND B.DEALERID = 1
AND aFlag <> 1

UNION

SELECT A.AMOUNT , B.PLANID ,B.EVENTCD
FROM ACCUMULATON A, PLAN B
WHERE A.PLANID = B.PLANID
AND B.DEALERID = 1
AND aFlag = 1
AND A.OFFERING = B.OFFERING;

flag NUMBER;

BEGIN

flag := 1;

FOR CUR_A_REC IN CUR_A(flag) LOOP

............... ............... .........
............... ............... .........

END LOOP;

END;

[pragatiswain]

I feel willakawill's solution is acceptable as per the question.

[saby]

Thanks.
Sorry, but IF --ELSE is not being accepted within FOR -- IN.
The following error coming up while compiling.
---------------------------------------------------------------------------
ORA-06550: line 7, column 4:
PLS-00103: Encountered the symbol "IF" when expecting one of the following:

( - + case mod new not null others select <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
--------------------------------------------------------------------------------

saby

Try this out:

FOR CUR_A IN (
IF aFlag = 1 THEN
SELECT A.AMOUNT , B.PLANID ,B.EVENTCD
FROM ACCUMULATON A, PLAN B
WHERE A.PLANID = B.PLANID
AND B.DEALERID = 1
AND A.OFFERING = B.OFFERING
ELSE
SELECT A.AMOUNT , B.PLANID ,B.EVENTCD
FROM ACCUMULATON A, PLAN B
WHERE A.PLANID = B.PLANID
AND B.DEALERID = 1
ENDIF
)
LOOP ......

[pragatiswain]

Thanks.
Sorry, but IF --ELSE is not being accepted within FOR -- IN.
The following error coming up while compiling.
---------------------------------------------------------------------------
ORA-06550: line 7, column 4:
PLS-00103: Encountered the symbol "IF" when expecting one of the following:

( - + case mod new not null others select <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
--------------------------------------------------------------------------------

saby

Check if this works....

DECLARE
MY_SQL VARCHAR2;
SELECT 'SELECT A.AMOUNT , B.PLANID ,B.EVENTCD FROM ACCUMULATON A, PLAN B WHERE A.PLANID = B.PLANID AND B.DEALERID = 1 AND' || DECODE(AFLAG,1, 'A.OFFERING = B.OFFERING','B. DEALERID = 1') INTO MY_SQL FROM DUAL

FOR CUR_A IN (MY_SQL)
LOOP ......

Other wise you have to use Dynamic SQL, using DBMS_SQL package.

[suvam]

Hi , u may try Sonic2k6 query or if U want not to use Union for performance issue, U may use this ----->

SELECT A.AMOUNT , B.PLANID ,B.EVENTCD
FROM ACCUMULATON A, PLAN B
WHERE A.PLANID = B.PLANID
AND B.DEALERID = 1
AND( (A.OFFERING = B.OFFERING AND aFlag = 1) OR(1=1)) ;