VALUE IN FIELD NULL OR VOID STRING!?
Jul 23, 2005
Hi!
I got a column, with different dates in it (Ddata type "nvarchar")...
when running a SELECT on this column, I'm trying to filter those
recordsets out, WHERE this column is NULL (I checked the table, there
are "empty" fields in the column):
"SELECT bla FROM bla WHERE myColumn NOT NULL"
but I still receive those "empty" fields in my resultset ...
so I tried it with:
SELECT bla FROM bla WHERE myColumn <> ''
again, "empty" fields in my resultset ....
what's going wrong there? is there a possibility to check what kind of
value I got in my column!?
Thanks!
Peter
View 3 Replies
ADVERTISEMENT
Dec 30, 2003
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
View 6 Replies
View Related
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
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
Aug 29, 2007
Hi,
What is the difference updating a null value to char/varchar type column
versus empty string to char/varchar type column?Which is the best to do and why?
Could anyone explain about this?
Example:
Table 1 : tCountry - Name varchar(80) nullable
Table 2 :tState - Name char(2) nullable
Table 3 :tCountryDetails - countryid,state (char(2) nullable) - May the country contain state or no state
So,when the state is not present for the country ,i have two options may be - null,''
tCountryDetails.State = '' or tCountryDetails.State = null?
View 9 Replies
View Related
Oct 27, 2015
We have a case where in we should show date based on conditions for e.g if we had a column defined as
col varchar(10) then we would show col as 'NULL' for some condition and actual value when no condition
Normaly date values are stored here e.g under col 20150901 .
Case
when col>'20150901' then 'NULL'
else col
end as Derivedcol
Note this is an extract process and we are presenting data by pumping the data in a table .
Now there is another similar column -colz varchar(10) which stores date but doesnt have case condition so whenever date has no value its shows null which is database null.
So whats the difference between database null and string null ?
How can we show database null for the case condition instead of string "null"?
View 10 Replies
View Related
Mar 18, 2008
hi to all,
i have written SQL-CLR function using C# which will perform some manupulation and will not not return any value(return type is void)
i am creating sql function using SQL script
CREATE FUNCTION dbo.Amex ()
RETURNS NULL
AS CALLER
WITH EXECUTE
AS
EXTERNAL
NAME [AMEX].[UserDefinedFunctions].[Function1];
GO
but i am getting error.
there is something wrong in my sql syntax..can anybody help me?
when creating funtion having signature
public static void Function1()
i am getting error.......................
Error 1 The method "Function1" in class "UserDefinedFunctions" marked as a user defined function must return a scalar value or ISqlReader. SqlServerProject1
can anybody help me?
thanks in advance
Chetan S. Raut
View 4 Replies
View Related
Mar 20, 2014
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
View 9 Replies
View Related
Dec 22, 2006
my goal is to show reports in sharepoint services 3.0 and use this update Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint
i have this problem:
After update my sql server 2005 to sp2 ctp with SQLServer2005SP2-KB921896-x86-ENU.exe and upate reporting services, try to:http://<localserver>:1010/reportserver show the message.
An internal error occurred on the report server. See the error log for more details. (rsInternalError)
Method not found: 'Void Microsoft.ReportingServices.Diagnostics.UserUtil.CleanCurrentUserName()'.
i this problem is in the Microsoft.ReportingServices.Diagnostics.dll i see the method and in version (9.0.3027.0) dont have, and have in 9.0.1399, what i can do to this work rigth
Just try Microsoft.ReportingServices.Diagnostics.dll 9.0.3033.0 still dont working...........
More Details.........
<Header>
<Product>Microsoft SQL Server Reporting Services Version 9.00.3033.00</Product>
<Locale>en-US</Locale>
<TimeZone>GMT Standard Time</TimeZone>
<Path>C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFilesReportServer__12_26_2006_12_16_30.log</Path>
<SystemName>CRM</SystemName>
<OSName>Microsoft Windows NT 5.2.3790 Service Pack 1</OSName>
<OSVersion>5.2.3790.65536</OSVersion>
</Header>
w3wp!library!1!26-12-2006-12:16:31:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.MissingMethodException: Method not found: 'Void Microsoft.ReportingServices.Diagnostics.UserUtil.set_CacheUserName(Boolean)'.
at Microsoft.ReportingServices.WebServer.Global.StartApp()
at Microsoft.ReportingServices.WebServer.Global.Application_BeginRequest(Object sender, EventArgs e)
--- End of inner exception stack trace ---
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing ConnectionType to '0' as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing IsSchedulingService to 'True' as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing IsNotificationService to 'True' as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing IsEventService to 'True' as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False' as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing IsWebServiceEnabled to 'True' as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing ProcessRecycleOptions to '0' as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing WatsonFlags to '1064' as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file.
w3wp!library!1!26-12-2006-12:16:31:: i INFO: Initializing EnableRemoteErrors to default value of 'False' because it was not specified in Server system properties.
w3wp!library!1!26-12-2006-12:16:31:: Unhandled exception was caught: System.MissingMethodException: Method not found: 'Void Microsoft.ReportingServices.Diagnostics.UserUtil.CleanCurrentUserName()'.
at Microsoft.ReportingServices.WebServer.Global.Application_EndRequest(Object sender, EventArgs e)
at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
w3wp!library!1!26-12-2006-12:16:31:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.MissingMethodException: Method not found: 'Void Microsoft.ReportingServices.Diagnostics.UserUtil.CleanCurrentUserName()'.
at Microsoft.ReportingServices.WebServer.Global.Application_EndRequest(Object sender, EventArgs e)
at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
--- End of inner exception stack trace ---
View 3 Replies
View Related
Feb 13, 2006
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
View 1 Replies
View Related
Jul 23, 2005
I don't know enough math to demonstrate that any numerical operationwith a null should yield a null; although I would guess that it's true.I just don't buy it, however, when dealing with strings and nulls. In asimple table with first, middle and last name columns, I would inferthat a null value in the middle name column means the HR person forgotto ask. A zero length string, however, tells me HR did ask and there isno middle name. Regardless of whether HR asked, when I concatenate thethree fields, I can't think of a sound reason why I souldn't get thefirst and last names.Having now started a flame war, I actually have a question: How do Iset the default for any given database or table so that concat nullyields null is permanently off? I have tried exec sp_dboption'myDB','concat null yields null',false (and many variations ondelimiting the parameters) but it doesn't do jack. I can use setconcat_null_yields_null off but that only lasts for the immediatesession.I've tried to follow the thread through the BOL but I'm left scratchingmy head on how to accomplish this.Thanks.Randy
View 7 Replies
View Related
Jan 19, 2000
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
View 1 Replies
View Related
Jun 12, 2007
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.
View 11 Replies
View Related
Mar 26, 2014
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'))
View 7 Replies
View Related
Apr 22, 2007
May i know how to generate a SQL query to set a field of row to Null? Thank you.
View 2 Replies
View Related
May 12, 2004
I want all the null (blank) values returned by a stored procedure to be shown as a string like "n/a", how to do that easily? Thanks
View 2 Replies
View Related
Jul 11, 2001
example
create view v_GuestOrder
as
Select T1.id_Guest,T2.OrderName
from Guest T1
left join Order T2 T2.id_order = T1.Id_order
select * from v_GuestOrder
--
Id_Guest OrderName
-------- -----
1 spoon
2 phone
3
4 tv
I need something similar to
Select
id_Guest,
case orderName
when '' then Null -- Sql server gives error in thsi case
end as orderName
from v_GuestOrder
So I need to assign NULL to OrderName is query return empty string,
it will be treated by Crystal reports as Null
Please help , thanks
View 3 Replies
View Related
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
Jul 1, 2006
Folks, this isn't exactly a 'Getting Started' question, but I couldn't find a more appropriate Application Development forum.
I'm porting an open source PHP application (http://sourceforge.net/projects/gallery) to use SQL Server as a backend. One of Gallery's unit test scripts tests the ability to insert a string containing a NULL character ( ). It's OK if the string is truncated during insertion, just so long as everything before the is there.
The string being inserted looks like:
$testString = "The NULL character should be escaped !";
(Note the between "escaped " and " !")
The error that the Gallery test script is getting is:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'The NULL character should be escaped '.] in EXECUTE("INSERT INTO g2_PluginParameterMap (g_pluginType, g_pluginId, g_itemId, g_parameterName, g_parameterValue) VALUES ('module','unitTestModule',1,'test19476','The NULL character should be escaped !')")
It looks like SQL Server is complaining about the syntax. I've written a much simpler test script in the hopes of reproducing the problem, but I don't know if what I'm now hitting is the same problem or a different one.
My simple script is:
<?php
$localhost = exec("hostname");
$database = "gallery2";
$uid = "g2user";
$pwd = "g2pwd";
$connectString = "Host=PROVIDER=MSDASQL;DRIVER={SQL Server};";
$connectString .= "SERVER=$localhost\sqlexpress;";
$connectString .= "DATABASE=$database;";
$connectString .= "UID=$uid;PWD=$pwd";
$sqlTableDrop = "drop table ljmtemp";
$sqlTableCreate = "create table ljmtemp (col1 nvarchar(100))";
$sqlTableQuery = "select len(col1) from ljmtemp";
// Connect to the db
$db = new COM("ADODB.Connection") or die("Cannot start ADO");
$db->open($connectString);
// Drop & recreate the table
$db->Execute ($sqlTableDrop);
$db->Execute ($sqlTableCreate);
// Insert the test data
//$testString = "The NULL character should be escaped !";
$testString = "This is a test string.";
$res = $db->Execute("insert into ljmtemp (col1) values ('$testString')");
if (!$res) die ("INSERT failed");
// Disconnect from the db
$db->Close();
?>
And it results in:
C:MyServer>php testMsSqlInsertNull.php
PHP Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string 'The NULL character should be escaped '.' in C:MyServer estMsSqlInsertNull.php:27
Stack trace:
#0 C:MyServer estMsSqlInsertNull.php(27): com->Execute('insert into ljm...')
#1 {main}
thrown in C:MyServer estMsSqlInsertNull.php on line 27
I'm not sure if this is the same problem as Gallery is reporting or another one.
It looks like somebody is treating the as a string terminator, but when i double the backslash the literal ' ' is inserted into the database.
How can I get SQL Server to insert at least everything up to the ?
Hopefully once I have my simple script working I'll be able to figure out what's wrong in the Gallery test.
Thanks.
View 10 Replies
View Related
Mar 26, 2008
Hello 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.
View 3 Replies
View Related
Jun 12, 2008
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 Related
Jan 31, 2005
There 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
View 9 Replies
View Related
Jan 26, 2015
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?
View 6 Replies
View Related
May 28, 2008
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?
View 3 Replies
View Related
Sep 4, 2003
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
View 2 Replies
View Related
Sep 20, 2006
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
View 6 Replies
View Related
Mar 29, 2007
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
View 1 Replies
View Related
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
May 30, 2006
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.
View 6 Replies
View Related
May 1, 2008
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
View 4 Replies
View Related
Nov 9, 2007
I need to check in my Stored procedure if the information passed is null or empty so I can decided to insert the new value or keep the old. How do I accomplish this please in T-SQL. Thanks in advance.
View 6 Replies
View Related
Jan 12, 2008
Hi:
Trying to insert null value into sql table, but not working, if I use:
if (strMyText.Length == 0) command.Parameters.Add("@Text", DBNull.Value); // or using:("@Text", null), or using:("@Text", DBNull) else command.Parameters.AddWithValue("@Text", strMyText);
When I go back to table, I see the value is: 'NULL', has single quotation mark, suppose to be: NULL
Where is the problem?
Thanks a lot.
Jt
View 3 Replies
View Related
Aug 9, 2005
Hi all, I have some columns in my database which allows null. I want to know if leaving the field to be NULL or storing an empty string into the field, which will take up more space?? if the field type is varchar(100)
View 5 Replies
View Related
