NESTED IF in Oracle SQL

Question

--mesinicio parameter goes here
IF 1 = 1 
 THEN


---cantmeses parameter also here
        IF 1 = 1   ---Invalid SQL statement
        THEN
        SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dene, a.hene,a.saldototal   FROM mccont a  WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
        ELSIF '?cantmeses' = 2 THEN
        SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dene, a.hene,a.dfeb, a.hfeb,a.saldototal   FROM mccont a WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
        ELSIF '?cantmeses' = 3 THEN
        SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dene, a.hene,a.dfeb, a.hfeb,a.dmar, a.hmar,a.saldototal   FROM mccont a WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);

        ELSIF '?cantmeses' = 4 THEN
        SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dene, a.hene,a.dfeb, a.hfeb,a.dmar, a.hmar,a.dabr,
       a.habr,a.saldototal   FROM mccont a WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);

        ELSIF '?cantmeses' = 5 THEN
        SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dene, a.hene,a.dfeb, a.hfeb,a.dmar, a.hmar,a.dabr,
       a.habr,a.dmay, a.hmay,a.saldototal   FROM mccont a WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
      ELSIF '?cantmeses' = 6 THEN
       SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dene, a.hene,a.dfeb, a.hfeb,a.dmar, a.hmar,a.dabr,
       a.habr,a.dmay, a.hmay, a.djun, a.hjun, a.saldototal   FROM mccont a WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
       ELSIF '?cantmeses' = 7 THEN
         SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dene, a.hene,a.dfeb, a.hfeb,a.dmar, a.hmar,a.dabr,
       a.habr,a.dmay, a.hmay, a.djun, a.hjun,a.djul, a.hjul, a.saldototal   FROM mccont a WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
       ELSIF '?cantmeses' = 8 THEN
         SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dene, a.hene,a.dfeb, a.hfeb,a.dmar, a.hmar,a.dabr,
       a.habr,a.dmay, a.hmay, a.djun, a.hjun,a.djul, a.hjul,a.dago, a.hago, a.saldototal   FROM mccont a WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
      ELSIF '?cantmeses' = 9 THEN
        SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dene, a.hene,a.dfeb, a.hfeb,a.dmar, a.hmar,a.dabr,
       a.habr,a.dmay, a.hmay, a.djun, a.hjun,a.djul, a.hjul,a.dago, a.hago,a.dset, a.hset, a.saldototal   FROM mccont a WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
     ELSIF '?cantmeses' = 10 THEN
       SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dene, a.hene,a.dfeb, a.hfeb,a.dmar, a.hmar,a.dabr,
       a.habr,a.dmay, a.hmay, a.djun, a.hjun,a.djul, a.hjul,a.dago, a.hago,a.dset, a.hset,a.doct, a.hoct, a.saldototal   FROM mccont a WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
       ELSIF '?cantmeses' = 11 THEN
          SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dene, a.hene,a.dfeb, a.hfeb,a.dmar, a.hmar,a.dabr,
       a.habr,a.dmay, a.hmay, a.djun, a.hjun,a.djul, a.hjul,a.dago, a.hago,a.dset, a.hset,a.doct, a.hoct,a.dnov, a.hnov, a.saldototal   FROM mccont a WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
       ELSIF  '?cantmeses' = 12 THEN

       SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dene, a.hene,a.dfeb, a.hfeb,a.dmar, a.hmar,a.dabr,
       a.habr,a.dmay, a.hmay, a.djun, a.hjun,a.djul, a.hjul,a.dago, a.hago,a.dset, a.hset,a.doct, a.hoct,a.dnov, a.hnov,a.ddic, a.hdic, a.saldototal   FROM mccont a WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
       END IF;



    --febrero
