Problem Viewing Records Having A NULL Value
Aug 27, 2005
hello friends!
i am new to sql and practicing on query analyzer
here is the practice table(test) i have ...
id | names| skill | city|
101| roy | welder | yuba|
102| peter | NULL | yuba|
103| alex | plumber | yuba |
104| edward | NULL | yuba|
105| smith | carpenter| yuba|
Now my problem is i want to see only those records which has skill = NULL..
I tried this stupid query ...
select * from test where skill = NULL
with this it is only showing me all the columns of my table
is there any query for my problem??
also
Is there any single query in query analyzer with which i can delete all the records having skill = NULL ?
View 3 Replies
ADVERTISEMENT
Oct 15, 2007
Hello,
I have a column called Prod_Class in a table. The data type is char(1) and allow null. The records have 3 distinct values ('E', 'P' and null) for Prod_Class in this table . When I run Select * from table1 where Prod_Class <> 'E', I expect I get the records that value equals to 'P' or is null. However, I only got records that the value equals to 'P'. If I run "Select * from table where Prod_Class <>'E' and Prod_Class <> 'P'", I got nothing while I expect I get the records that value is null. I know if I run "Select * from table where Prod_Class is null", I can get the records that value is null, but I need to know why I can not get result using "<>" and how to fix it. I am using SQL server 2005.
Thanks in advance for your help!
View 7 Replies
View Related
Oct 19, 2006
Hi, I am trying to use a formView with an update button to update individual records in an sql database. (when i click update it doesnt perform the update and just refreshes the page. ) One of the fields in my records is a NULL - this is also one of the fields that i need to update. When i manually go into the database and enter some data, and then go back to my form, it updates fine, but as soon as i delete the data from the field, it returns to NULL and im back to square one. Any Ideas on how to get around this problem?THanks
View 4 Replies
View Related
Oct 9, 2007
I'm hoping someone can help me with my problem.
I'm reading in records from a 'flat file' and loading them into sql.
I have 5 values I'm loading in. I first check my sql db, if all 5 values match a current record in sql, I don't want to load the record, because it's already there. If it doesn't exist, I need to load it.
It works fine as long as none of my values are NULL. But if I have a record with a field, say Gender, that is null, if its a new record it loads fine, setting gender to NULL in sql. But then when I encounter another record, which is identical, I'm testing to see if it already exists by doing a 'select where Gender = @Gender' and it always returns that the record does not exist in the db - even though it exists (because its using the = instead of is null)!
I need some mechanism where if the value is null it tests 'Gender is null' but if there is a value, it tests gender=gender, [and I need this for all my parameters]. Or is there some other way to do this?
Here is my code for looking to see if the record exists:
public static int ExistInsured(int CaseID, object InsuredLastName, object DateOfBirth, object CurrentAge, object Gender)
{SqlConnection conn = new SqlConnection(connStng.ToString());
SqlCommand cmd = new SqlCommand("SELECT top 1 InsuredID from Insured Where PolicyID = (Select PolicyID from Policy where CaseID = @CaseID) and LastName = @InsuredLastName and DateofBirth = @DateofBirth and Gender = @Gender", conn);
int result = -1;
cmd.CommandType = CommandType.Text;cmd.Parameters.AddWithValue("@CaseID", CaseID);
cmd.Parameters.AddWithValue("@InsuredLastName", InsuredLastName);cmd.Parameters.AddWithValue("@DateOfBirth", DateOfBirth);
cmd.Parameters.AddWithValue("@CurrentAge", CurrentAge);
cmd.Parameters.AddWithValue("@Gender", Gender);
conn.Open();
try
{object ret = cmd.ExecuteScalar();
if ((ret != null) && (ret is int))result = (int)ret;
}
finally
{
conn.Close();
}return result;
}
View 6 Replies
View Related
Feb 9, 2008
Hi,
All records of a table must be deleted if a field (field1) contains NULL or is made empty by a previous update command.
I did this but it only works when the field is made empty, not when it contains NULL:
dim field1 as string = ""...sql = "delete from mytable WHERE (field1=@field1)" comd = New SqlCommand(sql, oConnection) comd.Parameters.AddWithValue("@field1", field1) comd.ExecuteNonQuery()
How can i do that?
Thanks
Tartuffe
View 4 Replies
View Related
Aug 29, 2006
Hi -- I'm starting an ASP.NET 2.0 application which contains a page with a checkbox and gridview control on it. In its default state the gridview displays all the records from a table pulled from a SQL Server database (via a SqlDataSource object). When the user checks the checkbox, I want the gridview to display only the records where one of the columns is not null. But I've been unable to construct the WHERE clause of the SQLDataSource object correctly. I see that I can hard-code the SqlDataSource object so that the column to be filtered is always NULL or always NOT NULL. But I want this filtering to be more dynamic such that the decision to show all or non-null records happens at run-time. Should I be using two SqlDataSource objects -- one for the NOT NULL condition and one for the "all records" condition? Then when the user checks the checkbox, the gridview would be configured to point to the appropriate SqlDataSource object. (???) Seems like a bit of overhead with that approach. I'm hoping there's a more elegant way to get this done. Please let me know if you need more information. Thanks in advance. Bill
View 2 Replies
View Related
Feb 11, 2014
I have written up a grid consisting of properties and units.The way it works is we have properties, and within properties there are units. They are two seperate tables.Some properties do not have any units so in the unit reference (UN_UREF) column for those records which do not have units and are NULL I would like to insert the text 'NO UNIT'.Please see below for my SQL for the grid which works fine.
create or replace view VWC_PROPMKUNIT_TEST AS ( SELECT
PR_SNAM, PR_NAME, PR_ADD1, PR_ADD2, PR_ADD3, PR_ADD4, PR_ADD5, PR_ADD6,
PR_POST, PR_OWN, UN_UREF, UN_NAME, UN_GFA
FROM PROP
LEFT JOIN UNIT
ON PR_SNAM=UN_BREF);
All I need to do now is insert 'NO UNIT' within the Unit Reference column where it is NULL.
View 5 Replies
View Related
Dec 5, 2007
How to pull null (empty) records from SQL database,
what query am I suppose to use to get result
help me
thank you
maxs
View 4 Replies
View Related
May 14, 2008
Hi,
I'm running into an issue where if a report retrieves 0 records, I run into an error. My report has 2 queries-- the main query to retrieve Account information and the other to return Contact information based on the contact id(s) returned by the Account query. The reason needing two separate queries is that the contact and account tables are on different databases.
Now here is my account query: select contact_id, username, password from account.
My contact query looks like this: select name, address, .... from contact where contact_id = @contact_id.
In my report parameters, I define contact_id like this:
*******************************************
name = contact_id
data type = integer
prompt = [blanked out]
hidden = checked
internal = checked
multi-value = unchecked
allow null value = checked
allow blank value = unchecked
available values:
from query: dataset = account
value field = contact_id
label field = contact_id
default values:
from query: dataset = account
value field = contact_id
**********************************************
Now, when I run my report and knowing there are no records, I get the following error: "The 'contact_id' parameter is missing a value". Any ideas on how to solve this issue? Thanks.
larry
View 4 Replies
View Related
Jan 10, 2008
Can someone helo me to query a table and list all the columns and how many null records are there in each.
View 3 Replies
View Related
Nov 10, 2014
I recently ran into an issue with an issue with a query against our Data Warehouse. When attempting to sum revenue from a table, and using a WHERE clause on a field that contains NULL values, the records with the NULL values are suppressed (in addition to whatever the WHERE clause specified). I believe this is because a NULL value is unknown so SQL doesn't know if it does or doesn't fit the criteria of there WHERE clause so it is suppressed.
That being said, is there a way to avoid this instead of having to add an ISNULL function in the WHERE clause which is going to kill performance?
Code:
create table #nullTest (
name varchar(50)
,revenue int)
INSERT INTO #nullTest
Values ('Tim',100)
,('Andrew', 50)
,(null, 200)
SELECT sum(revenue) as Revenue FROM #nulltest WHERE name <> 'tim'
Ideally, I would want the SELECT statement above to return 250, not 50. The only way I can think to accomplish this is with this query:
Code:
SELECT sum(revenue) as Revenue FROM #nullTest WHERE isnull(name,'') <> 'tim'
View 4 Replies
View Related
Apr 11, 2014
I'm trying to insert multiple records, each containing a null value for one of the fields, but not having much luck. This is the code I'm using:
Use InternationalTrade
Go
CREATE TABLE dbo.ACCTING_ADJUST
(
stlmnt_instr_id varchar(20) null,
instr_id varchar(20) not null,
[Code] ....
View 5 Replies
View Related
Jun 9, 2015
I'm trying to get the records in table1 not in table2 the query is
Select * from table1 where not exists (select * from table2)
--table1 and table2 ,structure are same
It will get a null result
Strange thing , while I add a where condition in the subquery, it worked.
Select * from table1 where not exists (select * from table2 where table1.ID =table2.ID)
It will get the result as expected.
I just thought it would compare all the fileds for the outer query and inner query, but seems not. Why, I add a condition, it worked ?
View 4 Replies
View Related
Mar 3, 2008
Hi all I am having some issues in selecting items from my database where the record is NOT NULL. I have the code below however although some fields do contain soem data in it, others are blank which I believe are empty spaces. How do I do a SELECT command which ignores empty spaces and NULLS?
Code Snippet
SELECT CustomSearch FROM OfficesTable WHERE CustomSearch IS NOT NULL
Thanks, Onam.
View 10 Replies
View Related
Apr 4, 2007
Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.
Thank you very much.
View 2 Replies
View Related
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
go
declare
@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
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
AND DR.languageid = isnull(@inlanguageid,null)
AND DR.[ID]= @ID
)
go
set ansi_nulls on
View 3 Replies
View Related
Jan 21, 2008
I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database.
I have a very simple dilemma. In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields.
It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table.
In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters.
The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%')
If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null>
My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>.
For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null> (accounting for about 95% of the records in the database). Thanks in advance Simon
View 9 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Apr 2, 2008
Hi all,
I'm very new to SQL Server having previously worked with Oracle 10g for the last couple of years.
Anyway, in Oracle there is a view called V$SQL that provides information about SQL that is or has recently been executed. I'm trying to find a similar sort of functionality in SQL Server.
Basically, what I'd like to do is list all of the SQL statements that are running at a given moment. Or even better would be a way of listing all statements that have run in the last 10 minutes.
Appreciate any help for a newbie.
We're on SQL Server 2000 8.00.194
Cheers
Kloid
View 8 Replies
View Related
Aug 17, 2006
Is there any way, in enterprise manager or otherwise, for me to view the last few SQL statements executed against my sql server?Thanks
View 1 Replies
View Related
Dec 9, 2003
I just installed SQL Server on my 2003 box. Up till now I've been using MS Access for all my database needs. When I wanted to work on fields in a table, all I had to do was open Access and there you go. How do I view and modify Fields in SQL?
Chill
View 2 Replies
View Related
Dec 4, 2000
Is it possible to view the transaction log to identify events run against a database.
I know that I could create a trace file to log events.
But what I really want to know is whether there is any way to actually view the live .ldf transaction log file and make sense of it.
It is possible to do something similar in Informix.
View 2 Replies
View Related
Aug 17, 1999
How can I view the transaction log for a particular database. We are trying to track down when a delete occurred and wanted to look there to find it, unless there is a better place to look.
Thanks
View 4 Replies
View Related
Jun 28, 1999
Can any one tell me how you view the transaction log records since we don't have a syslogs anymore?
View 1 Replies
View Related
Apr 30, 1999
How can I view the contents of a transaction log? Is there a better way to determine who deleted a record and when?
View 1 Replies
View Related
Dec 13, 2004
Holy MOLY I've been banging my head up against the wall on this matter for months now. I have two databases and I need to be able to see the tables from different different databases. Usually I create a view like this
LicensingActions.dbo.License_Suspensions
But it wasnt working kept telling me that the License_Suspensions table didnt exsist and at the time the name was in all caps. So I decided to change the name, and low and BEHOLD IT WORKS. The funny was that I wasnt having that problem any of the other tables just that one. Well live and learn. Just thought I'd share that with you guys
View 4 Replies
View Related
Aug 22, 2005
I just got some great code from this site for viewing constraints, but I can't find the equivalent for indexes (and I couldn't modify the constraint sql), any suggestions?
View 2 Replies
View Related
Jun 17, 2007
why can't i see SQL server 2005 the same way i see Visual Basic 2005 Express edition. i'm able to see VB by going under the all Programs menu and select it, when i try to find SQL 2005, i see configuration tools and some submenu of configuration tools. is that the way it is? if not, how do i get SQL 2005 to open up like VB so i can play with it. Also, at home i do not have a server, do i have to have a home server to play with SQL
hens
View 5 Replies
View Related
Feb 17, 2006
I am trying to view a "View" that is in one of my databases, and I am having a problem seeing the scripting that is inside of it. In sql2000 all I needed to do to view a "View" is double click on it. If I double click on a "View" in sql2005 it will open more folders showing the layout, but I want to see the scripting. If I right click on it and then click open view, sql2005 returns the values that the view would bring across. Does anybody know how to view the scripting of a "View" for sql2005?
Also if I select Modify, sql2005 returns scripting but it does not look right.
Thanks for the help!
View 7 Replies
View Related
Mar 28, 2006
I would like to know the best way to view Package Logs that are being stored in SQL Server? Right now I select out of sysdtslog90 to see what the log file has. I would like to know if there is a way like SQL 2000 where there is a drop down for the date and you could view the entire package data.
View 2 Replies
View Related
May 4, 2001
I have a linked table in Access to my SQL 7 database. But one field who's values are either 0 or 1 now show up as 0 or -1 when I view the linked table in Access?
Any ideas why? Thanks..
View 1 Replies
View Related
Jan 2, 2001
Hi,
What minimum level access/permission/role is required for a database user to view scheduled job listing?
Thanks in advance..
View 1 Replies
View Related
Jun 19, 2001
I would like to be able to get a listing of the object permissions for a particular group in a database. I can't find a procedure for doing this. I know I must be overlooking an obvious solution. Does anyone have any suggestions for doing this? Thanks.
View 1 Replies
View Related
