asked on

Null values in SQLgot a message from someone and wanted to see what the experts think

I asked an expert who has gone offline for the weekend where to find a field his response was * " Null in the database." I replied and he said if the database value for the field is empty (Null) I assume he means to assign the field the NULL value in the record. I have not done that does anyone have an example of how to do that. Is it.

,* NULL AS {field}
or
, NULL AS {field}

or something like that.

Then gave me this code:
Select isnull({field}, 'DBName') AS FIELD, Count (0)
From TABLE T
Where T.Date between '20200801' and '20200830'

The results were:

FIELD. (no Column Name)
abcd 120
efg 3405
wxyz 198
DBName 309218

What did I miss here and can someone clarify what you think he meant?
I am rusty on the function of things as many know here. Any thoughts always appreciated...?

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RUA Volunteer2?

ASKER

When I look at the code I was working off of in order to add That new FIELD to a report which the DBName seems to be counting. I run the query and the results show in that table the word NULL already in the records.

I am not sure if he meant if a user enters no value to make sure it is converted to NULL
or
if he means set the field if it is empty to NULL incase the system does not LOG an entry into the Database as null.

When I run the actual dataset for the report with new FIELD entered over 100k rows show the word NULL. No other value present? Do you think he is trying to tell me to remove all nulls or make sure they are accounted for?

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RUA Volunteer2?

ASKER

I did run that and the field (column) is blank. According to the manager there should be values present. Like DBNames
Is there a way if I post data here can it be made private so it is only visible to EE members?
Not sure how to do that.

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RUA Volunteer2?

ASKER

Here is what I gathered from an evening (an hour) with an EE member. the DBName represent the backend of the application the DB runs inside of. The DBName is a place holder since the application doe not send the record for row. I can only place a null value when a user starts using the application. The statement Select isnull({field}, 'DBName') AS FIELD, Count (0) simply applies a value in the null records so that the viewers can see that name of the system which holds the records. That was my whole problem. I did not interpret the requestors question or it was not given clearly to me. Thank you for all your help.

RUA Volunteer2?

ASKER

So if I drop in a field to the query that already has other fields from the table joined to another table in the QRY then it sounds like he just wanted me to omit any nulls in the field.

I say that because again when I drop in the field all the records in that field that show up at the same row level as the are NULLS and the customer said there should definitely be record values there not nulls in the report.

Sound like a One To Many relationship in these other tables that display the record and some are NULLS and the true record associated with the data they need to see is farther down the list....? Does that sound like sense to you guys.

Pavel Celba

It looks like you would need LEFT JOIN in your query but your description is rather unclear.

Sample query and data would show much more than the description.
So please provide some sample data (a few rows), the query if you have one, and expected results.

It will also be better to ask a new question.