title: Nested select in create table query
body: <p>I want to write create table statement with query with nested select in if construction to compare with number constant. So that the code is readable.</p>
  
  <pre><code>create table transaction_client stored
      as
  select
      if(c.record_id = '01',
         'Handle',
         c.record_name) as type,
      if(c.record_id = '02',
         cast(t.amount as string),
         '')            as value,
      if(c.record_id = '02',
         c.record_name,
         '')            as attribute
  from transaction  t
  join       client c
             on c.id = t.client_id;
  </code></pre>
  
  <p>I tried to use the with structure and select code from table, but it does not allow the use of different values in rows.</p>
  
  <pre><code>create table classifier as
  select
      stack(5,
            '01', 'CODE_01', 'TRANSACTION_TYPE',
            '02', 'CODE_02', 'TRANSACTION_TYPE',
            '03', 'CODE_03', 'TRANSACTION_TYPE'
          ) as (code, description, classifier_name)
  from any_table;
  
  create table transaction_client stored
      as orc as
  with
      cl as (select code as code from classifier where classifier_name = 'TRANSACTION_TYPE' and description = 'CODE_01')
  select
      if(c.record_id = cl.code,
         'Handle',
         c.record_name) as type,
      if(c.record_id = cl.code,
         cast(t.amount as string),
         '')            as value,
      if(c.record_id = cl.code,
         c.record_name,
         '')            as attribute
  from transaction  t
  join       client c
             on c.id = t.client_id
  cross join t;
  </code></pre>
  
  <p>I also tried using a map, but the query execution speed is doubled.</p>
  
  <pre><code>set hivevar:classifier=map('01', 'CODE_01',
                             '02', 'CODE_02', 
                             '03', 'CODE_03')
  
  ....
   if(c.record_id = ${classifier}['CODE_01'],
         'Handle',
         c.record_name) as type,
  ...
  </code></pre>
  
  <p>Please tell me which structure is better to avoid magic number and make code more readable?</p>