June 10, 2009 at 8:00 am
Hi,
I am trying to select a few columns in my view where by if a particular column is NULL then it should use an alternative column. The real problem here is that there are three different scenarios whereby if there is data in a column, i will like to create a temporary column as to tell me that the data is populated from whichever colum is returning the result.
Please find below a brief explanation fron the query:
CASE
WHEN dbo.Episodes.[Onset Date] IS NULL THEN dbo.Episodes.[Referral Date] END AS [Date of Onset], [this will also be a new column titled referall date]
WHEN dbo.Episodes.[Onset Date] IS NULL AND dbo.Episodes.[Referral Date] IS NULL THEN dbo.Specimens.Specimen_Date AS [Date of Onset ] [this will be a new column titled Specimens]
END
I hope this makes sense. I tried to find out if i can use an IIf statement but no luck.
Any advice or help will be appreciated
June 10, 2009 at 9:36 am
You cannot determine the count and names of your columns within a CASE statement. SELECT statements always require to return table structures with same columns and data types for each row.
You can return both (all) columns and use CASE to fill them.
June 11, 2009 at 6:05 am
Dunno if this helps you on the way:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[#Table_1](
[F1] [nchar](2) NULL,
[F2] [nchar](2) NULL,
[F3] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO #Table_1 VALUES('A','B',1)
INSERT INTO #Table_1 VALUES(null,'B',2)
INSERT INTO #Table_1 VALUES('A',null,3)
INSERT INTO #Table_1 VALUES(null,null,4)
SELECT F3,F2,F1,
-- Computed Field
CASE
WHEN F1 is not null AND F2 is not null THEN F1 + F2
WHEN F1 is null AND F2 is not null THEN F2
WHEN F1 is not null AND F2 is null THEN F1
ENDAS ComputedField
FROM #Table_1
DROP TABLE #Table_1
I used a ComputedField for a resultset based on the value of F1 and F2, but you can easily use another column of the table here.
But maybe I do not understand your problem correctly.
Another method would be to use UNION between 2(or more) SELECT statements, in which you select each different situation you would want.
Greetz,
Hans Brouwer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply