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


ADVERTISEMENT

SQL Server 2012 :: Check To See If Columns Are NOT NULL In A Record

Jun 25, 2015

I am looking for the best way to check to see if any columns are still NULL in a record. I have a form that gets filled out by users and the values entered into TableA. There are 6 columns in the table, 5 are responses and column6 indicates if the record is complete. So I want a way to see if all of the first 5 columns are NOT NULL and, if so, mark column6 with a 1.

I am thinking this would be a good thing for a trigger on INSERT or UPDATE to check to see if the first 5 columns are filled in and then mark the record as complete.

View 5 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),

[code]....

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

How To Check Value Is Null Or Not

Apr 17, 2008

Hi
       I have two tables. one  MasterDetail and second is countrydetail
In master detail i have MasterDetailId (Primary) and  countryId.
In CountryDetail table I have Countrid (Primary),CountryName.
I don't want to give relationship because i can insert null value in countryid in MasterDetail table.
So i have wriiten query like this
->     select c.CountryName,m.MasterDetailId from MasterDetail m,CountryDetail c where c.CountryId=m.CountryId and m.MasterDetailId= '2'
If In MasterDetail table if CountryId is null then it will not show me any record. So I want that  record and its value with this query and checking null values. Help me about solving this query.
Thank You
 

View 4 Replies View Related

Check For Null

Mar 11, 2008

I am trying to create a stored proc.
I have to check a parameter for null value and if null then set it to zero
Here is the stored proc
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[UPDATENOA]
(
@Accepted char(10),
@NOAId bigint,
@Court char(30),
@RetainerType char(30),
@PendingInTaxCourt char(1)
)
AS
begin
UPDATE NOA set StageCode = Accepted,Court=@Court,RetainerType=@RetainerType,PendingInTaxCourt=@PendingInTaxCourt where NOAId = @NOAId;
Declare @Sequence int
Select @Sequence = MAX(Sequence) From [dbo].[NOAStages] where NOAId=@NOAId;
if @Sequence IS NULL
{
@Sequence=0
}
Insert into [dbo].[NOAStages] (NOAId,StageCode,Sequence)
values (@NOAId,@Accepted,@Sequence+1)
But this is giving me error. What is the correct way to do this.
Thanks

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

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

Check For NULL In CASE

May 19, 2006

SELECT whatever_field, CASE LEN(DrAccount)   WHEN 12 THEN DrAccount   ELSE CASE (Note1)     WHEN NULL THEN Location + DrAccount     ELSE Note1 + DrAccount     END   END AS Account FROM Table1 The purpose of the CASE(Note1) is when Note1 column is null, return Location+DrAccount. The actual result is when Note1 column is null, it always returns null, Location+DrAccount is not executed. When Note1 column is not null, it returns correctly Note1+DrAccount. The problem seems to reside in validating the null value in WHEN NULL How to check for null in CASE(fieldname) WHEN ???

View 1 Replies View Related

Please Check This 'not Null' SQL String

Jun 20, 2007

the SQL string below worked, and then started bringing up every record.
it should only select records with a value in at least one of the columns, but it apears to be suggesting that all records have some data in one of the columns. if I check the database or the output on the web page there apears to be no data. ?? confused.

"SELECT id, make, model FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"

Any ideas how I could implement this more robustly?
cheers
M

View 14 Replies View Related

How Do I Check If A Field Is Null?

Jul 2, 2007

hi guys, I'm using SQLServer 2005 and I was wondering how I check if some variable is null or not? I tryied to do as the code show but it doesn't work:

declare @number int

set @number = (select number from numbers where number_id = 1)

if (@number = null)
begin
set @number = 12
end

Thank you very much

View 3 Replies View Related

How To Check For Null Value And If Exist?

Aug 23, 2007

Okay, I have the following statement for my store procedure:



Code Snippet
IF exists (SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID))
SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)
ELSE
SELECT null ID, 'No parent page yet' title FROM Outlook






