SQL

LPAD(<value>,<no>,<char>)

Left fill <value> with <char> until <no> length

concat(<value1>,<value2>)

Concatenate 2 strings

The example shows how subquery results can be handled as a table, aliasing of field and tablenames, a case statement and inner joins. Example query

select int_id,
       'fieldspec1' as Alias,
       (Case 
         When table1.field1 > 1 And table2.field1 = 'VALUE' Then x
         When table1.field1 > 2 And table2.field1 = 'VALUE' Then y
         Else z
       End) As CaseResult,
       subquery.field as VALUE_SUBQUERY
  from 
   (Select distinct fielsspec,
     from table1 tabalias
     Inner Join table2 on table2field = tabalias.field
     Inner Join table3 on table3field = table2field
      And table3filed = value 
     Where (field1 > value And (field2 = 1 And field3 = 'value'))
      or (field > value )
   ) as subquery
   Inner Join jointable1 On jointable1.field = subquery.field 
    Where subquery.field <> jointable1.field