title: Oracle SQL CASE statement pseudo column
body: <p>There are 2 tables - orders &amp; config_check.</p>
  <pre><code>create table orders (order_created date,
                       cus_no varchar2(20),
                       order_num number,
                       pay_type varchar2(30),
                       MFC date,
                       prod_no varchar2(15) );
                       
  create table config_check (check_type varchar2(100),
                       field_name varchar2(10),
                       field_value varchar2(15),
                       start_date date,
                       end_date date
                      );
                      
  insert into orders values ('20-NOV-21', 'GT-19Y67', 489, 'Credit Card', NULL, '1000');
  insert into orders values ('07-OCT-21', 'NU-20D73', 567, 'Cash on Delivery', '14-OCT-21', '1001');
  insert into orders values ('17-NOV-21', 'JP-16V81', 789, 'Cash on Delivery', NULL, '1101');
  insert into orders values ('20-NOV-21', 'DZ-17T92', 837, 'Net Banking', '23-NOV-21', '1002');
  insert into orders values ('16-SEP-21', 'RJ-18W107', 124, 'Cash on Delivery', NULL, '1002');
  insert into orders values ('18-NOV-21', 'KI-19A39', 638, 'Cash on Delivery', NULL, '1000');
  insert into orders values ('19-NOV-21', 'FT-20U86', 347, 'Net Banking', NULL, '1002');
  insert into orders values ('19-NOV-21', 'FT-20U86', 347, 'Net Banking', NULL, '1110');
  
  
  
  insert into config_check VALUES ('Invalid Orders Check', 'PROD_NO', '1000', '16-NOV-21', '30-NOV-21' );
  insert into config_check VALUES ('Invalid Orders Check', 'PROD_NO', '1101', '08-SEP-21', '21-SEP-21' );
  insert into config_check VALUES ('Invalid Orders Check', 'PROD_NO', '1002', '16-NOV-21', '30-NOV-21' );
  insert into config_check VALUES ('Cust Bday Offer', 'CUST_NO', '845796', '13-AUG-21', '23-AUG-21' );
  
  commit;
  </code></pre>
  <p>Task is to find cus_no, order_num where the order is invalid in one of the 3 conditions :</p>
  <ol>
  <li>pay_type is 'Cash on Delivery' - when this happens then a pseudo column named comments should have the message 'This order does not qualify for CoD'</li>
  <li>column MFC is not null - when this happens then a pseudo column named comments should have the message 'This order can not be an MFC'</li>
  <li>when an additional product is added which is not listed in config_check table. This one needs some explanation. So I will try my best with the above table examples to explain in detail. In config_check table there are 2 PROD_NOs (1000 and 1002) for CHECK_TYPE 'Invalid Orders Check' which started on 16-Nov-21. Today's date is 21-NOV-2021. So only those 2 rows can be considered where today's date lies between start and end date. Now order number 347 has a product number 1110 which is not in config_check table. In such a case the pseudo column named comments should have the message 'Non listed items can not be ordered'.</li>
  </ol>
  <p>I have got the 1st 2 conditions but have not been able to get the 3rd one. Here is my attempt at the query :</p>
  <pre><code>select o.CUS_NO, o.ORDER_NUM,
         CASE 
              WHEN o.pay_type = 'Cash on Delivery' THEN 'This order does not qualify for Cod'
              WHEN o.MFC IS NOT NULL THEN 'This order can not be an MFC'
         END comments
         from orders o INNER JOIN 
  ( select * from config_check where check_type = 'Invalid Orders Check' and sysdate between start_date and end_date) c
  ON (o.prod_no = c.field_value)
  where o.ORDER_CREATED &gt; c.start_date
    AND (o.pay_type = 'Cash on Delivery' OR o.MFC IS NOT NULL);
  </code></pre>
  <p>The above query gives the result as :</p>
  <ol>
  <li>DZ-17T92    837 This order can not be an MFC</li>
  <li>KI-19A39    638 This order does not qualify for Cod</li>
  </ol>
  <p><a href="https://i.stack.imgur.com/LIUGs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LIUGs.png" alt="enter image description here" /></a></p>
  <p>The Case should have another WHEN clause for the 3rd condition and the same should be in the bracketed AND clause at the last. I cannot get the 3rd condition in the CASE block. Can someone please help me get the desired result? The desired output should be</p>
  <ol>
  <li>DZ-17T92    837 This order can not be an MFC</li>
  <li>KI-19A39    638 This order does not qualify for Cod</li>
  <li>FT-20U86    347 Non listed items can not be ordered</li>
  </ol>
  <p><a href="https://i.stack.imgur.com/WkYlB.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WkYlB.png" alt="enter image description here" /></a></p>
  <p>Looking for help or even pointers will be great. Thank You for reading such a long post.</p>
  <p>Edit : I am having trouble pasting the query output in table format. I will share the output in jpeg.</p>