I wanted to put an ELSE IF statement right before the ELSE statement to check if the value is null. Something like this:


IF exists (SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID))


SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)
ELSE IF (SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID) != null


SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)

ELSE


SELECT null ID, 'No parent page yet' title FROM Outlook


Or if this is possible, it's much better:


IF exists (SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID) AND (SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID) != null)


SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)

ELSE


SELECT null ID, 'No parent page yet' title FROM Outlook

I don't quite get how to write the SQL statement that I wanted so any help is much appreciated.

View 12 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.
Thanks.....

View 2 Replies View Related

Check For Null In Repeater Date Field

Jan 27, 2008

I'm about ready to pull my hair out.  I have a repeater control, and a date field.  If the field is a valid date, I'll use it to calculate and display the person's age.  Otherwise, I want to display "N/A".  My problem is trying to determine if the date field is null or not.I'm using SQL Server 2005 which allows Nulls in the date field, and for many records I don't have a birth date.  It makes sense for these records to leave the date Null.  This is my code:Protected Sub Repeater1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater1.ItemDataBound    If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then        Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label)        Dim LblAge As Label = CType(e.Item.FindControl("LblAge"), Label)        Dim inmate As WAP.prisonmembersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.prisonmembersRow)        If System.IO.File.Exists(Server.MapPath("~/images/picts/" & inmate.FILE2 & ".jpg")) Then            LabelIcon.Visible = True        End If        If inmate.DATE_OF_BIRTH Is DBNull.Value Then            If IsDate(inmate.DATE_OF_BIRTH) Then                LblAge.Text = "Age: N/A"            Else                LblAge.Text = "Age: " & GetBirthdate(inmate.DATE_OF_BIRTH)            End If        End If    End IfEnd Sub How can I resolve this?Diane 

View 7 Replies View Related

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

Sep 9, 2014

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

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

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),
[FinanceYear],
[New Value] = SUM([Previous_Year]+[Current_Year])
from Finance
GROUP BY [Original], [FinanceYear]

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

Check Constraint Does Not Work (compare With Null)

Mar 20, 2006

Hi!I have a table with a check constraint. But unfortunately it does notwork like I wanted.CREATE TABLE MAP([R_ID] [T_D_ID] NOT NULL,[R_ID1] [T_D_ID] NULL,CONSTRAINT CHECK_ID1 CHECK (R_ID1 = R_ID OR R_ID1 = NULL),CONSTRAINT [PK_MAP] PRIMARY KEY ([R_ID]))R_ID1 should always have the value of R_ID or NullThe following statements should cause errors:insert into map (R_ID, R_ID1)values(1,2);update map set R_ID1=3 where R_ID=1;But there occur no errors. Does anyone have an idea? It is an SQL Server2000.TIASusanne

View 6 Replies View Related

Check Null Value Of Long Data Type

Jul 20, 2005

Hi, I have a record set that is bound to a table in MS SQL Server. Onefiled in the table is bound to a "long" type member variable in theRecordSet. What will happen to the "long" variable when the field isNULL in the table?Thanks!-Yi

View 1 Replies View Related

Can I Check For Null Reference Before Referencing ReportItems?

Feb 4, 2008

A report I maintain has an extensive footer that appears on each page, populated with information I've stashed into invisible cells in the table's header. This works great, except in rare circumstances where the report includes a page that doesn't display the table at all--in which case the report errors when it attempts to print ("Object reference not set to an instance of an object").

Due to the requirements of the report, I cannot guarantee that the table will appear on every page, every time. Is there a way I can safely refer to the reportItems collection, in case of Nulls? I'll be okay with blank values in the footer in the cases where the page has no reportItems to work with, but I can't have the report blow up when printing.

Thanks in advance,
Jeff Lautenschlager

View 3 Replies View Related

How To Check Null Or '' In Date Field In Expression And ...............

Aug 16, 2006

