Why Is My Select...Case Generate Only NULL Value?
Nov 9, 2007
Here's what I have and it's not working.
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.
Any help is much appreciated.
View 4 Replies
ADVERTISEMENT
Oct 8, 2015
how to generate null row two different date in data in sql ,,for example as below
name age DOB
ram 20 04/05/1988
kumar 30 04/05/1988
NULL NULL NULL
jonh 25 05/06/1988
View 7 Replies
View Related
Nov 2, 2000
I am having trouble with the following:
SELECT CASE name
WHEN NULL THEN 'No name'
ELSE name
END
It is not working as I'd expect. The column displays <NULL> instead of 'No
name'. Any thoughts?
TIA
View 1 Replies
View Related
May 19, 2006
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 How to check for null in CASE(fieldname) WHEN ???
View 1 Replies
View Related
Apr 3, 2008
Hey all,
1) I have a report with many parameters that I want users to be able to pick from. Allow them to pick 1, many or all to build their report dynamically. I'm all set on the TSQL side, but on the Reporting Services side I have to allow each parameter to be null with a default of NULL. In by doing this, the report will auto run, which I do not want to happen. The only resolution I've found thus far was by adding a parameter that does nothing, with a NULL default value. Yet It sticks out like a sore thumb on the report and I want to get rid of it. If I check in "Hidden" in the parameter options, my report errors out stating that the parameter requires a value.
2) Is it possible to have a parameter that has available values from a dataset have a NULL checkbox like those of parameters that do not have available values?
3) Is it possible to add back/forward buttons inside of a report instead of just at the report header by default?
Thanks!
View 8 Replies
View Related
May 6, 2008
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?
View 11 Replies
View Related
Oct 22, 2013
Code below:
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.
View 6 Replies
View Related
Mar 17, 2008
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 ...
View 3 Replies
View Related
Jul 23, 2005
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)
View 5 Replies
View Related
Jun 19, 2015
DECLARE @I1 VARCHAR(5),
@I2 VARCHAR(5)
;
WITH cte
AS (SELECT 1 AS i,
'val1' AS j UNION ALL
SELECT 2,
'val2')
[code]....
Why @i1 is null but not @i2 ? I'm trying to assign values after grouping by the column.
View 17 Replies
View Related
Jul 21, 2004
Can someone help me with this :
Case WHEN ISNULL (dbo.BLH.n1name,'NOTHING')= 'NOTHING' then dbo.BLH.coname else dbo.BLH.n1name End as CustName
as the strored proc is not returning value when the fileds is blank
cheers
View 2 Replies
View Related
Sep 21, 2015
I'm trying to get a result set without the NULLs. Here is the code I'm using. I'd like the results to look like:
17285204 90471 090471
17285204 90715 090715
17285204 99396 099396
17285204 99420 099420
17285204 90471 090471
17285204 NULL G0444
create table #Test
(
AppNum varchar(10),
CPT varchar(10),
src char(1)
)
insert into #Test(AppNum, CPT, src) values('17285204','090471','b')
[Code] ...
View 5 Replies
View Related
Jun 3, 2008
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
View 3 Replies
View Related
Aug 5, 2015
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), ' ', '-') )
...
But it did not like the "IS NOT NULL".
View 9 Replies
View Related
Sep 15, 2015
The below code returns a NULL for all the Differential backups. I am trying to return the word Differential when there is a NULL because I can tell from when the NULLs occur that those are my differential backups. How can I get the result I want?
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE
WHEN msdb..backupset.type = 'D' THEN 'Database'
[Code] .....
View 9 Replies
View Related
Jul 30, 2002
Here is the example
state
-----
NY
NJ
CA
NY
NC
NJ
CA
IL
CA
Can we generate a result like this with select statement
state No
----- ---
CA 1
CA 2
CA 3
NJ 1
NJ 2
NY 1
NY 2
IL 1
NC 1
View 1 Replies
View Related
Jun 15, 2015
I'm running the following test query on a single table:
SELECT sph.datestamp, sph.stocksymbol, sph.closing, DATENAME(dw, sph.datestamp),
CASE DATENAME(dw, sph.datestamp)
WHEN 'Monday' then 'Monday'
ELSE (SELECT CAST(sph2.datestamp AS nvarchar) FROM BI_Test.dbo.StockDB AS sph2 WHERE sph2.DateStamp = DATEADD(d, -1, sph.datestamp) AND sph2.StockSymbol = 'NYA')
END AS TestCase,
[Code] ....
And here's an example of the output I'm getting:
Why the exact same subquery in the THEN of the second CASE statement is returning NULL when the first one completes as expected?
View 7 Replies
View Related
Aug 3, 2007
My problem is one of my query is returing party_codes . now i also want a column which returns serial numbers along with it . the serial numbers are not stored anywhere. they should be autogenerated. My query is combining two different databases and its using union in it so i can not use count in it . Is there any other way i can acheive . for eg Now my query output is party_code ---------- R06048 R06600 R06791 (3 row(s) affected) I want it like party_code serial number ---------- ------------- R06048 1 R06600 2 R06791 3 (3 row(s) affected)
The serial number column should be auto generated in the select statement it self Is there any system rowid i can use . Please suggest
View 3 Replies
View Related
Oct 22, 2015
I had to enable identity_insert on a bunch of tables and I have already done that. Now I need to modify my insert into select * from statements to include column list names along with identity columns for select as well as insert statements. The DDL is same but they are both different databases.There are almost 100 tables that it needs to be modified. Is there a way we can generate scripts for insert and select for each individual table along with their column lists including the identity column?
View 7 Replies
View Related
Jan 23, 2008
Hi,I have a query like this :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...The problem is that I don't want to return the results where x3 isNULL.Writing :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ... AND x3 IS NOT NULLdoesn't work.The only solution I found is to write :SELECT * FROM((SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...) AS R1)WHERE R1.x3 IS NOT NULLIs there a better solution? Can I use an EXISTS clause somewhere totest if x3 is null without having to have a 3rd SELECT statement?There's probably a very simple solution to do this, but I didn't findit.Thanks
View 7 Replies
View Related
Apr 3, 2015
Is there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?
View 4 Replies
View Related
Jul 7, 2005
Hi, i have just written this:
select distinct department = case department
when 'ict' then 'Partnership Office'
when 'finance' then 'Partnership Office'
when 'Employee Development' then 'Partnership Office'
when 'Contract Compliance & MIS' then 'Partnership Office'
when 'Quality, Standards & Effective' then 'Partnership Office'
when 'Partnership administration Exe' then 'Partnership Office'
when 'Partnership Executive' then 'Partnership Office'
when 'Personnel' then 'Partnership Office'
when 'Operations' then 'Partnership Office'
when 'Quality Standards and Effectiv' then 'Partnership Office'
when 'Marketing and Communications' then 'Partnership Office'
when 'Marketing and Information' then 'Partnership Office'
else department end from personnel_records
is there some way to do this on one line, like
...
when in ('ict','finance','personnel', etc etc) then 'Partnership Office'
else
... ?
TIA
View 2 Replies
View Related
Feb 8, 2007
hi, this is my procedure.
CREATE procedure yard_retrieve
(
@yard varchar(50),@vessel varchar(50),@customer varchar(50)
)
AS
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@yard and category_code=(select category_code from categoryMaster where category_description='Yard') )
select 'found' as result
else
select 'not found' as reault
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@vessel and category_code=(select category_code from categoryMaster where category_description='vessel'))
select 'found' as reault1
else
select 'not found' as reault1
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@customer and category_code=(select category_code from categoryMaster where category_description='customer'))
select 'found' as reault2
else
select 'not found' as reault2
end
GO
if i run this procedure i need the result as following
result result1 result2
found notfound found
is this posible to do using select case ? please help me to get my need please
View 4 Replies
View Related
Sep 19, 2007
I need to do the following so that I can convert a Null value to 0 in my db... however I am getting errors "Incorrect Syntax near '='.
Facilities.PortfolioID AS PortfolioID =
CaseWhen Facilities.PortfolioID = Null then Facilities.PortfolioID = 0
End,
View 5 Replies
View Related
Jan 17, 2008
Hi, i've got this stored procedure:
@LidFederatieNummer varchar(20),
@ClubID int,
@LidClubNummer varchar(50),
@PersoonNaam varchar(100),
@ClubStamnummer varchar(10),
@ClubNaam varchar(50),
@SeizoenID int,
@ShowAllJN bit,
@LidFederatieJN int,
@CategorieID int
AS
BEGIN
IF @CategorieID IS NOT NULL BEGIN
DECLARE @LicentieVerplicht int
SET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
END
IF (@ShowAllJN = 0)
BEGIN
SET RowCount 100
END
CASE @LicentieVerplicht
WHEN '1' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 1
Order By Persoon
END
WHEN '2' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN)
ORDER BY Persoon
END
WHEN '3' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 0
Order By Persoon
END
According the value of my @LicentieVerplicht variable:
DECLARE @LicentieVerplicht intSET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
My where clausule should change... how can i achieve this cause my case is not working because it is not inside the select statement
View 4 Replies
View Related
Apr 13, 2006
Hi everyone,
I have the following problem withj this query:
SELECT DocNumber,
Title,FirstName,LastName,Paper,
ANNO,Location,Keywords,
URL= case WHEN URL IS NULL THEN
'|<a
href=''https://illiad.library.nova.edu/illiad/FNN/illiad.dll?ILLiadRedirect=OpenURL&genre=thesis&isbn='+CONVERT(varchar,DocNumber)+'&title='+CONVERT(varchar,Title)+'&rft.pub='+CONVERT(varchar,Program)+'&rft.edition='+
CONVERT(varchar,Paper) +'&date='+ CONVERT(varchar,ANNO)+
'&aulast=' + CONVERT(varchar,Lastname) + '&aufirst=' +
CONVERT(varchar,Firstname)+
'&url_ver=Z39.88-2004&rfr_id=info:sid/MARPS:Practicum''target=''blank'')>
Order through ILLiad</a>' ELSE URL END
FROM DocTable1 WHERE FREETEXT....
When URL is null, I don't get the string showing. It works properly only in the query analyzer.
Any ideas?
Christian
View 3 Replies
View Related
Nov 21, 2001
I'm using a 'searched case' however I don't care which method I use.
I'm open to using anything....
I just want to correct a problem in my pivot table where I need to create a ficticous eventdate for 'S' records, those that do not have an event date in the past 7 days.
-- eventdate is a datetime column
select User
,res =
case res --event result
when 'S' then '0' --S generic non-event
when 'SUC' then '1' --successfull event
end
,eventdate =
case eventdate
when enventdate < getdate()-8 then getdate()-8
end
from eventlog_1 --my view of events
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '<'.
TIA
JeffP...
View 2 Replies
View Related
Jul 7, 2005
I am doing a mail merge from data returned from a query. My problem is one of my fields is to long for the document. I want to be able to break up the line into 2 sections. I'm assuming I can do this within the case statement but am unsure how.
This is the code I have. I want the break to come before the "at".
case
when rb.building in (9420,420) then 'DEERFIELD SUMMER SCHOOL at BAKER WOODS #99'
when rb.building=9722 then 'JAMES SUMMER SCHOOL' 'at JOHN MARSHALL MS'
when rb.building=9723 then 'NORTHWEST SUMMER SCHOOL at CRISPUS ATTUCS MS'
when rb.building=9717 then 'BROAD SUMMER SCHOOL at SIDENER MS'
when rb.building=9715 then 'MANUAL SUMMER SCHOOL at MCFARLAND MS'
else upper(trim(rb.building_name)) end,
View 2 Replies
View Related
Sep 7, 2006
Hey guys, i'm trying to use a CASE within a select but i'm not sure how to get started.
Here is my select so far, i'm inserting into a temp table that is used within a loop to gather data from my trigger:
Code:
insert into @temp_par
select CONVERT(varchar(20),d.PAT_ID),
ep.EXT_PAT_ID,
d.DRUG_DESC_ID,
d.ALLERGY_CD,
CONVERT(varchar(20),d.PAR_DT,120),
ISNULL(d.PAR_TXT, 'NULL'),
ISNULL(REPLACE(m.DRUG_NM,'"','^"'),'NULL'),
ISNULL(m.DOSAGE_FORM_CD,'NULL'),
ISNULL(m.STRGTH,'NULL'),
ISNULL(m.STRGTH_UM,'NULL')
from deleted d --PATIENT_PRIOR_ADVERSE_REACTIONS
join EXTERNAL_PATIENT ep on ep.PAT_ID = d.PAT_ID
join MEDNAME m on m.DRUG_DESC_ID = d.DRUG_DESC_ID
I would like to use a CASE for the d.ALLERGY_CD column. The data in this column is usually a 1,2,3,4,5 but i'd like to convert those to their real data, for example:
Code:
1 = A
2 = B
3 = C
4 = D
5 = E
Any help?
View 3 Replies
View Related
Jan 20, 2005
Limitations: Needs to be single sql statement for exec, no udf/stored proc
Objective: select aggregate count from tblPerson grouped by s_fk, division,Desc, areaDesc with rollup for totals
if mail_goes_to=1, get the s_fk value via tblPerson/tblAffiliation/tblOrg/tblCity/tblCounty
if mail_goes_to=2, get the s_fk value via tblPerson/tblCity/tblCounty, skipping tblAffiliation/tblOrg
What's the best method? A case statement? Would a cross-join work? Thanks so much for any and all advice!
tblPerson: person_pk, affiliation_fk; home_city_fk, mail_goes_to
tblAffiliation: affiliation_pk, organization_fk
tblOrg: organization_pk, city_fk
tblCity: city_pk, county_fk
tblCounty: county_pk, s_fk
For resulting display only:
tblPersonArea: personArea_pk, area_fk, person_fk
tblArea: area_pk, areaDesc, division_fk
tblDivision: division_pk, divisionDesc
View 1 Replies
View Related
Dec 1, 2006
Hello !
For MS SQL 2000/2500
I am having a table ::
User
Ident int NOT NULL,
Numb int NOT NULL,
Info nvarchar(100) NOT NULL,
with this query ::
SELECT Numb, Info, Ident
FROM Users
WHERE (Numb IN (100,200, 300))
ORDER BY Ident
I get ::
Numb | Info | Ident
100 | dgwrg | 1
200 | dgeth yiuyi | 1
300 | Agt hgn ngtn | 1
100 | jttytujky | 2
200 | yjtut iuiu | 2
300 | dgd 2gggwekew | 2
what I need is something like::
SELECT
(CASE WHEN Users.Numb = 100 THEN Users.Info END) AS InfoA,
(CASE WHEN Users.Numb = 200 THEN Users.Info END) AS InfoB,
(CASE WHEN Users.Numb = 300 THEN Users.Info END) AS InfoC,
Ident
FROM Users
WHERE Ident = Ident (for the same Ident)
to get ::
InfoA | InfoB | InfoC | Ident
dgwrg | dgeth yiuyi | Agt hgn ngtn | 1
jttytujky | yjtut iuiu | dgd 2gggwekew | 2
Actually I am getting a NULL value everywhere
of course I cannot use MAX(CASE WHEN ... , or I am getting only a few rows and not all
thanks a ton for helping
View 6 Replies
View Related
Aug 2, 2013
OK so I want to use datepart(month,getdate() with a Case statement with this logic:
SELECT CASE
datepart(month,getdate()='8' then
SELECT *
FROM [Master]
WHERE [Month]='Jul'
END
OK what's wrong with the code?
View 6 Replies
View Related
Aug 2, 2013
The following code is a front end parameter - Quantity but when the user enters '0' it is not returning any rows (there are). Is there something wrong here or to add something?
(Select {?Quantity}
CASE "ALL":
1=1
CASE "Negative":
({ACBALMPK.IRQOH#}<0) OR ({ACBALMPK.IEQOH#}<0)
CASE "Zero":
({ACBALMPK.IRQOH#}=0) OR ({ACBALMPK.IEQOH#}=0)
CASE "Greater than Zero":
({ACBALMPK.IRQOH#}>0;) OR ({ACBALMPK.IEQOH#}>0;))
View 4 Replies
View Related
