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' );