To homepage To homepage
Solved

SQL Case When Statement not working correctly with TimeXtender

  • 7 April 2023
  • 2 replies
  • 197 views
K
Rank
Userlevel 2

In my DSA I have my Business Partner Master Data. I want to add a field that named Intercompany that shows ‘Intercompany’ when BP Group Code = 104 or 109 and else show Extern. BP Group Code has a smallint datatype. I added a custom field with the CASE WHEN statement. However, the extra field does not show the right results.

When executing the CASE statements SMSS everything works fine, but when I implement it in my DSA, it does not work correctly.

I tried all kinds of CASE statements with e.g. Like,OR statements, strings, however it does not show the desired result, but rather tons of nulls.

The screenshot below shows the intercompany field with the Nulls and some tests CASE statements.

 

 

 

icon

Best answer by rory.smith 7 April 2023, 11:43

View original

2 replies

rory.smith
Rank
Userlevel 5
Badge +7

When you build your custom transformation, are you using parameters and are these mapped to the correct type of table (i.e. raw vs valid)?
I would not implement this in a CASE statement but use a set of fixed transformations with conditions:

  1. Fixed ‘Extern’
  2. Fixed ‘Intercompany’ , condition: BP Group Code In list 104,109

This will first set Extern on all records and then set Intercompany only for those that meet the condition. If you are applying this to an incremental table you will need a Full reload to populate the field for older records.

Note that the elements in a list should not have a space: 104,109 instead of 104, 109

K
Rank
Userlevel 2

Hi @rory.smith,

I think it indeed had to do with the incremental settings! Also transformed it to the fixed transformations with conditions as you mentioned. All working now! :)

Is this normally best practice over CASE statements?

 

Reply


Terms and ConditionsCookie settings