Check For Null Or Empty Values And Return 0

Mar 13, 2014

I am using the below query to calculate column values. But I need to return zero when a column values is empty or null.

select [Funding] [Fundings],
[Original] AS [Originals],
[Variance] = SUM([Previous_Year]-[Current_Year]),
[SumValue] = SUM([CurrentYear]/4),
[ActualValue] = SUM([Variance] * 0.75),
[New Value] = SUM([Previous_Year]+[Current_Year])
from Finance
GROUP BY [Original], [FinanceYear]

View 1 Replies


T-SQL (SS2K8) :: Check If Variable Is Empty Or Null?

Sep 9, 2014

declare @user varchar(30) = ''
if(@user is not null or @user <> '')
print 'I am not empty'
print 'I am empty'

The output should be 'i am empty' but when i execute this i am getting ' i am not empty'. even i did browse through but i don't find the difference in my logic.

View 9 Replies View Related

Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?

Oct 27, 2004

i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:


IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))

Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table

but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"


View 2 Replies View Related

Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?

Oct 27, 2004

i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:

IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))

Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table

but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"


View 3 Replies View Related

How To Check For Null / Empty For A Variable In Post Deployment Script

Oct 28, 2015

I am using SSDT to deploy my database project. This project has a post deployment script and i am creating a new user in this post deployment script. I have used sqlcmd variable to capture the username. I am doing a null/empty check for this variable and my code looks like below.

IF (([$(UserName)] IS NOT NULL) OR ([$(UserName)] <> ''))

When i execute this, I am getting below error.

Could not deploy package.

Error SQL72014: .Net SqlClient Data Provider: Msg 207, Level 16, State 1,Line 15 Invalid column name 'User Name'.

Error SQL72045: Script execution error. The executed script:

View 2 Replies View Related

NULL Values Vs Empty String Vs Space

Oct 25, 2006

How do I define a field to have the default value = ''. Not NULL but not a space either in SQL Server 2005?

View 10 Replies View Related

Analysis :: YTD / MTD Functions Return Empty Values Probably Due To Old Test Data

Jun 17, 2015

I have managed to use the BI Wizard for time intelligence and added YTD and MTD successfully. I notice the values returned are empty, and I think this is due to the fact that all the test data I use is many years old. What's the simplest way to resolve this issue so that I can see that these MDX functions return correct values? Changing the system date on this company laptop is not an option.

View 4 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

How To Check For NULL Values In T-SQL

Jan 19, 2007

How can I check on NULL values in a Select statement?
SELECT ID FROM TabelWHERE somecolumn <> NULL??

View 2 Replies View Related

Transact SQL :: Check 2 Columns And Return Rows As Long As One Column Is Not Null

Oct 19, 2015

how to do a check for 2 columns. As long as there is data for at least one of the columns I want to return rows.

Example Data

create table test
ID int,
set1 varchar(50),
set2 varchar(50),


View 4 Replies View Related

Check For Null Values In Transact Sql

Aug 20, 2006

I am using Visual Web Developer Express 2005 and SQL Server Express 2005.
I have set up a trigger that fires when an update has been performed on a specific table.  The trigger checks to see if specific columns have been updated.  If they have, then the trigger code is executed.  This part works fine.
I am now trying to find a way to check if null values exist in either one of two field from the same table.  If either field contains a null value, then I don't want the trigger code to be executed.
How can I check for null values and skip a block of code within my Transact Sql trigger.

View 2 Replies View Related

Return A Row With Null Values?

Apr 19, 2014

I am trying to return all the names of employees and their managers

this query returns all the employees except for 1


the one that isn't returned has a null SUPERSSN, but when I add in:


it returns a row with the name of the employee whose SUPERSSN is null 8 times (where each time the M.FNAME,M.LNAME are other employee names)

How do I ammend the first query to return each employee and their respective manager once, the employee without a manager having null values for the manager name columns?

View 1 Replies View Related

