Determining Null Values In Columns

Sep 7, 2004

How to find out that there is a null value in a column rather than a valid integer, DateTime or bool value, for strings I use 'as' operator to cast the column value and it returns null when column value is null, but for value types using 'as' operator causes compile error and using simple casting causes runtime error, for example:








int count = (int)row["Count"];





and





int count = row["Count"] as int;








the first one throws an exception when Count is null and the second doesn't compile at all since 'as' applies to reference types, so what is the way other than exception handling to determine null value in a column?

View 6 Replies


ADVERTISEMENT

Determining What Columns Are All Null

Jul 6, 2006

Does anyone have a suggestion for how to efficiently determine what columns in a table are NULL for all rows? - Shean

View 5 Replies View Related

Divide Two Columns With Null Values

Aug 14, 2014

I am trying to divide these two columns...

parsename('$'+ Convert(varchar,Convert(money,Q.[Product Cost]-R.[Shared Cost]),1),2) as [Pre Cost],
parsename('$'+ Convert(varchar,Convert(money,R.[New Product Cost]-R.[Shared Cost]),1),2) as [Post Cost]

Formula:
[Post Cost] / [Pre Cost]

Expected:

If [Post Cost] is null then I would need to assign 1 and divide by [Pre Cost] and similarly if [Pre Cost] is null then I would need to assign 1 so that I can divide [Post Cost] / [Pre Cost]

View 1 Replies View Related

Count Of Null Values In Columns

Mar 16, 2006

Hi,

I would like to ask how can I get count of null values in each column together?


I know I can use COUNT(*) - COUNT(<col>), but I need to explicitly name every column in the query.... is it possible without it?

View 9 Replies View Related

Comparing 2 Columns Containing Null Values

Sep 28, 2006

Hi All.

     I'm having some issues with what seems to be a simple update statement but is giving me grief when one or both of the columns I'm comparing are null. My statement (simplified) is as follows:-

 

UPDATE
 TAB_A
SET
 TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
 TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
 TADS.DBO.UNCLBRAMDEPT TAB_A
ON
 TAB_B.BRANCODE = TAB_A.BRANCODE
AND
 TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
 TAB_B.TRADCODE <> TAB_A.TRADCODE
)

 

If either of the TRADCODE fields (or both) are null then the comparison fails to return the row to update. I've tried setting the ANSI_NULLS setting to off, this has no effect, presumably because it will only work when comparing a column to a variable or evaluating if the column is null itself.

I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.

 

I'd be grateful for any pointers!

 

Thanks in advance

 

View 4 Replies View Related

Adding Column With Null Values Between Two Columns

Mar 22, 2012

If i have a table with Col1,Col2,Col4, and Col5, how can I create and add a Col3 with null values? The format would be varchar.

View 13 Replies View Related

SELECT With NULL Values In One Or More Of Selected Columns

Apr 10, 2008

Hello everyone,

I have a little problem here. I need to select data from multiple columns and multiple tables, some of those columns can have NULL value. I have tried few things, and havent find the right way so far. Problem that i am incountering is that when i run SELECT statment as it is right now, i dont get back results which match search parameters but have NULL value in one or more column in select statment. How can i fix this problem?


SELECT Person2role.person2roleID,Person.firstname, Person.lastname, Person.sex

FROM Person LEFT OUTER JOIN

Person2Role ON Person.personID = Person2Role.personID

WHERE (Person.firstname LIKE '%' + ISNULL(@firstname + '%', ''))

AND (Person.lastname LIKE '%' + ISNULL(@lastname + '%', ''))

AND (Person.sex = ISNULL(@sex, Person.sex))

This is a part of SELECT statment..just so you can get the idea of what i am doing..i have few more parameters


I am working with SQL Server 2005 Express Version

Thank you,

Alex

View 10 Replies View Related

Inserting Empty Values Into NOT NULL Columns Via ODBC

Jul 20, 2005

We are writing a C application that is using ODBC to insert recordsinto a database. We have a NOT NULL column that can legitimately havean empty value, i.e. we know the value and it is empty (i.e. a zerolength string).We are using SQLBindParameter() to bind a variable to theparameterized insert statement <<in the form: INSERT INTO table VALUES(?, ?, ?)>>. We are using SQLExecDirect() to process the SQL.We are running into the problem where ODBC is converts the empty (zerolength) string into a NULL value and this errors due to the fact thatthe column is defined as NOT NULL.We do not want to redefine the column as NULL, becasue myunderstanding of the correct usage of a NULL column is to indicatethat a value is unknown or meaningless... in our case we know thevalue (it is empty) and an empty value has meaning within ourapplication.I'm sure that this issue has been seen and address thousands(millions?) of times... any guidance would be appreciated.

View 2 Replies View Related

