Tagged Questions

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

learn more… | top users | synonyms

1
vote
1answer
53 views

Populate one table with data from another one on selected cells

I have a problem with filling empty cells in one table with data from another one. I got a table with user emails - some of the fields are empty, some are already containing emails. I gathered the ...
0
votes
1answer
44 views

If I assign a NVL constraint to a nullable column will it display an error or it will retrieve the records?

I have a database in which some tables have to be joined to fetch the data. The problem is that in one table the column is nullable (nullable is set to yes or no constraint given for that column) and ...
1
vote
1answer
63 views

What relevance does the table-level ANSI_NULL setting have?

Apparently, SQL Server stores the current ANSI_NULL setting when creating a table (see related questions here and here). However, when I execute SELECT * FROM myVeryOldTable WHERE ...
0
votes
0answers
40 views

Oracle 12c, exclude rows wher two columns are both null

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit I am trying to select rows with a non-null value in one of two columns. Data is being selected from a view (MY_VIEW) with both ...
2
votes
2answers
273 views

How do I include nulls during comparisons in SQL Server?

So basically in SQL Server a NULL means there is no value and thus can't be compared which returns some unexpected results. For example, the following query doesn't return rows where value IS NULL ...
1
vote
0answers
21 views

How do I include nulls during comparisons in SQLServer? [duplicate]

So basically in SQLServer a NULL means there is no value and thus can't be compared which returns some unexpected results. For example, the following query doesn't return rows where value IS NULL but ...
-1
votes
1answer
53 views

Inserting null value without quotes postgresql

