Question about Rendering with NULLs #34

Activity

1. osalvador commented on Mar 10, 2019

   

   osalvador

   on Mar 10, 2019

   Owner

   More actions

   Hi @xendren ,

   This template:

   <% for c1 in (select null as a_varchar_col from dual) loop %>          
      INSERT INTO my_table (process_id, a_varchar_col)
           VALUES (1,'<%= NVL(c1.a_varchar_col, null) %>');
   <% end loop; %> 

   Generate this output:

      INSERT INTO my_table (process_id, a_varchar_col)
           VALUES (1,'');
   

   And this other template:

   <% for c1 in (select 'Something' as a_varchar_col from dual) loop %>          
      INSERT INTO my_table (process_id, a_varchar_col)
           VALUES (1,'<%= NVL(c1.a_varchar_col, null) %>');
   <% end loop; %> 

   output this

      INSERT INTO my_table (process_id, a_varchar_col)
           VALUES (1,'Something');
   

   I see everything right. What are you trying to do exactly?
2. MikeKutz commented on Mar 10, 2019

   

   MikeKutz

   on Mar 10, 2019

   Collaborator

   More actions

   @xendren

   For Oracle, NULL and '' are the same thing.

   If you want the word NULL, then you need to do a CASE statement. IF ... END IF doesn't work within <%= .. %> tags.

   Example template:

   <% for c1 in (select NULL as a_varchar_col 
                                from dual
                                union all
                                select 'Something' from dual) loop %>          
      INSERT INTO my_table (process_id, a_varchar_col)
           VALUES (1, <%= case
                              when c1.a_varchar_col is not null
                              then
                                '''' || c1.a_varchar_col || ''''
                              else 'NULL'
                            end %>);
          <% end loop; %>

   Produces output:

      INSERT INTO my_table (process_id, a_varchar_col)
           VALUES (1, NULL);
      INSERT INTO my_table (process_id, a_varchar_col)
           VALUES (1, 'Something');
   

   👍React with 👍1Reacted by Philipp Salvisberg
3. xendren commented on Mar 10, 2019

   

   xendren

   on Mar 10, 2019

   Author

   More actions

   Thank you both for the quick responses. Oracle does treat '' the same as NULL for VARCHAR, but not for CHAR, so our coding standards prohibit the use of '' for NULL. My apologies, I should have stated that in the initial post.

   Mike solved the problem for me. I had tried the case statement inside the expression clause, but it looks like I did not have the correct number of single-quotes to make it work. Using Mike's example with 4 single-quotes surrounding the value did work as expected.

   Thanks again for the help.
4. xendren

   closed this as completedon Mar 10, 2019