With Clause using a IF Clause in Oracle SQL possible?-CodePudding

I have a table with values:

CREATE TABLE school (
    classroom varchar(125),
    girls int,
    boys int,
    sum_class int
);

INSERT INTO school
    (classroom, girls, boys, sum_class)
    values('1a',4,10,14);

INSERT INTO school
    (classroom, girls, boys, sum_class)
    values('1b',11,19,30);

INSERT INTO school
    (classroom, girls, boys, sum_class)
    values('2a',12,13,25);

INSERT INTO school
    (classroom, girls, boys, sum_class)
    values('2b',10,9,19);

At a later point the table school is filled with further entries, automatically (I don't write the exact command for the creation of the table; it should be solved in the SQL query). Because the entry of the classroom is known but not filled in the table at the moment, I write the following SQL query:

With exact_class AS (
 SELECT '2c' AS classroom, 0 AS girls, 0 AS boys, 0 AS sum_class FROM dual
  UNION
 SELECT '2d' AS classroom, 0 AS girls, 0 AS boys, 0 AS sum_class FROM dual
)

SELECT classroom, girls, boys, sum_class 
FROM school
UNION
SELECT * FROM exact_class

For bridging this query is helpful until the new values of the classroom '2c' and/or '2d' is/are coming. For example:

(classroom, girls, boys, sum_class)
    values('2c',6,14,20);

Now, the SQL query show me the classroom '2c' two times in the table (next to the other values):

'2c',6,14,20
'2c',0,0,0

Of course, I need only the correct row and not both of them and here begins my problem:

Is it possible to switch in the SQL query with an IF-Clause? In one case it shows me the 0-values, when there is no entry. In the other case it shows the new values of the classroom.

Of course, I tried it intuitively with an IF-Clause but I got errors. However, I think the syntax also doesn't make sense.

Is there a 'simple' solution for this SQL query? Or must I write the query in another way to get a solution? If yes, how?

Thanks in advance.

CodePudding user response：

One way would be to use UNION ALL with the table and the default values and then to aggregate:

SELECT classroom,
       MAX(girls) AS girls,
       MAX(boys) AS boys,
       MAX(sum_class) AS sum_class
FROM   (
  SELECT classroom, girls, boys, sum_class FROM exact_class UNION ALL
  SELECT '2c', 0, 0, 0 FROM dual UNION ALL
  SELECT '2d', 0, 0, 0 FROM dual
)
GROUP BY classroom

CodePudding user response：

You can just put a condition on the unioned query.

    SELECT 
        CLASSROOM, GIRLS, BOYS, SUM_CLASS 
    FROM 
        SCHOOL
UNION ALL
    Select 
            CLASSROOM, GIRLS, BOYS, SUM_CLASS
    From  
        (SELECT * FROM exact_class) ec
    Where NOT EXISTS (Select * From SCHOOL Where CLASSROOM = ec.CLASSROOM And SUM_CLASS > 0)

The NOT EXISTS query is checking if there is a row for the same classroom with some boys/girls - if there is no such row then the one with zeros will be created. If such row exists then it was already selected by the first query and the one with zeros will not be.

/*  Result with initial data
CLASSROOM         GIRLS       BOYS  SUM_CLASS
----------- ---------- ---------- ----------
1a                   4         10         14 
1b                  11         19         30 
2a                  12         13         25 
2b                  10          9         19 
2c                   0          0          0 
2d                   0          0          0
*/


/*  Result after inserted row for 2c (6, 14, 20)
CLASSROOM         GIRLS       BOYS  SUM_CLASS
----------- ---------- ---------- ----------
1a                   4         10         14 
1b                  11         19         30 
2a                  12         13         25 
2b                  10          9         19 
2c                   6         14         20 
2d                   0          0          0
*/