-1

We have a fairly recent version of a SQL Server that we're using to extract data into a SAP BW Datawarehouse. We're using views to access data in tables on the SQL server. Some of the fields in these tables contain NULL values. These are transferred into SAP as String Value ('NULL') instead of empty, which causes us a major headache.

I understand that we can use COALESCE() in views to replace NULL values with a desired default value ('', 0, '1900-01-01', etc.), however, doing this for each NULL field that we encounter doesn't appear to be very smart.

Is there a better way of addressing this issue short of changing tables to not allow NULL values? Is it possible to include a custom global function that gets automatically applied to all fields fetched in a view without us having to call this function for each field individually?

Jan K.
  • 1,607
  • 2
  • 13
  • 22
  • Why do you want to do this. `NULL` is a value unto itself (just an unknown one). Replaced all your `NULL` values with some other (arbitrary) value seems like a really bad idea. If the column in the **table** shouldn't allow `NULL` values it should be set to `NOT NULL`. If it's because you're query against a `NULL` value, handle your `NULL` values in your queries properly. – Thom A Feb 17 '20 at 09:28
  • I suspect you're using logic like `WHERE MyDateColumn < GETDATE()` and expecting rows where `MyDateColumn` has a value of `NULL`. If so, use `WHERE (MyDateColumn < GETDATE() OR MyDateColumn IS NULL)`. – Thom A Feb 17 '20 at 09:29
  • If the SQL Server tables already contain null values then you won't be able to alter those columns to have a `not null` constraint. Sounds like you need to sort your queries out to use `isnull()` or `coalesce()` ... some things you just can't work around. – AlwaysLearning Feb 17 '20 at 09:35
  • 1
    What I understand here is the code which you are using to extract data is taking NULL value as 'NULL' . So if you can give the details how you are extracting data and loading in HANA then people may be able to help here – Deepak Kumar Feb 17 '20 at 09:57
  • @DeepakKumar Your understanding is correct. We're using SAP BW 7.5 generic extractors through SAP HANA Smart Data Access to connect to the MS SQL Server views. – Jan K. Feb 17 '20 at 13:03
  • @AlwaysLearning Correct. We cannot change the underlying tables or fields. – Jan K. Feb 17 '20 at 13:04
  • @Larnu Simply put: The receiving system cannot handle values such as 'NULL' when we process the field as a numerical value. There are some more reasons but let this suffice. I understand NULL from a regular database point of view, we just can't process those fields correctly on the receiving end. – Jan K. Feb 17 '20 at 13:06
  • Sounds like it's the application that needs fixing then, not the views. – Thom A Feb 17 '20 at 13:19
  • 1
    There is no global toggle, flag or setting that will magically eliminate `NULL`s for you. The closest thing is that `COALESCE(, '')` will "work" (for some values of "work") for almost every type (including `DATETIME`), with the notable exception of `DECIMAL`. You cannot write a function to do this, as functions in T-SQL cannot return different types based on their input. By far the best "fix" is indeed to fix the processing step, if only because ending up with `1900-01-01` dates in your database is typically quite undesirable. – Jeroen Mostert Feb 17 '20 at 13:46
  • @JeroenMostert Thanks! Your answer concludes my question. – Jan K. Feb 17 '20 at 14:14

1 Answers1

0

@Jeroen Mostert's answer in the comments answers my question.

There is no global toggle, flag or setting that will magically eliminate NULLs for you. The closest thing is that COALESCE(, '') will "work" (for some values of "work") for almost every type (including DATETIME), with the notable exception of DECIMAL. You cannot write a function to do this, as functions in T-SQL cannot return different types based on their input. By far the best "fix" is indeed to fix the processing step, if only because ending up with 1900-01-01 dates in your database is typically quite undesirable.

Therefore, the only options are

  1. go through each field that can potentially hold a NULL value and cleanse it within the view
  2. handle NULL values on the receiving end (e.g. SAP BW); this could be done through a generic function to be placed in the entry layer's start routine or it could be done manually.

Disallowing NULL values on table level in the source system is in this case not feasible as we cannot change the application that writes to the tables (third party vendor ERP).

Jan K.
  • 1,607
  • 2
  • 13
  • 22