I have a column say 'ActivationDate' which is a (database timestamp [DT_DBTIMESTAMP]) which I want to replace with an expression in derived columns

The condition is if 'ActivationDate' field is null or '' then 'Null' else 'ActivationDate'

I am struggling to write this condition. Without condition i.e. at present it saves the value in this database '1753-01-01 00:00:00.000'.
In the preview the 'ActivationDate'field does not show any thing so I recon it is either null or ''





View 6 Replies View Related

Transact SQL :: Check For NULL In CASE / WHEN Statements?

Aug 5, 2015

I have a table that keeps track of all changes that were performed in an application. There is a column called "old value" and column called "new value". There are some values in the application that don't require data therefore the "old value" or "new value" values can be empty. These columns are an nvarchar data type because the value can be text or numbers or dates. An example is "ReceivedDate". There is a report that is generated based on this table.

What is happening is the query in the report dataset is adding dates when it should be displaying empty. They query is using "CASE/WHEN/THEN". What I need is "When the column is "RecievedDate" and it is not null then convert it to a date". This is for formatting purposes.

This is an example of the table:

UpdateColumn
Old Value
New Value
ReceivedDate
 7/8/2015 5:00:00 AM
ReceivedDate
7/8/2015 12:00:00 AM
7/9/2015 5:00:00 AM
ReceivedDate
7/9/2015 12:00:00 AM

So, the first time it was updated there was no value but it was replaced with July 8, 2015 and so on.This is what the report is displaying

This is the query:

CASE UpdateColumn
... WHEN 'ReceivedDate' THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') )
...
I tried adding
CASE UpdateColumn
...
WHEN 'ReceivedDate' IS NOT NULL
THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') )
...

But it did not like the "IS NOT NULL".

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:


Code:


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"

thanks

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"

thanks

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)] <> ''))
BEGIN
CREATE USER [$(UserName)] WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo;
GRANT CONNECT TO [$(UserName)];
END

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

T-SQL (SS2K8) :: Check Multiple Columns With One Value

May 21, 2014

I keep thinking this can be done in one query:UPDATE H

SET Phone1 = NULL
FROM Dialer.dbo.Agentless_Hold H
INNER JOIN Dialer.dbo.PhoneLookup D ON H.Phone1 = D.Phone;
UPDATE H
SET Phone2 = NULL

[Code] ....

I need to NULL any phone number in the Hold table where the number is in the Lookup table.

View 5 Replies View Related

Check A Value Against Multiple Columns In Different Table

Nov 25, 2014

I have two tables table1 and table2. I want to check a value from table1 against 4 different columns in table 2. What would be the most optimized way to do this. I came up with this SQL but it runs forever.

select * from table1 a
where
(a.id in (select orig_id from table2 where exctn_dt >= '01-OCT-14')) or
(a.acct_intrl_id in (select benef_id from table2 where exctn_dt >= '01-OCT-14')) or
(a.acct_intrl_id in (select send_id from table2 where exctn_dt >= '01-OCT-14')) or
(a.acct_intrl_id in (select rcv_id from table2 where exctn_dt >= '01-OCT-14'));

View 5 Replies View Related

How Check Columns + Fields Add Result Row At END

Apr 9, 2008

hi need help from the wonderful people here
i am working on project and convert a OLD DOS program to SQL server on the web
this is the resalt i need to show





napipot
Fname
new_unit
day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444

1
aaaa
1_1
A


C


Q
D
E
y



2
bbbb
2_2
-




E
A



d


3
cccc
3_3
B



F

B



z
f

4
dddd
1_1
-





*






5
eeee
4_4
C


A


C










--------------------------------------------------------add space row-------------------------


result row





Ok

OK

OK

ok

B

ok

ok

ok

ok
A
B
C


i need check only the
A
B
C

if not show the missing A B C
like this
A on the left
B on middleC on right

View 1 Replies View Related

T-SQL (SS2K8) :: Check Constraint On Multiple Columns

Jul 25, 2014

