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