Hi Guys,
I have a report where some fields are blank, i need to put 0 when the field is blank, i tried the following
: IIf (Fields!X.Value = System.DBNull, 0, Field!X.Value)
this is complaining about System.DBNull is a type in system and cannot be used in comparison, what other types of fields can I use here to compare against Empty data?
is there anything basic I am missing?
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
Hi,
I am very new to using SQL. Our department usually uses Brio to query the various databases under our control. However, I have recently come against a problem that prompted me to create a custom SQL query which works well as far as it goes. My problem is looking for specific conditions in billing information I receive monthly. I would like to compare on of the date fields contained in the database with a field in the form of YYYYMM (200710, for October 2007) I have created a custom column generator that forms a date from the YYYYMM. I would like, however, do the translation on the fly and make the comparison during the query. The problem is that query without the date check returns a mass of data, only about 1 percent of which is what I want.
The beginning of the SQL query looks like this:
FROM From.T_Crs_Tran_Dtl WHERE T_Crs_Tran_Dtl.Crs_Bill_Yr_Mo IN ('200710', '200711', '200712') AND ((T_Crs_Tran_Dtl.Crs_Cde IN ('1G', '1V') AND (T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND (T_Crs_Tran_Dtl.Prev_Stats_Cde IN (' ', 'TK', 'TL') AND T_Crs_Tran_Dtl.Cur_Stats_Cde IN ('TK', 'TL') AND T_Crs_Tran_Dtl.Std_Tran_Typ_Cde='B') OR (T_Crs_Tran_Dtl.Prev_Stats_Cde='UN' AND T_Crs_Tran_Dtl.Cur_Stats_Cde='XX' AND€¦
It is the €œ(T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND€? part of the query that is just plain wrong. The business part of this statement takes the YYYYMM field and turns it into a date which is the last day of YYYYMM.
I hope someone out there can help me with making this comparison.
I appreciate your help.
Bill
I want to apply something like below in my where clause:
select users.first_name
from users
where users.somecolumn = isnull(@somecolumnvalue, users.somecolumn)
The idea is to get all the values from users table if the value contained in @somecolumnvalue is null.
Now the issue is, users.somecolumn is nullable and has many rows that have null values. This query returns only those rows that have values populated in users.somecolumn.
How can the desired result be achieved?
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
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 RelatedHi all experters,
Please suggest how to build the report in below case:
Raw data:
ID
Member
Functions
1
Alan
A
1
Alan
B
2
Tom
A
2
Tom
B
2
Tom
C
3
Mary
D
3
Mary
E
Report Shows:
ID
Member
Functions
1
Alan
A,B
2
Tom
A,B,C
3
Mary
D,E
I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.
Any good suggestion?
Thanks in advance,
Steve Wang
2008/4/2
I am a bit of a SQL Server newbie and have a question. I'm trying to compare two text fields. Both are 56 character fields and are a company name (one company want to see how much customer overlap they have with a newly acquired company). As you can imagine the names are a bit different in each ABN AMRO versus ABN AMRO INC. I tried comparing the first 6, 7, 8 characters with some success. Is there a more advanced way to do this? I appreciate the help.
View 2 Replies View Related
hi guyz i want to compare time from DateTime field i.e. i want to identify if the time is from 1pm to 2pm the do this else do......
select DATEPART(hour, loginTime) ......returns me the hour i can get the Hour part of the time but the prblem is how to identify it
whether it is less than 2:00:00 pm and greater than 1:00:00 pm i can do this task using at application level but i want this to b done at query level
any ideas??????????
I am trying to build a simple search engine using Sql Server 2000 to scan information about approximatelly 20.000 products.
Heres what I am doing:
I created a table called keywords that contains a reference for each product.
keyword -> varchar(100)
items -> Text
keyword data example:
[keyword] [items]
car 1, 3, 5, 7
blue 3, 5
compact 1,7
I am not using clustered index.
To search basically I run the "AND" or "OR" to select the keywords I want to target.
I need to run another select that would compare the data in the items field depending of the condition selected. If "AND" clause is used I would need to compare all the items that contains the same reference, for example:
looking for car compact using "AND" clause
result = 1
looking for car compact using "OR" clause
result = 1,3,5,7
There is no table that holds references. The items are stored in a text field in the keyword table. I can compare data using script like AsP spliting the items by comma or space, but that can be too slow and use up a lot of RAM. Another solution would be to use a table to hold the references but that would affect performance dramatically because of the large number of records created and storage space used. One example, if I have 60.000 keywords and each keyword has an avereage of 200 references, I would have to generate 12 milion records.
I want to know if there is a function or routine in SQL server to compare matched references on the fly in the server between two or more fields and how should I do it.
In addition, in this scenario, how should a clustered index help?
Thanks
Rodrigo
I have 2 database, databaseA and DatabaseB with same table structure.
I change lot of table structure in databaseB.
So i want compare what field that i add or delete in databaseB.
it's like what field there are in databaseB and not found in databaseA or nothing in databaseB but exist in databaseA.
How can i do that?
I have a SmallDateTime data type field. The SmallDataTime contains day/month/year hour:minuteeconds AM/PM. What I wanted to do is create a store procedure that will only take the month of the field and compare that to my variable. So for example, I wanted to something like the following:
@month varchar(50) = "3"
SELECT * FROM myTable WHERE monthField = @month
Any help is much appreciated.
sorry, this is probably a really basic question ..........
is there any way to enable the bit data type in SQL Server to accept nulls?
thanks
nyoobee
Hi all,
I have a table, named systems, with fields as follows.
field datatype
___________________
system varchar(50)
modelID int
.... ....
... .... etc...
The modelID field is related to the Models table. Therefore, the value it expects is a number that already exists in a Models.modelID field.
In the systems table, I allow nulls for the model ID field. If I edit the table directly through SQL front end it allows me to enter a system name for example and leave modelID value as NULL.
My problem occurs when I try inserting into the table from ASP. Whether I leave the value I'm passing to the stored procedure for insert blank or NULL it gives me problems.
Is this a typical issue that can be resolved with a simple property change?
Please help me resolve this, I need to have the ability to leave certain fields empty even though they are related to other tables. Is that possible?
Thank you for any help in advance.
I have a column called person.last_name, and I want to set this field to NULL if person.last_name ='test'///
I have tried this but don't work: ISNULL ({person. last_name},'<>"test'))
May i know how to generate a SQL query to set a field of row to Null? Thank you.
View 2 Replies View RelatedI'm trying to have a default or null value in the dropdown list of the parameters on SSRS report. The dataset is bound with the Dynamics-AX 2009 AOT query. In the screen shot below you can see that I need a show All option in the dropdown list.
View 7 Replies View RelatedHello all,
Can some one help me with this issue. It seems to be a common question and I have tried a lot of options but nothing seems to work for me.
I have a text box and this is linked to ajax calendar extender. They get a date and I am inserting/updating the date in a sql 2000 database using a stored procdure.
This is the code I use to grab the data from my web form
If txtTrimsEntryDate.Text = String.Empty Then
_oTrim.TrimsEntryDate = System.Data.SqlTypes.SqlDateTime.Null
Else_oTrim.TrimsEntryDate = CDate(txtTrimsEntryDate.Text)
End If
If txtEstCompletion.Text = String.Empty Then
_oTrim.EstCompletionDate = System.Data.SqlTypes.SqlDateTime.Null
Else_oTrim.EstCompletionDate = CDate(txtEstCompletion.Text)
End If
If txtProjectClosedDate.Text = String.Empty Then
_oTrim.CloseDate = System.Data.SqlTypes.SqlDateTime.Null
Else_oTrim.CloseDate = CDate(txtProjectClosedDate.Text)
End If
_oTrim.InsertOrUpdateAProject()
This is the code for the stored Procedure
Dim oSql As clsSqlServer = Nothing
oSql = New clsSqlServer(AppSettings(_dbName))oSql.AddParameter("@projTrimsEntryDate ", _trimsEntryDate, SQLDataType.SQLDateTime, ParameterDirection.Input)
oSql.AddParameter("@projStartDate", _startDate, SQLDataType.SQLDateTime, ParameterDirection.Input)oSql.AddParameter("@estCompletionDate", _estCompletionDate, SQLDataType.SQLDateTime, ParameterDirection.Input)
oSql.AddParameter("@closeDate", _closeDate, SQLDataType.SQLDateTime, ParameterDirection.Input)
Now when it inserts or updates if the value is null then it inserts "01/01/1900" in the database. I want the value to be Null in database.
Is there a way to do this.
Is there any way of excluding an entire row if a particular field contains a null value? Even if other fields in the row aren't null... This is in SQL Server 2005
View 2 Replies View RelatedThere must be a better way to write this where clause. Can somebody help
if @id is null
begin
(bla bla bla) where parentid is null
end
else
begin
(bla bla bla) where parentid = @id
end
I would like to force a column to be null all the time. I cannot alter the table structure or alter the code that inserts data.
create table dbo.tblCustomer
(
CID int IDENTITY(1,1) not null,
Fnamevarchar(20) not null,
Lnamevarchar(20) not null,
Extravarchar(20) null
[Code] ....
So when this is executed the field Extra is always NULL
INSERT INTO tblCustomer (Fname, Lname, Extra)
VALUES ('bob', 'smith', 'ignore'), ('jane', 'doe', 'empty')
update dbo.tblCustomer set Extra = 'something'
If I've understood After triggers correctly the data will be written and the trigger will fire and overwrite. To avoid 2 writes
I could create an INSTEAD OF trigger
CREATE TRIGGER TR_I_Customer
ON tblCustomer
INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON
INSERT INTO tblCustomer
(Fname, Lname, Extra)
SELECT Fname, Lname, NULL
FROM Inserted
END
This will not write the "extra" field twice. However if a new Nullable column were added; it would be v.easy to forget to update the Instead Of trigger. Everything would still work OK but I would be effectively ignoring the new column as well.
What I would like in the instead of trigger is do something like this...
UPDATE INSERTED SET Extra=NULL
Continue with insert without supplying column /value list.
What would be the best way of achieving this, trigger is the only way I could think of?
My tests have shown that if you use BCP to import records with a
date field as " ", it won't work.
Is there a way to get BCP to accept empty date fields?
Using an SQL server and writing ASP code trying to set a numeric field to null.
ie ssql = "UPDATE users SET customer_id = null where name = 'Joe'"
Keeps on generating a syntax error
ad1 ad2
-----------------------------
younge joe
null null
youn null
want a result like that. anyone can help me to do it?
ad1+ad2
-----------------------
younge joe
null
youn
Hi everyone,
I have two tables - caseinfo and steps.
Caseinfo shows when a particular case entered and exited a particular project. If the project hasn't ended yet, then the end date is NULL.
Steps shows the steps the case has gone through and the dates of those particular steps.
I need to join the tables to show the steps the case went through during a particular project, but I'm having trouble with the NULL values in the end dates.
If I join the tables so that the step date is between the start and end dates of the project, then I get no step information for the cases where the end date is NULL (that is, where the project hasn't ended yet).
Does anybody have any ideas?
Here are my tables, the query that shows the main idea (with the wrong result), and my expected results.
Thank you for reading.
--- create sample data
set dateformat ymd
declare @caseinfo table (caseid int, startdate smalldatetime, enddate smalldatetime)
insert @caseinfo
select 10, '2006-12-23', '2006-12-27' union all
select 20, '2006-12-23', NULL union all
select 30, '2006-12-23', NULL union all
select 40, '2007-1-15', '2007-3-4'
declare @steps table (caseid int, stepnumber int, stepdate smalldatetime)
insert @steps
select 10, 1, '2006-12-24' union all
select 10, 2, '2007-1-3' union all
select 10, 3, '2007-2-5' union all
select 20, 1, '2006-12-26' union all
select 20, 2, '2007-1-7' union all
select 20, 3, '2007-1-9' union all
select 30, 1, '2007-1-14' union all
select 40, 1, '2007-1-23' union all
select 40, 2, '2007-3-2' union all
select 40, 3, '2007-4-16'
--- the main idea (with the wrong results)
select *
from @caseinfo c
left join @steps s on s.caseid = c.caseid and s.stepdate between c.startdate and c.enddate
--- expected result
declare @expresult table (caseidexp int, startdateexp smalldatetime, enddateexp smalldatetime, stepnumberexp int, stepdateexp smalldatetime)
insert @expresult
select 10, '2006-12-23', '2006-12-27', 1, '2006-12-24' union all
select 20, '2006-12-23', NULL, 1, '2006-12-26' union all
select 20, '2006-12-23', NULL, 2, '2007-1-7' union all
select 20, '2006-12-23', NULL, 3, '2007-1-9' union all
select 30, '2006-12-23', NULL, 1, '2007-1-14' union all
select 40, '2007-1-15', '2007-3-4', 1, '2007-1-23' union all
select 40, '2007-1-15', '2007-3-4', 2, '2007-3-2'
select *
from @expresult
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
Visual Basic 2005 Professional Edition:
I have an SQL database table that includes a BirthDate field. I would like to have this field as optional when adding a record, but, SQL insists on throwing an exception if the field is null.
Hello,
After the "DAYS" column, some fields are coming null. We don't want it to come null. Beside this, i.e; in the "MONDAY"column, the same "NAMESURNAME" is coming more then one. How can I do the grouping by "NAMESURNAME"?
MachType DayPeriod Monday Tuesday Wednesday Thursday Friday Saturday
Negative- || NIGHT || NULL || CAN AK || NULL || FÄ°RDEVS ATEÅž || NULL || NULL
Negative- || NIGHT || NULL || NULL || CEM TEK || NULL || NULL || NULL
Negative- || NIGHT || NULL || NULL || NULL || FÄ°RDEVS ATEÅž || MÃœKREMÄ°N EKELÄ°K || NULL
Negative- || NIGHT || NULL || CAN AK || CEM TEK || FÄ°RDEVS ATEÅž || NULL || NULL
Negative- || NIGHT || NULL || NULL || NULL || NULL || ÖMER CANOĞLU || NULL
select MachineType,DAYS,
CASE WHEN DIALYSISDAY= 'MONDAY'THEN NAMESURNAME END AS MONDAY,
CASE WHEN DIALYSISDAY= 'TUESDAY' THEN NAMESURNAME END AS TUESDAY,
CASE WHEN DIALYSISDAY= 'WEDNESDAY' THEN NAMESURNAME END AS WEDNESDAY,
CASE WHEN DIALYSISDAY= 'THURSDAY' THEN NAMESURNAME END AS THURSDAY,
CASE WHEN DIALYSISDAY= 'FRIDAY' THEN NAMESURNAME END AS FRIDAY,
CASE WHEN DIALYSISDAY= 'SATURDAY' THEN NAMESURNAME END AS SATURDAY
from
(SELECT SICK.MACHINETYPE,SICK.NAMESURNAME ,
CASE
WHEN SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' THEN 'NIGHT' ELSE '' END AS DAYS,
CASE
WHEN SICK.MACHINETYPE= 1 THEN 'Negative-'
WHEN SICK.MACHINETYPE= 2 THEN 'B(+)'
WHEN SICK.MACHINETYPE= 3 THEN 'C(+)B(+)'
WHEN SICK.MACHINETYPE= 4 THEN 'C(+)' END AS MachineType,
CASE
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 1 THEN 'MONDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 2 THEN 'TUESDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 3 THEN 'WEDNESDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 4 THEN 'THURSDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 5 THEN 'FRIDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 6 THEN 'SATURDAY' END AS DIALYSISDAY
FROM SEANCE,SICK WHERE SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' and
SUBSTRING(SICK.EUCLID,1,5)= '18016' AND SEANCEDATEBETWEEN '20080301' AND '20080307'
AND SICK.EUCLID=SEANCE.EUCLID )AS T ORDER BY MachineType
I need to insert a null valvue when the user does not impute any text.
here is my code
If cell_phone.Text = "" Then
cell_phone.Text = "dbnull.value"
End IfDim mySqlConnection As New SqlConnection
mySqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings("Call_ListConnectionString").ConnectionString
Dim strSQL As String = "INSERT INTO Employees(Clock_ID, Last_Name, First_Name, Cell_Phone, Home_Phone, Work_Phone, Email, Primary_Day_Phone, Primary_Night_Phone, Blackberry_Number) VALUES ('" & clock_id.Text & "','" & last_name.Text & "','" & first_name.Text & "','" & work_phone.Text & "','" & home_phone.Text & "','" & cell_phone.Text & "','" & email.Text & "','" & prime_day_lst.SelectedValue & "','" & prime_night_lst.SelectedValue & "','" & blackberry.Text & "')"Dim mySqlCommand As New SqlCommand(strSQL, mySqlConnection)
mySqlConnection.Open()
mySqlCommand.ExecuteNonQuery()
mySqlConnection.Close()
THanks
Mike
I have a datetime field in a database which I am programmatically inserting values into. The field can be null or not null. However, I am having problems inserting NULL as part of my SQLCommand.
The value is pulled from a text box (linked to a calendar extender) and when I select a value it is being inserted fine. If I just leave the box blank though, I want the field to be set to NULL. I have tried adding in ,DBNULL.Value, as part of my VALUES(…) string, but this throws an exception. I Have tried just inserting ‘’ but that also throws an exception (“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value�), so I don’t know how I can insert this value when the field is blank?
Can anyone shed some light please? Thanks
hi all!I have a task, for example, to create a record for bill. I have table which represents this bill entity (Bill_ID, Amount, CreationDate, ExposureDate, PaymentDate)In table definition date fields allow null. I would like to create bill, which means insert record: (new_bill_id, 1000, 2007.12.11, null, null) But it couses exception. Smth like: System.Data.SqlTypes.SqlTypeException, date should be not null. How could I do it?Please advice!
View 5 Replies View RelatedHi,
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
Hi,
I am building a website in ASP.net C# for a university project, and would like to search a table (Member) for a field (UserName) using a session variable Session["sUserName"]. If that field is null, then I would like to insert that session variable into the field to start to create a new user. However, I am getting errors saying that I am using invalid expression terms. My code is;
//Create the Command, passing in the SQL statement and the ConnectionString queryString = "SELECT UserName FROM Member WHERE (UserName = @myUsername); ";
SqlCommand cmd = new SqlCommand(queryString, sqlConn);cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
//If UserName is null, display confirmation, else display errorif (UserName == null) ;
{UserNameCheckLabel.Text = "Username okay";
String queryString = "INSERT INTO Member (UserName) VALUES(@myUsername); ";SqlCommand cmd = new SqlCommand(queryString, sqlConn); cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
}else;
{UserNameCheckLabel.Text = "That username is in use";
}
I have a feeling I should be checking the database for the UserName, but I'm not sure whether to put this in the SELECT statement part or as a method... I would be most grateful for any advice!
Many thanks,
Chima