I have a case where if the Id field is a specific value, I don't want to allow null in another field, but if the Id value <> a specific value, null is ok.

In the example below, inserting the first record should succeed, the second should succeed, and the 3rd should fail. Right now the 2nd two fail. I gotta be missing something easy, but I can't figure it out.

USE tempdb
GO
IF OBJECT_ID('tempdb.dbo.CheckConstraintTest') IS NOT NULL
DROP TABLE tempdb.dbo.CheckConstraintTest;
CREATE TABLE CheckConstraintTest

[Code] .....

View 4 Replies View Related

Lots Of Columns To Check/convert - Best Practice?

Jun 27, 2007

I've got about 6 formats of flat files coming through data flows & heading into a relational db, then later to a data warehouse. In each file type, I've got about 60-70 columns to perform basically two levels of validations on - first is straight data type conversions, then 2nd is finer level stuff. Some of the data from each file type overlaps in other files, so for instance some lookup codes are maintained for both.



The data is pretty dirty, so I'm keeping everything as varchar coming into the staging area, just so I can get the data in the system, b/c the users demand on some form of of the data making it into the system, no matter the dirtiness.



So then I'm running my two steps - first converting data types from varchar to bit/datetime/int, etc. as applicable. And then , I'll be running finer levels of validation, doing range checks, etc. My question is - with so many columns, what's the most efficient & best way of doing all these checks in the data flow, and recording errors out of each check? Do I put one column check after another with the success constraint, do the lookup/range check/other, and then record the error if an error is encountered, then move on to the next column? Or would it be better to multicast out the stream to 60 flows and do all the needed checks, then union all the good stuff back together at the end? Anything to help save some headache - b/c this data is dirty and there's a lot of it.

View 4 Replies View Related

Not Able To Check For Nulls While Looping Through Input Columns

Mar 10, 2008

Hi,

I have an SSIS Package. I am using script component to loop through input columns and their values. I am not able to do Null checking. The code is as below. In place of dashes , I want to do null checking but am not able to do. I tried vbNull, IsNull, TypeOf, System.dbNull but nothing is working. I guess am missing something here. Can anyone help me with this.


For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection

columnValue = rowType.GetProperty(column.Name)


If Not --------------- Then


RowData += ((FormatElement(column.Name) + ReplaceSpecialChars(columnValue.GetValue(Row, Nothing).ToString()) + FormatElement(column.Name, True)))

End If

Next


Thanks in Advance.

View 11 Replies View Related

How To Check Whether Nchar Columns Arabic Character Set?

Mar 5, 2008

Hi

How can I check whether an nchar column contains Arabic character set? The only allowed values that can go into this column are English and Arabic.



Thanks,
Salman Shehbaz.

View 4 Replies View Related

Adding New Columns With Check Constraints Using Same Batch

Jul 11, 2006

I'm using a stored procedure to add fields to an existing table.

These fields must have check constraints and I need to use one T-SQL batch.

In Sql2000 Ok. In Sql2005, if table exists, I get error "invalid column xxxxx" in Add Constraint statement before new column creation.

the code is

Declare @Setup bit

Set @Setup = 1

if @Setup = 1 Begin

--Alter Table MyTable Add MyField Numeric(1, 0) Not Null Default 3

Exec mySp_Add_Column 'MyTable', 'MyField', 'Numeric (1, 0) Not Null', '3'

If IsNull(ObjectProperty(Object_Id('xCK_MyTable_MyField'), 'IsConstraint'), 0) = 0

Alter Table MyTable Add Constraint xCK_MyTable_MyField Check (MyField >= 1 And MyField <= 3)

End Else Begin

-- drop column

End

GO

If MyTable does not exist and, naturally, I add it before of check constraints (using another Sp which add tables) ok.

If I add FK to new fields, ok.

Now I have to split batch in two parts as workaround...

Can anyone tell me if this is a bug or a "fix" for previous versions?

Many thanks,

Giulio

View 7 Replies View Related







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