| 12345678910111213141516171819202122232425262728 |
- select distinct case
- when T1."ACCT_NR" in (
- '8310',
- '8300',
- '8301',
- '8311',
- '7310',
- '7300',
- '7301',
- '7800'
- ) then { fn CONCAT(
- { fn CONCAT(
- { fn RTRIM(
- case
- when { fn LEFT(T1."ACCT_NR", 1) } in ('7', '8')
- and case
- when { fn LEFT(T1."ACCT_NR", 1) } in ('4', '7', '8', '5') then { fn CONCAT(
- { fn CONCAT({ fn LEFT(T1."ACCT_NR", 4) }, '_') },
- case
- when T2."DEPARTMENT" = ' ' then '00'
- else T2."DEPARTMENT"
- end
- ) }
- else T1."ACCT_NR"
- end like '%\_%' escape '\' then {fn CONCAT({fn CONCAT({fn RTRIM(case when {fn LEFT(T1."ACCT_NR",1)} in (' 4 ', ' 7 ', ' 8 ', ' 5 ') then {fn CONCAT({fn CONCAT({fn LEFT(T1."ACCT_NR",4)},' _ ')},case when T2."DEPARTMENT" = ' ' then ' 00 ' else T2."DEPARTMENT" end )} else T1."ACCT_NR" end )},' _ ')},{fn RTRIM(case when T2."CLIENT_DB" in (' deop01 ', ' deop02 ') and case when (T2."DESTINATION" is null or T2."DESTINATION" = ' ') then ' 00 ' else T2."DESTINATION" end = ' 50 ' then ' 30 ' when T2."CLIENT_DB" in (' deop01 ', ' deop02 ') and case when (T2."DESTINATION" is null or T2."DESTINATION" = ' ') then ' 00 ' else T2."DESTINATION" end in (' 90 ', ' 91 ') then ' 50 ' else case when T2."DESTINATION" is null or T2."DESTINATION" = ' ' then ' 00 ' else T2."DESTINATION" end end )})} else case when {fn LEFT(T1."ACCT_NR",1)} in (' 4 ', ' 7 ', ' 8 ', ' 5 ') then {fn CONCAT({fn CONCAT({fn LEFT(T1."ACCT_NR",4)},' _ ')},case when T2."DEPARTMENT" = ' ' then ' 00 ' else T2."DEPARTMENT" end )} else T1."ACCT_NR" end end )},' _ ')},T2."PRODUCT_GROUP")} else case when {fn LEFT(T1."ACCT_NR",1)} in (' 7 ', ' 8 ') and case when {fn LEFT(T1."ACCT_NR",1)} in (' 4 ', ' 7 ', ' 8 ', ' 5 ') then {fn CONCAT({fn CONCAT({fn LEFT(T1."ACCT_NR",4)},' _ ')},case when T2."DEPARTMENT" = ' ' then ' 00 ' else T2."DEPARTMENT" end )} else T1."ACCT_NR" end like ' % \ _ %' escape ' \ ' then {fn CONCAT({fn CONCAT({fn RTRIM(case when {fn LEFT(T1."ACCT_NR",1)} in (' 4 ', ' 7 ', ' 8 ', ' 5 ') then {fn CONCAT({fn CONCAT({fn LEFT(T1."ACCT_NR",4)},' _ ')},case when T2."DEPARTMENT" = ' ' then ' 00 ' else T2."DEPARTMENT" end )} else T1."ACCT_NR" end )},' _ ')},{fn RTRIM(case when T2."CLIENT_DB" in (' deop01 ', ' deop02 ') and case when (T2."DESTINATION" is null or T2."DESTINATION" = ' ') then ' 00 ' else T2."DESTINATION" end = ' 50 ' then ' 30 ' when T2."CLIENT_DB" in (' deop01 ', ' deop02 ') and case when (T2."DESTINATION" is null or T2."DESTINATION" = ' ') then ' 00 ' else T2."DESTINATION" end in (' 90 ', ' 91 ') then ' 50 ' else case when T2."DESTINATION" is null or T2."DESTINATION" = ' ' then ' 00 ' else T2."DESTINATION" end end )})} else case when {fn LEFT(T1."ACCT_NR",1)} in (' 4 ', ' 7 ', ' 8 ', ' 5 ') then {fn CONCAT({fn CONCAT({fn LEFT(T1."ACCT_NR",4)},' _ ')},case when T2."DEPARTMENT" = ' ' then ' 00 ' else T2."DEPARTMENT" end )} else T1."ACCT_NR" end end end "c1" , T1."LEDGER_ACCTS_NAME" "c2" , T1."TYPE_ACCTT" "c3"
- from "OPTIMA"."import"."ACCOUNT_INFO" T1, "OPTIMA"."import"."ACCT_DOC_KEY" T2
- where T1."ACCT_NR" = T2."ACCT_NO" and T1."CLIENT_DB" = T2."CLIENT_DB" and T1."TYPE_ACCTT" = '2'
- order by 1 asc
|
