Questions tagged [null]

A database concept used to represent missing, unknown or inapplicable data.

Filter by
Sorted by
Tagged with
1
vote
1answer
996 views

Transpose Rows to column

Can anyone Please help me to transpose below result +------+------------+-------+ | SVR | dates | count | +------+------------+-------+ | SRV1 | 03/25/2018 | 1 | | SRV1 | 04/06/2018 | 59 ...
0
votes
2answers
2k views

COALESCE with cast

I'm pulling data from a jsonb column in Postgres, and casting it to an integer to compare it against a count. The problem is that in some cases the data apparently can't be cast to integer and I get: ...
0
votes
3answers
108 views

How to show a column as NULL if it doesn't exist in match?

I have a query as shown below. In this query I am trying to get the result of orders which don't have messages (which I thought as null). But I was expecting that this query would produce a NULL in ...
0
votes
1answer
107 views

How to get 0 in the null value fields

select * from ( select type,SUM(ISNULL(amount,0)) AS amount,month(date) as date from tbl_cashmaster where date between '2016/12/31' and '2017/12/31' group by type,month(date) ) src ...
-1
votes
2answers
122 views

Sorting a Date Column in Microsoft SQL Server Management Studio

I have a table (master) with the following columns: Order # (integer) Location (varchar) Est Start Date (date) I want to return all rows. I want the rows sorted by Est Start Date, in ascending order,...
0
votes
1answer
677 views

Keep default value as 0.0 for 0 or NULL for numeric column

I have a numeric column Number(28,3) in which I have to insert value as 0.0 , whenever I encounter 0 or NULL/blank at the time of data insertion. I have tried below : NVL(col_name,0.0) but it's ...
4
votes
2answers
1k views

SQL Server: specifying a unique optional column

