You can write SQL in a variety of places when designing an interface or a procedure with ODI, but a lot of times we forget to take care of somebody. This somebody, is the next person. This person can be anybody, you, friend, colleague or your boss. I had to return and edit my own SQL codes and other’s more than once. I wish that we had some standards from the very beginning. In this post, I will try to keep 3 tips for ODI SQL standards simple enough, to be able to follow them with no real hassle, but also to benefit very greatly for the next time you look at your own code.

Lets start with a test case. Mapping a field in a interface, which will execute the following logic:

  1. If the field is null, insert ‘unknown’.
  2. Replace values of ‘bad’, ‘wrong’ and ’empty’ with ‘unknown-replaced’
  3. Select a replacement for the string from a table.
  4. If no replacement is found, enter the value as is.

Sounds simple enough, lets give it a try with mapping that SQL into an interface column. This is how it looks like without applying the tips on the SQL. I will focus only on the SQL of that specific column and the general SQL generated when executing the interface as an example for my point. Here is the code for the logic above:

case when mytable.myfield is null then ‘unknown’ when mytable.myfield in (‘bad’,’wrong’,’empty’) then ‘unknown-replaced’ else nvl((select reps.replacement from myschema.replacetable reps where reps.origval = mytable.myfield), mytable.myfield) end

Now, lets apply some tips.

1. Line breaks – Just by a simple line break in the right place and the SQL code becomes a lot clearer:

case
when mytable.myfield is null
then ‘unknown’
when mytable.myfield in (‘bad’,’wrong’,’empty’)
then ‘unknown-replaced’
else
nvl(
(
select reps.replacement
from myschema.replacetable reps
where reps.origval =
mytable.myfield
),
mytable.myfield)
end

2. Tabs – A few tabs here and there, will make the code much more readable:

case
when mytable.myfield is null
          then ‘unknown’
     when mytable.myfield in (‘bad’,’wrong’,’empty’)
          then ‘unknown-replaced’
     else
          nvl(
                    (
                         select reps.replacement
                         from myschema.replacetable reps
                         where reps.origval = mytable.myfield
                    ),
          mytable.myfield)
end

 

3. Capitalize the fields, catalogs, schemas, tables and aliases (just like ODI does) – This will make it easy to distinguish where an actual resource is being referenced and where it is pure SQL:

case
when MYTABLE.MYFIELD is null
          then ‘unknown’
     when MYTABLE.MYFIELD in (‘bad’,’wrong’,’empty’)
          then ‘unknown-replaced’
     else
          nvl(
                    (
                         select REPS.REPLACEMENT
                         from MYSCHEMA.REPLACETABLE REPS
                         where REPS.ORIGVAL = MYTABLE.MYFIELD
                    ),
          MYTABLE.MYFIELD)
end
This was a (really) simple test case. Those tips, should make much easier reading the code after a while, especially, when the SQL Logic is complicated. I hope by reading this post, you will make yours and somebody’s else life a little easier in the future.
You are welcome to share any of your tips for ODI SQL Standards in the comments, thanks.