ELSIF '?mesinicio' = 2 THEN

         IF '?cantmeses' = 1
         THEN 
         SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dfeb, a.hfeb,a.saldototal   FROM mccont a  WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
        ELSIF '?cantmeses' = 2 THEN
        SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dfeb, a.hfeb,a.dmar, a.hmar,a.saldototal   FROM mccont a  WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
        ELSIF '?cantmeses' = 3 THEN
        SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dfeb, a.hfeb,a.dmar, a.hmar, a.dabr,
       a.habr,a.saldototal   FROM mccont a  WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
        ELSIF '?cantmeses' = 4 THEN
          SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dfeb, a.hfeb,a.dmar, a.hmar, a.dabr,
       a.habr,a.dmay, a.hmay,a.saldototal   FROM mccont a  WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
      ELSIF '?cantmeses' = 5 THEN
       SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dfeb, a.hfeb,a.dmar, a.hmar, a.dabr,
       a.habr,a.dmay, a.hmay,a.djun, a.hjun,a.saldototal   FROM mccont a  WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
       ELSIF '?cantmeses' = 6 THEN
        SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dfeb, a.hfeb,a.dmar, a.hmar, a.dabr,
       a.habr,a.dmay, a.hmay,a.djun, a.hjun,a.djul, a.hjul,a.saldototal   FROM mccont a  WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
       ELSIF '?cantmeses' = 7 THEN
        SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dfeb, a.hfeb,a.dmar, a.hmar, a.dabr,
       a.habr,a.dmay, a.hmay,a.djun, a.hjun,a.djul, a.hjul,a.dago, a.hago,a.saldototal   FROM mccont a  WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);

      ELSIF '?cantmeses' = 8 THEN
         SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dfeb, a.hfeb,a.dmar, a.hmar, a.dabr,
       a.habr,a.dmay, a.hmay,a.djun, a.hjun,a.djul, a.hjul,a.dago, a.hago,a.dset, a.hset,a.saldototal   FROM mccont a  WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
      ELSIF '?cantmeses' = 9 THEN
          SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dfeb, a.hfeb,a.dmar, a.hmar, a.dabr,
       a.habr,a.dmay, a.hmay,a.djun, a.hjun,a.djul, a.hjul,a.dago, a.hago,a.dset, a.hset,a.doct, a.hoct,a.saldototal   FROM mccont a  WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
 ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
       ELSIF '?cantmeses' = 10 THEN
        SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dfeb, a.hfeb,a.dmar, a.hmar, a.dabr,
       a.habr,a.dmay, a.hmay,a.djun, a.hjun,a.djul, a.hjul,a.dago, a.hago,a.dset, a.hset,a.doct, a.hoct, a.dnov, a.hnov,a.saldototal   FROM mccont a  WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
 ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
       ELSIF '?cantmeses' = 11 THEN
          SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dfeb, a.hfeb,a.dmar, a.hmar, a.dabr,
       a.habr,a.dmay, a.hmay,a.djun, a.hjun,a.djul, a.hjul,a.dago, a.hago,a.dset, a.hset,a.doct, a.hoct, a.dnov, a.hnov,a.ddic, a.hdic,a.saldototal   FROM mccont a  WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
 ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);
       ELSIF '?cantmeses' = 12 THEN
        SELECT a.ctainterna, a.ejercicio, a.cuenta, a.nombre, a.dapertura, a.hapertura,a.dfeb, a.hfeb,a.dmar, a.hmar, a.dabr,
       a.habr,a.dmay, a.hmay,a.djun, a.hjun,a.djul, a.hjul,a.dago, a.hago,a.dset, a.hset,a.doct, a.hoct, a.dnov, a.hnov,a.ddic, a.hdic,a.dene, a.hene,a.saldototal   FROM mccont a  WHERE a.ejercicio = '?EJ'
 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) 
 ORDER BY DECODE('?O','1',CTAINTERNA), DECODE('?O','2',CUENTA);

       END IF;

       ELSE

      select * from mccont;

 END IF;

Hello guys, im trying to order month columns based on Both parameters 'mesinicio' and 'cantmeses'

'mesinicio' is used for the starting month 'cantmeses' is used for the number of months

I tried to replace the parameter to test if it works and throws me and error in the second "IF" saying "Invalid SQL statement" and nothing else. I cant see what im doing wrong, would appreciate some help. Thanks

Regards

1. IF statement is a feature of procedural programming languages. SQL is a declarative language. Specifically it has no control flow statements - that is the crucial difference between the two classes of languages. 2. Beyond that, if you need more help, tell us what problem you are trying to solve. You didn't tell us what the task is - only a broken query. That's not enough. — , Jun 05 '20 at 14:51

score 2 · Answer 1 · answered Jun 05 '20 at 15:56

In SQL, the IF logic you're looking for is CASE STATEMENT.

I suggest using a CASE statement to create a new derived column called "filter" which identifies each row w/ the conditions you specify. But you only SELECT once, you don't nest SELECT inside of your case statement.

Like this:

SELECT *
,CASE 
   WHEN '?cantmeses' = 1 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) THEN 'Condition 1'
   WHEN '?cantmeses' = 2 AND a.tipocta=DECODE('?CU',1,1,0,0,2,A.TIPOCTA) THEN 'Condition 2'
   ....
   ELSE 'No Condition'
END as "FILTER"
FROM TABLE
WHERE ...
;

score 1 · Answer 2 · answered Jun 05 '20 at 19:33

It looks like your logic can be reduced to

SELECT *
   FROM mccont a
   WHERE a.ejercicio = '?EJ' AND
         a.tipocta=DECODE('?CU',
                            1, 1,
                            0, 0,
                            2, a.TIPOCTA)
   ORDER BY DECODE('?O',
                     '1', CTAINTERNA,
                     '2', CUENTA);

I have a suggestion regarding the design of this table. You've got two fields for each month (e.g. DENE and HENE for January data, DFEB and HFEB for February data, DMAR and HMAR for March data, etc). A better choice would be to have a header table and a separate data for the month data, with each month's data being stored in a separate row - so something like

MCCONT
  ID_MCCONT - primary key, NUMBER
  CTAINTERNA
  EJERCICIO
  CUENTA
  NOMBRE
  DAPERTURA
  HAPERTURA
  SALDOTOTAL

MONTH_DATA
  ID_MONTH_DATA - primary key, NUMBER
  ID_MCCONT - NUMBER, NOT NULL, foreign key to MCCONT
  MONTH_NAME  VARCHAR2(15) NOT NULL
  D_VAL       Holds the data currently in DENE, DFEB, DMAR, etc.
  H_VAL       Holds the data currently in HENE, HFEB, HMAR, etc

This allows you to use a join to put the data together, as in

SELECT *
  FROM MCCONT a
  INNER JOIN MONTH_DATA b
    ON b.ID_MCCONT = a.ID_MCCONT

This might make your task a bit easier.

NESTED IF in Oracle SQL

2 Answers2