Sum Column Values And Also Check Null Condition

Mar 28, 2014

select '$ '+ CONVERT(varchar,CONVERT(decimal(10,0),CONVERT(money, Amt_Value)),1) as [Amount]

from Products

How can I sum this column values and need to set a validation like the column has null values it has to return zero.

View 2 Replies View Related

Return Null Values Using Coalesce And Nullif

Jan 9, 2008


I'm creating a 'dynamic where clause' with 15 parameters. I was using coalesce with the example below and it's working fine until it encounters a null value. I was trying to use nullif with coalesce to no avail. Can someone show me based on my example below how I can incorporate nullif with coalesce. Thanks in advance for you help.

Code Block
WHERE cs.DirID = Coalesce(@DirNum, cs.DirID)

View 12 Replies View Related

Return NULL Values In SELECT Statement With INNER JOIN ?

May 16, 2005

If I try to run the code below, and even one of the values in the INNER
JOIN statements is NULL, the DataReader ends up with zero rows. 
What I need is to see the results even if one or more of INNER JOIN
statements has a NULL value.  For example, if I want info on
asset# 2104, and there's no value in the DriverID field, I need the
rest of the data to display and just have the lblDriverName by
blank.  Is that possible?

    Sub BindSearchGrid()
        Dim searchUnitID As String
        Dim searchQuery As String
        searchUnitID = tbSearchUnitID.Text
        lblIDNum.Text = searchUnitID
        searchQuery = "SELECT * FROM Assets " & _
        "INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _
        "INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _
        "INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _
        "INNER JOIN AssetCategories
ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _
        "INNER JOIN Store ON
Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '"
& searchUnitID & "'"

        Dim myReader As SqlDataReader
        myReader = Data.queryDB(searchQuery)
        While myReader.Read
Not IsDBNull(myReader("Store Name")) Then lblStrID.Text =
myReader("Store Name")
Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text =
Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text =
myReader("Condition Description")
Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text =
Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit
Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year")
Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make")
Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model")
Not IsDBNull(myReader("Mileage")) Then lblMile.Text =
Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text =
myReader("Vin Number")
Not IsDBNull(myReader("License Number")) Then lblLicNum.Text =
myReader("License Number")
Not IsDBNull(myReader("Name")) Then lblDriverName.Text =
Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text =
Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text =
Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text =
Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text =
Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR")
Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW")
Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text =
myReader("Crane Capacity")
Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text =
myReader("Crane Certification")
Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text =
myReader("Repair Cost")
Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text =
myReader("Estimate Replacement")
Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text =
Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text =
Not IsDBNull(myReader("Comments")) Then lblCom.Text =
Not IsDBNull(myReader("Description")) Then lblDesc.Text =

        End While
    End Sub</code>

View 1 Replies View Related

Nested Join To Return Only Rows With Null Values From All Tables

Oct 17, 2007


I have this INNER JOIN that is fine to show all possible combinations. But I need to show only rows that have one or more Null values in tbIntersect.

Should I use nested LEFT JOINT? How?