I'm creating a php application which relies on postgresql. Now i have this query: $query= 'INSERT INTO "users" ("name", "username", "email", "phone") VALUES (\''. $name .'\', \''. $username .'\', ...
0
votes
1answer
92 views

Return a default value in group by if selected column is null

I am trying to run a group by statement which works fine however, one of the "group" is null. How do I return "Unknown" for that row? For example: 10000 A 9999 B 9990 C 4 etc. First ...
0
votes
0answers
26 views

Import CSV script executes and returns NULL on localhost

I have been running this import script on localhost to import CSV fields to my database. The script executes and returns the rows as NULL and no data is inserted. It would be great to get another set ...
1
vote
1answer
29 views

cant order NULL value properly (mysql)

So I got this view in mysql : CREATE VIEW vote_question_totals AS SELECT question_id , SUM((vote_question_type)='1') question_total_plus , SUM((vote_question_type)='-1') question_total_minus , ...
0
votes
0answers
16 views

Avoid obvious potential NULL-values [duplicate]

I've been searching through the different threads discussing NULL values and how it is good whether using it or not. I cant find any concrete examples of how to design a relational data model to avoid ...
1
vote
1answer
76 views

How can I insert records 'into null' in Oracle?

I'm looking for a way to insert records into a 'black hole'; i.e. execute a query for a certain table successfully, but make the records disappear. Something like INSERT INTO NULL, if you will. MySQL ...
1
vote
1answer
127 views

Alternatives to NULL for missing data [duplicate]

I am using PostgreSQL and trying to write value constraints. Is it possible to record missing data as distinct from NULL values in a numeric field without using an arbitrary numeric value? For many ...
2
votes
2answers
389 views

Returning empty string when string_agg has no records

I am trying to return a text field in a PostgreSQL query that is of the form 'stringOne' || string_agg(field, ',') || 'stringTwo' where for certain elements in the group clause, field is always ...
0
votes
1answer
167 views

TSQL change column constraint between null and not null

I am doing a exercise to change a column's constraint between null to not null. Table name: Production.categoriesTest. Please review my question and give me suggestion on what I did wrong and how to ...
2
votes
1answer
4k views

“Cannot insert the value NULL”, but there is no NULL values! [closed]

This question follows the answer of my other question there: Insert with OUTPUT correlated to sub query table The Person.LastName column has a NOT NULL constraint. When I execute this code: CREATE ...
1
vote
1answer
120 views

Database Normalization: A Case Of The NULLs [duplicate]

I'm trying to learn about proper database management and am having a hard time with designing a database that doesn't rely on NULLs. If you think I'm crazy for embarking on this, it is because of SQL ...
0
votes
1answer
195 views

sql-server optimize query with lots of null checks

I have a query such as: select something from somewhere join stuff on condition join more_stuff on condition where somewhere.sometime is null and somewhere.someone is null and stuff.whatever is null ...
1
vote
1answer
326 views

How different clauses treat Null/Unknow in SQL Server 2012?

(relatively new to SQL and SQL Server) I am reading SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan. Autor states that: SQL has different treatments for UNKNOWN in different language ...
10
votes
3answers
428 views

Can IS DISTINCT FROM be combined with ANY or ALL somehow?

Is a postgres way of combining IS DISTINCT FROM with ANY or some other neat way of getting the same result? select count(*) from (select 'A' foo union all select 'Z' union all select null) z where ...
0
votes
1answer
351 views

MySQL warnings while loading integer data into table (SOLVED)

For some reason, after loading a large amount of data into the following table (from a single file), all of the values register as 0. CREATE TABLE secondTable ( freq int NULL, w1 int NULL, w2 int ...
0
votes
0answers
45 views

MySQL - query join returning zero recs from table with data

I've provided a file that has the table structure and some sample data that I'm working with. I don't understand why I'm getting nulls from a joined table, where there is data. Here's my query ...
1
vote
3answers
2k views

Select query to retrieve values even if are null

I need some help in a query. Need to retrieve data from 1 table, even if one of the field has null value. Here's an example: Select name, SUM(credit) as credit From expenses Where name like ...
0
votes
2answers
100 views

Accept null value on parent column in multipart relationship

EDIT: I've come up with one poor solution (below) that requires some denormalization, but does avoid the risks of UDFs in CHECK constraints. I would appreciate your help if you have a better answer. ...
1
vote
0answers
61 views

How to merge rows based on how much data they have?

I am cleaning data and I formed a table something like this: file | product | color | product_id --------------------------------------------- one.csv pipe blue null ...
1
vote
0answers
48 views

Dealing with null values in non-nullable columns in a NoSQL database

If I have an existing table in a NoSQL database cluster and I want to add a new column that should never return null, how do I handle it in my application code? I can update all exist records and ...
1
vote
1answer
527 views

How to update where subquery value is null in SQL Server

I am attempting to update a column if another value from a subquery is less than a certain value or null: UPDATE Customer SET PriceClassID = 'A' FROM (SELECT custid, SUM(curybaldue) as last_sum FROM ...
1
vote
3answers
4k views

Oracle empty string converts to null

When doing an insert, an empty string is converted to null: insert into test (f) values (''); Now, there is a row with f containing a null. But, when I query the table, I cannot use '': select * ...
4
votes
3answers
4k views

Quickly change NULL column to NOT NULL

I have a table with millions of rows and a column that allows NULL values. However no row currently has a NULL value for that column (I can verify this fairly quickly with a query). However when I ...
2
votes
1answer
620 views

Is there a way to explicitely have a not-null column in a view

If I create a view in Oracle, the column definition's not null characteristic is inherited from the base table: create table tq84_t ( col_1 number, col_2 varchar2(15) not null ); create view ...
1
vote
4answers
581 views

MySql Order by isnull() Performance Problem

my sql below is use for listing stock added from 10 days ago. Order by isnull(Price) is use so that stock without any price yet will still being listed. AddDate and Price has an index. SELECT Id, ...
0
votes
2answers
327 views

Two nullable columns one required to have value

No-Explanation Question: Is there anyway to have a constrain of 2 null values that always requires 1 to have value ? For example two date columns both null but having at least 1 that requires to have ...
2
votes
3answers
9k views

Getting SELECT to return a constant value even if zero rows match

Consider this select statement: SELECT *, 1 AS query_id FROM players WHERE username='foobar'; It returns the column query_id with value 1 along with a player's other columns. How would ...
0
votes
1answer
219 views

Creating a partial unique constraint for MySQL

I have the same question as asked in a previous post: PostgreSQL multi-column unique constraint and NULL values. But the solution there is not applicable as I am using MySQL instead of PostgreSQL. My ...
0
votes
3answers
2k views

Easier way to handle so many isnull() situation

Is there any good way to avoid writing so many times isnull() function inside sproc ? I have stored procedure that use almost 30 times isnull() function, I think that I am miss a concept , but until I ...
0
votes
1answer
3k views

WITH ROLLUP WHERE x IS NULL

I tried to find this question somewhere else on here, and found a couple that were similar to it -- but all the answers seemed pretty convoluted, and I think it was surprising to me because I just ...
0
votes
1answer
271 views

Conditional Where Statement Involving a NULL value?

Trying to find a way to pass off a third value within a 'bit' field. I would like to be able to utilize NULL as a searchable value. As in 'True', 'False', or 'NULL', the only issue is I'm unsure how ...
2
votes
1answer
536 views

Update “NULL” string to Actual NULL value

I have a table that contains NULL values but the problem is that some of the values are actually string "NULL" and not actual NULLS so when you trying something like where date is null it will not ...
0
votes
1answer
287 views

H2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or not

I would like to check if a column has a NOT NULL constraint in order to start an automated upgrade of the table definition. However in H2 the SQL SELECT NULLABLE FROM TABLE_NAME doesn't return me the ...
1
vote
2answers
277 views

Expand sparse table with self outer join on distinct values

I have this: g1 | g2 | x ---------+-----------+----- New York | Monday | 210 New York | Tuesday | 258 Chicago | Monday | 30 Chicago | Wednesday | 25 LA | Thursday | 40 ...
0
votes
2answers
129 views

oracle null value in quotes - yet another newbie question

I have a where clause like "and uc.completion_status in ('[P....]') " here ('[P....]') is the user input from taken a dropdown list in the user interface. and dropdown list consists three options ...
1
vote
2answers
1k views

Only one NULL for one part of primary key

I have two date columns : "start date" and "finish date". I want to make sure that there can be only one null value in the "finish date" column (which should be current state if it's NULL) for one ...
3
votes
1answer
577 views

A function to check if a column allows NULL

Is there a way to write an insert/update query that checks if a column allows NULLs: If it does set the column to NULL, and '' (empty string) otherwise? I would be something like: UPDATE mytable ...
3
votes
2answers
16k views

Select column names whose entries are not null

I would like to have a list of those columns of a table that have at least one not-NULL data entries in them. In other words, I would like to get the column names for which the following returns at ...
3
votes
3answers
1k views

Empty LONGTEXT or NULL?

In my MySQL DB I have one field called html_contents that contains all the html to be shown in a webpage. Obviously the html could be huge, and certaintly bigger than 64KB, therefore I decided to use ...
1
vote
1answer
1k views

Query performance of a NULL vs an empty ('') varchar in SQL Server 2012

I have 3 indexed fields in a query: int, int, and varchar(250). The query performs well when all 3 conditions are specified with real values. The int columns always have values, but there are plenty ...
0
votes
1answer
843 views

Find ID columns with null value in oracle database

I need a way to iterate through all tables in tablespace "T_ECOS" and find all ID with null value in oracle database Any suggestion?
2
votes
1answer
293 views

Null Values and Performance

I always try to build the database avoiding any NULL values in my tables, and I have successfully had done this so far. (Hey, I am very new to database design.) But still there are times when saving ...
16
votes
3answers
2k views

Why does ANSI SQL define SUM(no rows) as NULL?

The ANSI SQL standard defines (chapter 6.5, set function specification) the following behaviour for aggregate functions on empty result sets: COUNT(...) = 0 AVG(...) = NULL MIN(...) = NULL MAX(...) = ...
1
vote
1answer
691 views

How to change the display of an empty string in mysql command line tool?

I'm maintaining a database with InnoDB tables. These tables have some columns of type (from show create table): `val0` varchar(30) default NULL, `val1` varchar(30) default NULL, etc... From the ...