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
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), ' ', '-') ) ...
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.
hi i want to select * from table1 where name =petter?now if there is many type of petter in table linke PETTER , Petter And petter which record will come in display?if i want all this three (PETTER,Petter,petter) will come in display which command is for this ??? regard
I have a scenario which i must check if the value of the inputted word is exact (case sensitive) in a string. And print the word if correct. using a user defined function.
Example:The quick brown fox jump over the lazy dog.
If the user enter "jump" it must print "jump". If the user enter "Jump" it must print "jump" which is the correct word.
I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using....
Code Snippet
WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime) >= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore)
Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected.
Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter.
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
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
I have a table called SrcReg which is having a column name called IsSortSeqNo smallint. I am mapping this column in SSIS and the problem comes when I try to execute against different database which has this table but the column name as ISSortSeqNo. I mean both databases having same name but one with upper case. So SSIS fails executing due to meta validation issue.Is there any way to check whether the column name is in small case or upper case through query?
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
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
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)
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.
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.....
Hello again! I'm having yet another problem with my code.
CASE Kit WHEN 'Y' THEN '(Kit)' WHEN 'N' THEN '' END + ' ' + CASE cream WHEN 'Y' THEN '(cream)' WHEN ' N' THEN '' END + ' ' + CASE Phone WHEN 'Y' THEN '(Phone)' WHEN 'N' THEN '' END
The problem that I am running into is that if one of the values comes up as NULL the whole field is NULL. am i doing something wrong?
SELECT NAME, CASE WHEN PHONE IS NULL THEN '-' ELSE PHONE END AS PHONE FROM PATIENT WHERE PHONE = '-'
If I am switching the NULL values to hyphens (-), why am I not getting any records when I look for hyphens in the WHERE clause? The script is not as simple as this or else I would just check for NULL values. I really need to switch NULL values to something I can pass on to a variable and query on it from Reporting Services.
I am trying to setup a query that receives input variables one being a number which could be null (could receive as string).
What I want to happen is if a number is sent the data is filtered by that number but if it is empty no filtering is performed I think this involves using a case statement in a where clause but can't work out the syntax I am using sql 200 server.
I think the case statement is something like this: (week_no = CASE @week WHEN null THEN '#' ELSE @week END) or maybe I am way off
I considered using like @inNumber + '%' but this would cause the problem of if I inoput 2 it would also return 20's 200's ...
HiConsider two tablesid1 code1----------- -----1 a2 b3 cid2 code2 value----------- ----- -----------1 a 02 a 13 b 1They are joined on the code field.For each code, I want the maximum corresponding value. If the valuedoesn't exist (corresponding code in second table doesn't exist), I wanta NULL field returned.The result should look like this:code2 value----- -----------a 1b 1c NULLI can't get it to include the NULL row.While there are uniqe ID's in this example, the real life example uses ahorrible four field compound key.Any help would be appreciated.Ger.The above example can be recreated by the following script.DROP table #temp1DROP table #temp2SELECT 1 AS 'id1', 'a' AS 'code1'INTO #temp1UNIONSELECT 2, 'b'UNIONSELECT 3, 'c'SELECT 1 AS 'id2', 'a' AS 'code2', 0 AS valueINTO #temp2UNIONSELECT 2, 'a', 1UNIONSELECT 3, 'b', 1SELECT code2, valueFROM #temp1 t1LEFT JOIN #temp2 t2 ON t1.code1 = t2.code2WHERE CASEWHEN t2.value IS NULL THEN 1WHEN t2.value = 0 THEN 2WHEN t2.value = 1 THEN 3END = (SELECTMAX( CASEWHEN value IS NULL THEN 1WHEN value = 0 THEN 2WHEN value = 1 THEN 3END )FROM#temp2WHEREcode2 = t2.code2)
Code Block SELECT [userID], CASE WHEN [mdlName] = NULL AND [mdnName] = NULL THEN [lstName] + ', ' + [fstName] WHEN [mdlName] <> NULL AND [mdnName] <> NULL THEN [lstName] + ', ' + [fstName] + ' ' + [mdlName] + ' ' + [mdnName] WHEN [mdlName] <> NULL AND [mdnName] = NULL THEN [lstName] + ', ' + [fstName] + ' ' + [mdlName] WHEN [mdnName] <> NULL AND [mdlName] = NULL THEN [lstName] + ', ' + [fstName] + ' ' + [mdnName] END AS [Full Name] FROM [grUser] ORDER BY [Full Name] ASC
The [Full Name] is always null. Any idea why? What I'm trying to do is if mdlName (middle name) and mdnName (maiden name) is null then display the lstName (last name) and fstName (first name) only and so on.
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
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.
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
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
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.
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 ''
I am working on a Function that takes multiple parameters. I have a query that populates a temporary table, and then it processes some logic. My question is, if the parameter is passed as null, I dont want the query to be affected by this null value. Rather, I would like to not pass it at all to the query. So if the parameter is NULL, dont pass it through the query. I have the following but its not compiling right:
SELECT bom.pEngr_BOM_ID , bom.fEngr_Item_ID, det.pEngr_BOM_Detail_ID, 1, bom.Bill_Type, bom.Rev_Ltr, bom.Series_Ltr FROM dbo.Engr_BOM_Control bom WITH (nolock) INNER JOIN dbo.Engr_BOM_Detail det WITH (nolock) ON det.fEngr_BOM_ID=bom.pEngr_BOM_ID WHERE bom.pEngr_BOM_ID=@v_pEngr_BOM_ID AND det.fEngr_BOM_ID=@v_pEngr_BOM_ID CASE WHEN @v_Bill_Type IS NOT NULL THEN AND bom.Bill_Type=@v_Bill_Type END