This is the SQL statement:
sSQL = "SELECT DISTINCT tbCar100.Car100_ID, tbCar100.Description100 AS [Caractéristique 100], " & _
"tbCar200.Car200_ID, tbCar200.Description200 AS [Caractéristique 200], " & _
"tbCar300.Car300_ID, tbCar300.Description300 AS [Caractéristique 300], " & _
"tbCar400.Car400_ID, tbCar400.Description400 AS [Caractéristique 400], " & _
"tbCar500.Car500_ID, tbCar500.Description500 AS [Caractéristique 500], " & _
"tbCar600.Car600_ID, tbCar600.Description600 AS [Caractéristique 600], " & _
"tbCar700.Car700_ID, tbCar700.Description700 AS [Caractéristique 700], " & _
"tbProducts.Prod_ID, tbProducts.PartNumber AS [Part Number] , tbProducts.Description AS [Description] , tbProducts.DateAdded AS [Date] " & _
"FROM tbProducts INNER JOIN (tbCar700 INNER JOIN (tbCar600 INNER JOIN (tbCar500 INNER JOIN (tbCar400 INNER JOIN (tbCar300 INNER JOIN (tbCar100 INNER JOIN " & _
"(tbCar200 INNER JOIN tbIntersect ON tbCar200.Car200_ID = tbIntersect.Car200_ID) " & _
"ON tbCar100.Car100_ID = tbIntersect.Car100_ID) ON tbCar300.Car300_ID = tbIntersect.Car300_ID) ON tbCar400.Car400_ID = tbIntersect.Car400_ID) ON tbCar500.Car500_ID = tbIntersect.Car500_ID) ON tbCar600.Car600_ID = tbIntersect.Car600_ID) ON tbCar700.Car700_ID = tbIntersect.Car700_ID) ON tbProducts.Prod_ID = tbIntersect.Prod_ID " & _

Here is the content of the tbIntersect table:
Car100_ID Car200_ID Car300_ID Car400_ID Car500_ID Car600_ID Car700_ID Prod_ID ID
1 1 1 1 1 1 1 1 1
1 2 1 1 1 1 1 19
1 3 1 1 1 1 1 20

I need to return the rows that have null data, ex: second row because Prod_ID is NULL and third row because Car300_ID is NULL. In fact I need the data from the other joint tables that correspond to these ID fields.


View 5 Replies View Related

How To Return Null Values For Non-exist Record On A Left Join Statement

Oct 16, 2004

I have table Products and Orders that has the following columns:

table Products: ProductID, ProductName
table Orders: OrderID, ProductID, OrderDate, Quantity, Price

The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.

I tried the following select statement:

select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'

Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :

ProductName / Quantity / Price
Product-A 5 1.89
Product-B 6 2.43
Product-C null null

Obviously, my sql statement won't work becaue the where clause will filter out Product-C.

Could anyone help me figure out how to modify my sql code to get the resultset I want?

Thanks in advance.

View 2 Replies View Related

Transact SQL :: Preserve And Return NULL For Non Matching Values From A Table Valued Function

Jun 29, 2015

I have tables and a function as representated by the code below. The names  for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.

When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.



[Code] ....

View 4 Replies View Related

Problem With Isnull. Need To Substitute Null If A Var Is Null And Compare It To Null And Return True

Sep 20, 2006

Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you

set ansi_nulls off



@inFileName VARCHAR (100),

@inFileSize INT,

@Id int,

@inlanguageid INT,

@inFileVersion VARCHAR (100),

@ExeState int

set @inFileName = 'A0006337.EXE'

set @inFileSize = 28796

set @Id= 1

set @inlanguageid =null

set @inFileVersion =NULL

set @ExeState =0

select Dr.StateID from table1 dR


DR.[FileName] = @inFileName

AND DR.FileSize =@inFileSize

AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)

AND DR.languageid = isnull(@inlanguageid,null)




set ansi_nulls on

View 3 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 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

Change Not Null To Null, Default Value To Empty

Aug 3, 2005

hi,my structure table in database:Amount float(53) not null default 0when i try to run his script:alter table ABC alter column Amount float(53) nullit can only set the Amount to allow null, but can't set the defaultvalue to empty.anyone know how to set the field to allow null and default set toempty, no value.thanks

View 5 Replies View Related

Reporting Services :: Give Meaning Full Name To Allow Null Value Check Box In Report Parameter Instead Of NULL?

Oct 20, 2015

In my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.

when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL. 

Is it possible through SSRS designer?

View 5 Replies View Related

Check If A Table Is Not Empty

Nov 15, 2006


i wanna know how can i check a table has datas inside, because i wanna use delete from table and if table is empty i dont wanna run this statement.

thanks in advance

View 5 Replies View Related

Check If SqlDataSource Is Empty In CodeBehind