Compressing Multiple Rows With Null Values To One Row With Out Null Values After A Pivot Transform

Jan 25, 2008

I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?

-- Ryan

View 7 Replies View Related

Determining A Range Of Values

Mar 29, 2012

suppose you have a large table with 2 columns

create table tick
(
ID bigint identity (1,1) primary key not null
, price money not null
)

and I want to know 3 things

Starting with ID = 1 through ID = (last)
give me the low and high price (that satisfies the below WHERE clause), and the last ID
WHERE high price - low price = 0.10
and the last ID (last) is the minimum ID to satisfy: high price - low price = 0.10

So the last ID will coincide with the record containing either the low or high price, the problem is you don't know which record in that range has the corresponding high/low price, it could be the first record or the 10,000th record.

I am thinking I need to create two summary tables, maybe calculate the min(ID) that goes down 0.01 then the min(ID) that goes down 0.02, etc...
Then calculate the min(ID) that goes up 0.01 then up 0.02, etc..finally join against these two summary tables to figure out which combination of downSummary and upSummary have a difference of 0.10.

View 2 Replies View Related

Determining Default Values Automatically

Oct 4, 2007

I am building code to look into the database and create "CRUD" stored procedure scripts automatically for each table.
I am in the finishing stages as my procedures take into consideration all necessary parameters and keys wheter they are natural or identity. It also takes into consideration User Defined Types.

I am working on the section which defines the parameters and their default values. ie.
CREATE PROCEDURE [dbo].[TransactionHistoryInsertOne]
(
@ProductID Int = 0,
@ReferenceOrderID Int = 0,
@ReferenceOrderLineID Int = 0,
@TransactionDate DateTime = '1/1/1900', <-- Possibly arbitrary
@TransactionType nchar(1) = '?' <---- How to get this.
@Quantity Int = 0,
@ActualCost Money = 0,
@ModifiedDate DateTime = '1/1/1900'
)
AS...

I have three problems remaining.
1. Using the AdventureWorks database as a data model, I run into the TransactionType field in the TransactionHistory table, a non-nullable nchar(1). How do I determine and use any default values that may be defined for this object?
2. Do UDTs have default values already defined?
3. How do I make sure I am not arbitrarily setting the default values?

Here i my select statement into the system tables:

SELECT C.*, T.name as xtypename, U.name as xutypename, S.*, so.*
FROM syscolumns C

left join systypes T on (C.xtype = T.xtype) and (T.xtype = T.xusertype)
left join systypes U on C.xusertype = U.xusertype
left join sys.all_columns S on (C.id = S.object_id) and (C.name = S.name)
left join sysobjects so on S.default_object_id = so.id
WHERE (C.id = [TableId])";


Thanks for any help

View 1 Replies View Related

Using A Excel Source To Get The Data From An Excel File Gets Null Values For A Couple Columns

Nov 19, 2007

I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.

Thanks,
Manisha

View 4 Replies View Related

NULL Values Returned When Reading Values From A Text File Using Data Reader.

Feb 23, 2007

I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?

View 12 Replies View Related

Integration Services :: SSIS Reads Nvarchar Values As Null When Excel Column Includes Decimal And String Values

Dec 9, 2013

I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?