SQL Server has a quirk with optional unique columns: you cannot have more than one null in a table. For example: create table employees ( id int identity(1,1) primary key, givenname nvarchar(...
2
votes
0answers
129 views

Inserting two NULLs values into an UNIQUE column in SQLite

I use: sqlite-jdbc-3.21.0.jar Error received: SQLite_CONSTRAINT_UNIQUE] A UNIQUE constraint failed: companies.ncf I ask a few things to the user, there is two fields that are optional to complete ...
6
votes
1answer
211 views

Join on NULL Key Column Optimizing as Table and Index Scans

I have a question about this query plan. We have a table in a test environment, Order_Details_Taxes, that has 11,225,799 rows. This table has a column, OrdTax_PLTax_LoadDtl_Key, which is NULL on ...
1
vote
1answer
91 views

Firebird: table and null data

I'm working with Firebird database. I have a big table with many thousand of rows. Each line records the status of an object at regular intervals for many objects. I need to register new properties ...
3
votes
1answer
148 views

Why does PostgreSQL create an additional NULL bitmap if all fields after the 8th are NOT NULL?

I don't refer to the 1 Byte null bitmap included in the row header that could be used to flag null values for any of the first 8 columns. See: https://stackoverflow.com/questions/12145772/do-...
1
vote
1answer
572 views

How to handle null value number DataWarehouse [duplicate]

I have a question. I'm developing a Data Warehouse, I retrieve the data from my reconciled level, this in order to keep updated my Data Warehouse, but i am not sure how to handle some numeric fields ...
1
vote
3answers
254 views

Array to string sometimes concatenates fewer values

array_to_string(array_agg(array_to_string(array[ highlights,exceptions,inter_note,inter_status::text ,comp_note,comp_status::text,hard_note,hard_status::text] ,'|#|'::...
4
votes
1answer
34 views

Why does null and true evaluate to null but null or true evaluates to true?

Why does null and true evaluate to null in PostgreSQL but null or true evaluates to true? I would have thought using null with and or or would've evaluated to null in both cases?
1
vote
1answer
197 views

Count where any 3 columns have values (not null)

I have a table by name ItemProperties with 15 columns. Columns names are like Feature1, Feature2, Color, Length, Width, Height etc... It has 10K+ rows. I need counts from this table where any 3 ...
5
votes
2answers
111 views

Replacing NULL values with one string from provided list

I am trying to replace NULL values in table by using SELECT with one (randomized?) string from the set that I provide to the compiler. Example: |id | date | +---+------+ | 1 | 2017 | | 2 | NULL |...
1
vote
1answer
62 views

Having two entities that are the same except for 1 distinct attribute

In an example scenario for a database design assignment, a company provides hotel rooms (split into two entities - Hotel and Room) and apartments. So the database will contain the following entities: ...
3
votes
1answer
100 views

SSIS / SSAS: Handling Age Columns

I have some columns representing Age in a table, taking values from 0 to 100, but also -1 for 'Missing Value'. I don't want to store them as strings to be able to find average age etc., but SSAS turns ...
3
votes
1answer
756 views

SSIS - SSAS: Missing data handling

I have a solution in SSDT with a SSIS and a SSAS project, with missing values in both numeric and text columns. Is it a good practice converting all the missing values of these columns to NULL during ...
5
votes
2answers
168 views

How to capture missing information in SQL Server tables

I have a few tables with nullable columns in a SQL Server database. Those nullable attributes are either not supplied at the time of row creation or they are not applicable in some cases. I realized ...
0
votes
1answer
241 views

MsMerge_genhistory has alot of rows with pubid = null

I have a merge replication and I am worried that the cleanup of metadata might not be enough. I have a retention period of 60 days and I can see thet the metadatacleanup-job do remove rows i ...
0
votes
1answer
3k views

Concatenate with NULL values ( Should not replace NULL )

After some time searching for an answer, I got a LOT of question saying to use coalesce,concat(), and etc, but what I need is: Table: create table nulos ( nome varchar(max)) alter table nulos add ...
3
votes
1answer
6k views

How do I select arrays that are not empty?

Why is this so tricky, what is token set to that it isn't equal to null nor an empty string? SELECT lexemes FROM ts_debug('This is a title') WHERE alias = 'asciiword'; lexemes --------- {} {} {}...
8
votes
1answer
174 views

Can't SQL Server store NULLs in fixed-length columns?

I came across this statement in the official Oracle documentation: In Microsoft SQL Server, only columns with variable-length data types can store NULL values. When you create a column that ...
-1
votes
1answer
278 views

Combining two tables with keys that contain null

I have two tables that I am joining |id |profit| | 1 | 1234 | | 2 | 1345 | | 4 | 1454 | | 5 | 1254 | and another table | id | x loss | y loss | | 2 | 34312 | 4354 | | 3 | 35614 | 4365 | | 4 ...
5
votes
2answers
1k views

CHECK constraint for array column to verify length > 0

I'm playing with postgres table validation rules and trying to set a CHECK constraint for an array column. An idea is to allow only arrays with length > 0. Here is how I want to implement it: create ...
3
votes
1answer
158 views

Why does NULL ordering impact a btree scan in PostgreSQL?

In this answer jjanes says that NULL ORDERING contrary to the index declaration ordering can force a quick scan. Turns out he's right. I want to know why that is though. If you a btree with nulls last,...
1
vote
1answer
49 views

Any potential complications from ms sql backup db to disk = 'nul', disk = 'nul', …?

Our development team has a tool that notifies us of issues with our DB backups, but they have not yet given us the ability to exclude certain databases from the alerts - DBs that we do not want ...
-4
votes
4answers
34k views

Using IS NULL in CASE Expression in WHERE Clause [closed]

I have a WHERE clause that I want to use a CASE expression in. However, my CASE expression needs to check if a field IS NULL. If the @UserRole variable value = 'Analyst', then the ...
3
votes
3answers
89 views

How to normalize a table with common and non-common fields?

and thanks in advance. I am trying to normalize a table "People", where each entry (person) belong to one of many different categories (e.g. "lawyer", "client", "judge"). Some fields are common ...
0
votes
1answer
2k views

Can't write DBNull to SQL in Powershell

In Powershell, I have some data that I am importing from a .csv, and some data points aren't always entered so I need to replace them with [DBNull]::Value. The problem is that when I go to use Write-...
1
vote
1answer
487 views

left joining and combining the results where they are not null

I'm given a MySQL database with tables like this: show create table c; CREATE TABLE `c` ( `id` int(11) NOT NULL AUTO_INCREMENT, -- and other columns PRIMARY KEY (`id`) ) show create table c_r; ...
1
vote
1answer
798 views

Left (or Right) Join and Null value return

I have a query where I'm joining 2 tables with a LEFT JOIN. To simplify the example, let's say it's fetching cars and their options. There are 31 possible options and the output should include all ...
0
votes
1answer
345 views

MySQL Insert NULL Inserts 0

I am using MySQL and PhpMyAdmin, I have a table named items - the structure is as follows; +------+---------+------+---------+ | name | type | Null | Default | +------+---------+------+---------+ |...
3
votes
1answer
145 views

Can an Oracle IDENTITY column be nullable?

I want to create an ID column that automatically generates unique ID numbers by default. Users won't be inserting rows via INSERT statements, rather, they'll create rows using an out-of-box ...
1
vote
1answer
60 views

Write join query without any relationship among columns in more than 2 tables. Here i am using 4 tables for join [closed]

I create a store procedure for join 4 tables result in rows and join tables but I don't want to null values from top i.e I want to display in rows by value. StoredProcedure [dbo].[...
0
votes
1answer
2k views

Why doesn't FIND_IN_SET return on NULL values

Can anybody explain why FIND_IN_SET isn't returning the row with the NULL value? To me it seems obvious, the string "test", isn't part of NULL, so it should return it? http://sqlfiddle.com/#!9/389043/...
0
votes
1answer
46 views

Are null values allowed in any column of a table in SQL?

I have doubts over null values.Therefore can you please explain me briefly what is a null value in sql and can we use null values at any place in sql tables?
1
vote
1answer
77 views

Normalization issue [closed]

I want to normalize a database to third normal form, but I do not know how to deal with start and end dates, knowing that some events can start and end the same day. Should I repeat the dates or allow ...
0
votes
1answer
44 views

Indexed view - time of update

A 3rd part app is using 'our' database views. Basically flow looks like this: New Row is inserted to Results table; Insert new row to Event table (with information about new result). The 3rd part is ...
1
vote
1answer
37 views

Calculate not-nulls in group by

I have a TRAILS table: +----+------------+-----------------+ | ID | LENGTH | CONDITION (1-5) | +----+------------+-----------------+ | 1 | 23.1 | null | | 2 | 41.7 | 1 ...
2
votes
2answers
633 views

In what case is a count(x or null) needed in Gaps and Islands?

In this answer, Erwin Brandstetter says: count(step OR NULL) OVER (ORDER BY date) is the shortest syntax that also works in Postgres 9.3 or older. count() only counts non-null values. In modern ...
4
votes
1answer
743 views

Why Is Null Ignored In Query Results

I have a table with similar structure to the below, and a field has a NULL value in it. When I query the table this record is not returned, it is omitted. How should the query be altered so that ...
13
votes
6answers
9k views

Why can't I use null values in joins?

I have solved the query problem by using ...row_number() over (partition by... this is a more general question on why we cannot use columns with null values in joins. Why can't a null be equal to a ...
0
votes
1answer
130 views

Empty result set when matching NULL?

If I run a statement such as SELECT * FROM stuff WHERE something=NULL I will get an empty result set. I know that the solution is to use something IS NULL. I also understand why: SQL interprets NULL ...
2
votes
3answers
50 views

Counting NULLs as zeroes and filtering with the WHERE statement

I have a SQL puzzle: How can we get a list of customers who have purchased 3 or fewer socks (including those who haven't purchased any socks) as well how many socks those customers have purchased? I ...
-1
votes
1answer
222 views

Will removing NTEXT columns improve performance and reduce space used?

I have some questions regarding the (still deprecated) (N)TEXT datatype in Sql Server - in many tables I can find (N)TEXT columns and many (90%+) rows where those values are NULL, so their use case ...
2
votes
1answer
507 views

Nullable columns or jsonb for storing user profile?

I'm deciding between using nullable fields or jsonb to store user profile. Initially, this would be used for contacts: email and phone. I anticipate that there may be other columns added later, such ...
0
votes
1answer
4k views

How to set a NOT NULL column into NULL in PostgreSQL?

I'm new to PostgreSQL and that is why I'm asking this question. I have a table app_client_users and a column named client_id. Initially I set it as NOT NULL and now I would like to change it to allow ...
2
votes
4answers
543 views

Computed field when NULL

I have two fields in my table : A int, B int. I want to add two computed fields (CF1 & CF2) that uses these two fields A & B. CF1 = (case when [A]>[B] then (CF1=3) else case when [A]<[B]...