SQL COALESCE Function

Coalesce returns the first non-null value from a series of expressions.

COALESCE Function

                            .-------------------.      
                            V                   |      
|--COALESCE--(--expression----+---------------+-+--)------------|
                              '-,--expression-'        


The COALESCE function evaluates its parameters in order and returns the first one that is not NULL. The result is NULL if, and only if, all the arguments are NULL. The parameters can be of any scalar type, but they do not have to be all of the same type. COALESCE may be used to provide a default value for a field, which might not accept a null character.

Coalesce in Case

         Exec SQL
           Insert Into CP1012Wk
             Select Case When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '20' )
                          Then '10'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '21' )
                          Then '11'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '24' )
                          Then '14'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '26' )
                          Then '66'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '40' )
                          Then '30'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '41' )
                          Then '31'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '42' )
                          Then '32'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '43' )
                          Then '33'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '44' )
                          Then '34'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '47' )
                          Then '37'
                       Else Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' )
                    End As Store,
                    IfNull( c.CuNo, '' ) As Customer,
                    Sum( Case When ( a.Period = Month( Current Date )
                                And Substr( b.El2, 1, 2 ) = '41' )
                              Then -ValueDoc
                              Else 0
                         End ) As MTD_41,
                    Sum( Case When ( a.Period = Month( Current Date )
                                And Substr( b.El2, 1, 2 ) = '42' )
                              Then -ValueDoc
                              Else 0
                         End ) As MTD_42,
                    Sum( Case When ( a.Period = Month( Current Date )
                                And Substr( b.El2, 1, 2 ) = '43' )
                              Then -ValueDoc
                              Else 0
                         End ) As MTD_43,
                    Sum( Case When( a.Yr = Year( Current Date )
                                And Substr( b.El2, 1, 2 ) = '41' )
                              Then -ValueDoc
                              Else 0
                         End ) As YTD_41,
                    Sum( Case When( a.Yr = Year( Current Date )
                                And Substr( b.El2, 1, 2 ) = '42' )
                              Then -ValueDoc
                              Else 0
                         End ) As YTD_42,
                    Sum( Case When( a.Yr = Year( Current Date )
                                And Substr( b.El2, 1, 2 ) = '43' )
                              Then -ValueDoc
                              Else 0
                         End ) As YTD_43,
                    Sum( Case When( a.Yr = Year( Current Date ) )
                              Then -ValueDoc
                              Else 0
                         End ) As YTD
                From coda12tst/OAS_DocHead a
                  Join coda12tst/OAS_DocLine b
                    On ( a.DocCode, a.DocNum ) = ( b.DocCode, b.DocNum )
                  Left Join LibD42/CIPName0 c
                    On Substr( b.Ref2, 1, 6 ) = c.CuNo
                  Left Join TTLib/TTablM d
                    On b.El3 = d.TblKy
                      And d.TblTp = 'REGON'
                  Left Join LibD42/EMPInvH0 e
                    On Substr( a.Descr, 1, 11 ) = e.IvNo1
                Where Substr( b.El2, 1, 2 ) In ( '41', '42', '43' )
                  And a.Yr = :Accounting_Period.Year
                  And a.Period <= :Accounting_Period.Period
                Group By Case When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '20' )
                          Then '10'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '21' )
                          Then '11'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '24' )
                          Then '14'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '26' )
                          Then '66'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '40' )
                          Then '30'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '41' )
                          Then '31'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '42' )
                          Then '32'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '43' )
                          Then '33'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '44' )
                          Then '34'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '47' )
                          Then '37'
                       Else Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' )
                     End,
                     c.Cuno
               Order By Case When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '20' )
                          Then '10'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '21' )
                          Then '11'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '24' )
                          Then '14'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '26' )
                          Then '66'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '40' )
                          Then '30'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '41' )
                          Then '31'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '42' )
                          Then '32'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '43' )
                          Then '33'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '44' )
                          Then '34'
                          When (
                       Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' ) = '47' )
                          Then '37'
                       Else Coalesce( e.StN1, Substr( b.El3, 1, 2 ), '' )
                     End,
                     c.Cuno
                ;

         If SQLStt <> SQLStsOK
           And SQLStt <> SQLStsEOF;
             Write_To_Job_Log( 'CP1012WK - SQLCOD = ' + %Char( SQLCod )
                             + ' ' +  %TrimR( SQLErM ) );
         EndIf;

         Exec SQL
           Get Diagnostics :Count = Row_Count;

         Write_To_Job_Log( %Char( Count ) + ' Records Added To CP1012WK' );