CREATE TABLE [dbo].[Import_CustomerSales](
 [CustomerId] [nvarchar](50) NULL,
 [CustomeName] [nvarchar](50) NULL,
 [CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]

View 5 Replies View Related

Null Values For Datetime Values Converted To '0001-01-01'

Mar 29, 2006

Hi

can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.




If Not Row.Datum1_IsNull Then

Row.OutputDatum1 = Row.Datum1

Else

Row.OutputDatum1 = CDate(System.Convert.DBNull)

End If



Any help welcome.

View 1 Replies View Related

Use Order By But Want NULL Values As High Values

Nov 9, 2000

Hi,

My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list

e.g.

Rank Blah Blah
1 - -
2 - -
3 - -
NULL - -
NULL - -

At present the NULLs are at the top of the list, but I do not want my ranking in descending order. Any suggestions?

Thanks
Dan

View 1 Replies View Related

Adding Null And Non-null Columns Together ----??

Nov 27, 2000

I have two columns A (which allows nulls) and B( which does not allow nulls).
How can I add the contents of columns A and B SO THAT I DO NOT GET A NULL RESULT WHEN A IS NULL.

The result of A+B concatanation will be stored in a column, C.


Appreciate your help
Ziggy

View 10 Replies View Related

Reporting Services :: SSRS - Get Common Values Between Two Columns Where Values Sorted Comma Separated

May 6, 2015

I have a situation in SSRS to get the common values between the two columns where the values are sorted comma separated as below.Ex:

ColumnA :  abc,cde,efg    
ColumnB : cde,xyz,abc    

the result in    

ColumnC : cde,abc

similarly Column A and B will have n number records. I need to right an expression or the Code function to get the required result in ColumnC. I am using SharePoint Lists as Datasource. Cannot write SQL query to achieve this requirement.

View 5 Replies View Related

SQL 2012 :: Generating Date Range Values (start / End Dates) From Month Columns With Boolean Values

Jan 13, 2015

I've got some records like this:

ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0

where each month field has a 0 or 1, depending on if the person was enrolled that month.

I'm being asked to generate a table like this:

ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014

Is there some slam dunk way to do this without a bunch of If/Then statements?

The editor compressed all my space fields, so the column headers are off in some places.

View 8 Replies View Related

T-SQL (SS2K8) :: Converting Row Values To Columns With Dynamic Columns

Jun 11, 2015

Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.

Sample Data:

;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'

[Code] ....

The results from the above are as follows:

columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL

My desired results with desired headers are as follows:

PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30

View 3 Replies View Related

OLE DB Source To Flat File Destintation Using Fixed Width Columns - Determining Source Column Width

Feb 13, 2007

Hi,

I am trying to create a program that transfers tables to flat files.
At this point in time, I have suceeded in created one that creates delimited files.

However, I am now trying to create fixed-width files as you can do with the SSIS designer, but programatically.

Is there a way to programatically determine the width of a column from the source table? I can not seem to find any kind of function or member that stores this information or allows me to retrieve it.

I know what I need to change in order to set a width for a column, but I just don't know how to find the width without just asking the user to provide one.

View 5 Replies View Related

Lookup To Check Values In 13 Columns With Same Values

Mar 22, 2006

hi,

it is my first post on this forum, please be patient if i miss any important bit of information.

i am transporting data from a legacy system into mssql 2k5 using SSIS.
among those column of a dataset there are 13 columns, all necessary for operational reasons, that i need to ensure data consistance.

i believe i could do this check using the lookup data flow item, but surely there must be a way to do it in a more streamlined fashion.

since column names contain numbers to distinguish the version, eg; col01, col02, col03 .. col13.

i thought i could include the lookup within a loop and use a couple of variables to do this trick, but since i have not done it before i am asking for some sort of guidance from a guru among you folks.


please let me know if further clarification is necessary.

regards,

nicolas

View 5 Replies View Related

Integration Services :: SSIS Package - Replacing Null Values In One Column With Values From Another Column

Sep 3, 2015

I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.

What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column. 

I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far. 

View 3 Replies View Related

Null Columns

Jun 14, 2001

ColumnA has existing data in it some of the data are Nulls
what would be the best way to have new as well as existing
data change to 0, using a constraint?

View 1 Replies View Related

Columns [not Null]

Oct 15, 2006

hi, I have a problem with a flat file and a table

I have a flat file with 5 columns and in the table I have 30 columns and they all are (not null)
I cannot leave other columns in target for which it throws mistake, have to insert something, since as I insert information in the demas columns???

Since I can solve this problem???




helps please

View 3 Replies View Related

Using NOT IN With Null Values

May 19, 2008

Hi, I ve a table,  I want to fetch certain rows based on the value of a Column. That column is nullable, and contains NULL values.I used the following query,SELECT Col_A FROM TABLE1 WHERE SOME_ID = 1317 AND Col_B NOT IN (8,9) Here,  Col_B contains NULL values too. I need to fetch all rows where Col_B is not 8 or 9.Now, if I use "NOT IN", it does not work. I tried reading on it and got to know why it does not work. Even "NOT EXISTS" does not help. But still I've to fetch my values. How do I do that?Thanks & Regards,Jahanzeb        

View 6 Replies View Related

SQL Null Values In VB

Apr 10, 2006

I have tried doing a search, as I figured this would be a common problem, but I wasn't able to find anything.  I know that my SP is functional because when I use VWD execute the query outside of the webpage, I get the correct results -however I have to ensure that a field is either entered, or set to <NULL>.  In my SET's I want it to use the wildcards.
What I want is to do a search (plenty of existing topics on that, however none were of help to me).  If a field is entered, then it is included in the search.  Otherwise it should be ignored.  In my VB I have the standard stored procedure call, passing in values to all of the parameters in the stored proc below:
CREATE PROCEDURE dbo.SearchDog@tagnum int,@ownername varchar(50), @mailaddress varchar(50),@address2 varchar(50),@city varchar(50),@telephone varchar(50),@doggender varchar(50),@dogbreed varchar(50),@dogage varchar(50),@dogcolour varchar(50),@dogname varchar(50),@applicationdate varchar(50)AS IF @tagnum=-1  SET @tagnum=NULL  SET @ownername = '%'+@ownername+'%'  SET @mailaddress = '%'+@mailaddress+'%'  SET @address2='%'+@address2+'%'  SET @city = '%'+@city+'%'  SET @telephone='%'+@telephone+'%'  SET @dogcolour='%'+@dogcolour+'%'  SET @dogbreed='%'+@dogbreed+'%'  SET @dogage='%'+@dogage+'%'  SET @doggender='%'+@doggender+'%'  SET @dogname='%'+@dogname+'%'  SET @applicationdate='%'+@applicationdate+'%'
 SELECT DISTINCT  * FROM DogRegistry WHERE (  TagNum = @tagnum OR  OwnerName LIKE @ownername OR  MailAddress LIKE @mailaddress OR  Address2 LIKE @address2 OR  City LIKE @city OR  Telephone LIKE @telephone OR  DogGender LIKE @doggender OR  DogBreed LIKE @dogbreed OR  DogAge LIKE @dogage OR  DogColour LIKE @dogcolour OR  DogName LIKE @dogname OR  ApplicationDate LIKE @applicationdate  ) AND TagNum > 0GO
I don't know why it is creating links inside my SP -ignore them.  TagNum is the primary key, if that makes a difference.
On the webpage, it ONLY works when every field has been filled (and then it will only return 1 row, as it should, given the data entered).  Debugging has shown that when nothing is entered it passes "".
Any ideas?

View 9 Replies View Related

Null Values

Jun 29, 2000

I am trying to retrieve data from two different tables. One of the tables has more than 20 columns some of which are null. I would like to retrieve data from both tables excluding the columns which have null values. How do I do this?

View 3 Replies View Related

SUM When There Are Null Values.

Nov 2, 2007

Would this take care of null values in either a.asset or b.asset?

SELECT convert(decimal(15,1),(sum(isnull(a.asset,0))/1000.0)+(sum(isnull(b.asset,0))/1000.0)) as total_assets

What's throwing me off is that there are multiple a.asset or b.asset for each unique ID. It seems to work, but I'm not following the logic too well. If I were doing this in another language, I would loop through, summing a.asset and b.asset wherever it's not null for each unique ID.

View 8 Replies View Related

Null Values

Jan 16, 2006

Hi,

How can I use "Derived Column" to check if a Datetime value is null or not and if null to insert  00/00/00 instead. ?

The background being that while using a "Derived Column" to change a Column from a (DT_DATE) to a (DT_DBTIMESTAMP) everytime I get a null value it see's it as a error.

And the column in particular has ~ 37 K blank / null fields so Im getting a lot of errors

So far I have tried to use something like

ISNULL([Column 34])

Or

SELECT ISNULL(ID, '00/00/0000') FROM [Column 34]

Or


SELECT ISNULL(au_id, '00/00/0000 00:00') AS ssn
FROM [Column 34

but none seems to work [Column 34] being the offending column.

What a normally use is just a simple "(DT_DBTIMESTAMP)[Column 34]"
in the expression column, which seems to work well, but here I get alot of errors

Any ideas?

View 2 Replies View Related

Null Values

Apr 20, 2007

I set up a new SQL database file, in that file I allowed nulls, When I went through code to save the record, the exception is saying it doesnt allow nulls.



Before I get to involved with SQL, is it a bad practice to use nulls?



If it is what do you enter in place of the null value,

which will lead to more code, right?



Davids Learning SQL

View 13 Replies View Related

Counting NULL Columns?

May 14, 2004

If I had a basic table with 3 VARCHAR fields; let's say A, B, C

How could I write a query that returns the count of the number of NULL columns for every record in a table?

Ideally, it would be something like:

SELECT
CAST (A IS NULL) AS INTEGER
+ CAST (B IS NULL) AS INTEGER
+ CAST (C IS NULL) AS INTEGER
FROM MyTable

That doesn't work at all. I can't seem to do "IS NULL" in the SELECT area. Should I write a T-SQL user-defined function that takes all three columns as parameters? Would that be performance friendly for large data sets?

View 13 Replies View Related

Check For Not Null In Either Of Two Columns

Apr 14, 2015

Content table

deleteddate revokeddate
1/1/2001 null
null 2/2/2003
4/5/2004 null

I am trying to create a stored procedure where.I need to loop through every row of the table and check if there exists a date in either of the column. If date exists in either of the column for every row, I need to update some other stuff.For the above table, it meets my requirement, so I need to update. However if the table is as below, it doesn't meet the requirement, so I don't need to update

deleteddate revokeddate
1/1/2001 null
null null
4/5/2004 null

I started off as below, But I am getting incorrect values.

Declare @deleterevoke bit
Declare @DelRevId int
Declare @DelRevnumrows int
Declare @tempDeletedDate datetime
Declare @tempRevokedDate datetime

[code]....

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved