PL/SQL Trick For PL/SQL DECODE To Lingual Conversion

 I've recently been exploring parts and bits of Lingual, another Concurrent Inc. effort to ease the transition from SQL/Relational Databases to Hadoop. Lingual is a library that converts ANSI SQL-99 code directly to MapReduce jobs, to run on a cluster. Specifically, I've been exploring the use of Lingual to generate Cascading flows.

Lingual supports these ANSI SQL commands in the text query that you provide it. So basically, if your query code contains anything outside of these functions, it will not run with Lingual.

This is a short post on a trick that helps convert a very standard DECODE operation that is often used in PL/SQL code.

I had the following instance of code:

 AND (                (ecd.code_a <> 'US'
                       AND (ecd.code_b =   

                                        DECODE (fas.code_b,
                              
          'INTL', ecd.code_b,
                              
          'ALL', ecd.code_b,
                              
          fas.code_b
                              
         )
                            )
                       )
                    OR (ecd.code_a = 'US'
                        AND (ecd.code_b =
                              
  DECODE (fas.code_b,
                              
          'US', ecd.code_b,
                              
          'ALL', ecd.code_b,
                              
          fas.code_b
                              
         )
                            )
                       )
     )
So we have a pretty mid-level complicated query to convert into Lingual supportable format. One thing to observe here, is that the DECODE is, in essence, just like a set of SWITCH...CASE...DEFAULT statements clubbed together.  A clean way, to then convert it into Lingual, is by doing something like so:

AND (
                        (ecd.code_a <> 'US'
                         AND (fas.code_b IN ('INTL', 'ALL') OR
                                  ecd.
code_b = fas.code_b)
                             )
           OR
                        (ecd.
code_a = 'US'
                         AND (fas.
code_b IN ('US', 'ALL') OR
                                   ecd.
code_b = fas.code_b)
                        )
    )


It is important to remember, that any type of DECODE statement can thus be converted into a set of AND/OR conditions thereby emulating each of the cases within the DECODE. For me, this was the difference between my decision to proceed with Lingual or not!

Thanks, I hope this helped! Happy coding!

No comments:

Post a Comment