Handle Null value in SQL Statement

[christophe_averous]

Hi all,

In SAP when you write the query:  Select codebars from oitm where codebars=''

SAP retrieve all the record included the null value

When you do the same in SQL, SQL retrieve only the record where codebars='' and not the record where codebars is NULL.

If you want to have the same result you have to write: Select isnull(codebars,'') as 'codebars from oitm where isnull(codebars,'')=''

Is there a solution to have the same behavior in SQL then in SAP.

I try SET ANSI_NULLS ON on top of my query but without success.

Thanks for your help

Regards

Christophe'

[pedro_magueija · Accepted solution]

Hi Christophe,

You can use the function isnull(field, '') which will transform the the field into what you place in the second parameter.

e.g. Select codebars from oitm where ISNULL(codebars,'') = '' will return all the values '' and NULL.

Hope it helps.

Best regards,

Pedro Magueija

[christophe_averous · Accepted solution]

Hi Pedro,

Thanks for your reply.

I agree with what your said and it was the solution that I use as explain in my initial post:

If you want to have the same result you have to write: Select isnull(codebars,'') as 'codebars from oitm where isnull(codebars,'')=''

I was looking for a SQL statement or a SQL tunning which avoid the use of isnull(,). Its seems that it is not possible.

Regards

Christophe

[former_member201110]

Hi Christophe,

You have to use the syntax 'is null' or 'is not null' when filtering for null values:

select codebars from OITM where codebars = '' or codebars is null

SBO will automatically convert nulls to empty strings (as does the DI API) but your data results will return lines where codebars is empty or null.

Kind Regards,

Owen