Feb 10, 2006

This is probably an easy one.
What is best way to determine if a SqlDataSource is empty (i.e. the query produced no results) in the CodeBehind?
I'm using this:
if (SqlDataSource1.SelectCommand.Contains(String.Empty))
      //Add code for scenario here.
It seems to work, but something just doesn't feel right about it for some reason.

View 2 Replies View Related

How To Check If A Table Is &<empty&> Ie Doesnt Contain Any Rows

Aug 18, 2007

Hi, is there a way I can check if a table is empty and contains norows?I have a Table1 which being dynamic can sometimes end up with nowcolumns what so ever. I'm using Table1 in one of my views along with 2other tables and I would like put a condition at the...something likeAND Table1 IS NOTEMPTYIs there a way to do this in MS SQL?Many thanksYas

View 7 Replies View Related

How To Check If A Table Is Empty Using Query Code

Aug 9, 2013

I want to check if a table is empty using query code?

How to do this?

View 4 Replies View Related

T-SQL Syntax To Check For Nulls And Empty In One Statement

May 5, 2008

What is the T-SQL command to check for NULL or '' in a field in one statement? I would like to change the following code to be more readable (without the OR).

IF @agent IS NULL OR @agent = ''


View 1 Replies View Related

Data Flow Task Empty After Check In

Dec 5, 2006

On my office PC I have a strange problem with Data Flow Tasks. When I check-in a package into Source Control (Team Foundation) I usually have some empty Data Flow Tasks in the packages...even if I haven't changed that Flow Task.

This is really frustrating cause when running the package all goes well, but after a whil you notice that some tables are empty.

Does anyone know what the problem is?

View 18 Replies View Related

Return Value Of Empty List

Aug 15, 2006


I'm trying check the records which satisfies the following condn.

select @p1 = su1 from RER where (no=10 and atp ='N')

It returns empty list . bcos no match found.that's ok.

But i need to write the logic if record is not there.

i tried to do as follows

if ((@p1 =null) or (p1=' '))

...some logic


.. other logic

It's always executing else case. How do i catch empty value using sql query.

Is there any command there like null?

I tried in SQL query analyzer. Using SQL 2000.

Thank You

View 6 Replies View Related

Empty &&<&&> IS NOT NULL

Jul 22, 2007


I have a query that returns the appropriate values I need, however there is one field I'd like to add and utilize but my problem is I only want to use it if it contains data.

If I filter it with IS NOT NULL it returns all the records, including the empty records. The field is simply empty, and doesn't come back as NULL. If I filter it with =' ' , it shows all the records with the empty records only.

I need to do the opposite, be able to filter it only if it's not empty.

Any help would be appreciated.

View 4 Replies View Related

How To Return A List Of Empty Tables

Mar 19, 2003

I want to return a list of user tables from a database where the rowcount is 0. This will be a 3 step process: (1) truncate all 'New%' tables, (2) load data via ODBC/DTS into 'New%' tables, (3) list all 'New%' tables with zero rows (i.e. those that didn't get loaded, as all tables in the ODBC data source contain data).

I've tried:
select left(,32) as TableName, max(s1.rows) as Records
from sysindexes s1
inner join sysobjects s2 on
where type = 'U' and like 'New%'
group by
HAVING max(rows) = 0
ORDER BY TableName
but of course there are multiple rows in sysindexes and the routine does not reliably return the correct list; for example the data in sysobjects & sysindexes, without the max and group by, might look like:
I was hoping to come up with a single T-SQL statement that I could use in an xp_sendmail situation to email me the results.

Thanks for the suggestions.


View 3 Replies View Related

Big Queries Return Empty Result Set

Apr 22, 2004

Hello guys,

MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables:
If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice.
The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application.
Has anybody suffered this problem and what solution was adopted?
Thanks for any information you can provide.

View 5 Replies View Related

Copyrights 2005-15, All rights reserved