I looked up select case statements and have used them for returning single values, but can't seem to get it working when returning a select statement..
I have my SPROC configured as below, can anyone help me out as to why its not working?
btw previously I had been using something like below, but in this case it wont work as I have to make more changes than just the WHERE genderID = @genderID (hmm, hopefully that makes sense, if not ignore my example lol)
IF @genderID > 2
..
ELSE
........WHERE genderID = @genderID
Hello i wrote this case statement to choose the password for a certain user but seems not to work. I have to get custom1 from another table. select cast(custom1 as varchar(5)) from dbo.newhiresraw where id =userid) when custom1 'C%' then password = abcd4321 when not 'C%' then password = [postalcode] end [postalcode].
Not sure if custom1 = C then password =abcd4321 If custom1 not = C then it will be a zipcode.
SELECT OH.OHONR, CASE WHEN LV.LVTYP='VIL' THEN LV.LVT30 ELSE NULL END AS LE, CASE WHEN LV.LVTYP='SÄL' THEN LV.LVT30 ELSE NULL END AS LS INNER JOIN LV ON OHONR=LVONR
The result gives one row for each hit on EITHER of the CASE statements. (Two rows if both case stantements are true) OHONR LE OHONR LV
I want to have one row with the two fields after each other. OHONR LE LV
Hi, I require some help with a SP. I have written the following CASE block but it is giving me an syntax error with Case statement.
Here @loc_id, @id_val and @inid are input parameters to the SP.
Basically what I am doing is I am selecting the column from table based on the input parameter @loc_id I want to use Case and avoid IF statements as Case will reduce the execution time.
If any of you have faced such problem and have a sloution then do let me know.
-- SP section starts
CASE @loc_id WHEN 1 THEN select @id_val = val1 from id_val where name = @inid WHEN 2 THEN select @id_val = val2 from id_val where name = @inid WHEN 3 THEN select @id_val = val3 from id_val where name = @inid WHEN 4 THEN select @id_val = val4 from id_val where name = @inid WHEN 5 THEN select @id_val = val5 from id_val where name = @inid WHEN 6 THEN select @id_val = val6 from id_val where name = @inid WHEN 7 THEN select @id_val = val7 from id_val where name = @inid WHEN 8 THEN select @id_val = val8 from id_val where name = @inid WHEN 9 THEN select @id_val = val9 from id_val where name = @inid END -- SP section ends
Can someone tell me what is wrong with my syntax? I am getting several errors:
Incorrect syntax near keyword 'CASE' Incorrect syntax near keyword 'WHEN'
Incorrect syntax near '@Tablename'
CREATE PROCEDURE al_readcampsignup
@User_ID int,
@Pagenumber smallint,
@Tablename varchar(10)
AS
CASE @Mypagenumber WHEN 1 THEN SELECT Blah FROM tempcampsignup WHERE Camp_ID = @User_ID WHEN 2 THEN SELECT Blah FROM @Tablename WHERE Camp_ID = @User_ID WHEN 3 THEN SELECT Blah FROM @Tablename WHERE Camp_ID = @User_ID END
I am attempting to mask a user's SS# based on Users.DisplaySSN. I have gone over the code time and time again and can't find where the error is.
Error I'm getting: Incorrect syntax near the keyword 'AS'.
Here's where the error lies: CASE (SELECT DisplaySSN FROM Users WHERE Users.UserID = @UserID) WHEN False THEN 'xxx-xx-xxxx' + right(SocialSecurityNumber,0) AS SocialSecurityNumber ELSE vwEmp.SocialSecurityNumber END
Hello all,I belive, my problem is probably very easy to solve, but still, Icannot find solution:declare @iintdeclare @zintcreate table bubusilala ([bubu] [int] NOT NULL ,[gogo] [int] NOT NULL ,[lala] [varchar] (3) NOT NULL )insert into bubusilala (bubu,gogo,lala) values (1,2,'ala')insert into bubusilala (bubu,gogo,lala) values (10,20,'aca')insert into bubusilala (bubu,gogo,lala) values (100,200,'bbb')insert into bubusilala (bubu,gogo,lala) values (11,21,'ccc')insert into bubusilala (bubu,gogo,lala) values (12,22,'abc')insert into bubusilala (bubu,gogo,lala) values (13,23,'cbd')set @i = 10set @z = 2select * from bubusilalawhere bubu in (case when @i > @z then (1,2)when @i < @z then (10,13) end)and gogo like '%a%'I get error, that statement is wrong in case near ','.I supose, it is not possible, to get from case a group of values.But why then, this works:select * from bubusilalawhere bubu in (case when @i > @z then (1)when @i < @z then (select gogo from bubusilala) end)and gogo like '%a%'This data are totaly simplified.agrh ... any ideas??Thank You in advance,Mateusz
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.
I cannot seem to find the syntax to combine IN + CASE in a WHERE clause
WHERE ses.BK_MS_SESSION <= '2015-03' AND vis.CAT_DRAW_STATUS = (CASE ses.BK_MS_SESSION WHEN '2015-03' THEN vis.CAT_DRAW_STATUS ELSE CASE stat.BK_MS_VISIT_STATUS WHEN 'T' THEN 'X' ELSE vis.CAT_DRAW_STATUS END END ) IN ('D','R')
I am a newbie to SSIS and looking for answer to a simple search/replace style question.
I am building a derived column and looking for some expression syntax that can search for several strings in one column and set a value in a new column.
A bit like a case statement:
CASE ProductLine WHEN 'R1' THEN 'Road' WHEN 'M1' THEN 'Mountain' WHEN 'T1' THEN 'Touring' WHEN 'S1' THEN 'Other sale items' ELSE 'Not for sale' END,
The twist is that 'R1','M1','T1','S1' could feature anywhere in the string that is ProductLine.
I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.
similar to this SQL case statement
CASE ProductLine WHEN 'R1' THEN 'Road' WHEN 'M1' THEN 'Mountain' WHEN 'T1' THEN 'Touring' WHEN 'S2' THEN 'Other sale items' ELSE 'Not for sale' END,
the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.
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 ... ?
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
In a select statement select_list, if one of the columns in the result set is NULL, I need to substitute a constant value else I want to return the value in the column. Would I use an IF_THEN_ELSE or CASE expression and how would that look? Thanks, Robert
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,
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
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.
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 '<'.
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,
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:
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!
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
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;))
Select Case l.GLTransactionTypeID When 'Asset' --AND l.GLTransactionDate BETWEEN convert(datetime,'1/1/2007')AND convert(datetime,'12/31/2007') and l.GLTransactionSource=@assetID Then Begin Set @Cost=l.GLtransactionAmount end end from LedgerTransactions l,FixedAssets f where l.GLtransactionsource=f.assetID and l.GLtransactionsource= @assetID
I have an application that at the begining a user will login with a user name and password which is stored in the database. The SQL statement is as follows:
"SELECT id_employee FROM employee WHERE employee_number='" + txtUserName.Text + "' AND passWord='" + txtPassword.Text + "'";
For testing purposes I have set the password to the word test. The problem is, if the user enters in TEST or TeSt or TESt it will grant them access. How do I set it to force the correct case?
INSERT INTO #rpt(Hic, DtImported, Source, PlanID, LastName, FirstName, MemID, CaseStatus) SELECT s.Hic, s.DtImported, s.Source, dbo.LastErolledPlan_C_D(s.Hic,s.IsPart_C) as PlanID, m.LastName, m.FirstName, dbo.GetMemID_PartC_D(s.Hic,s.IsPart_C) as MemID, s.CaseStatus = CASE WHEN CaseStatus ='1' or CaseStatus='0' THEN 'New' CASE WHEN CaseStatus <>'1' or <> CaseStatus <>'0' Old
ElSE 'No Valid' End
from tbEsccSuspects s inner join tbPDMMembers m on s.Hic = m.HicNumber Where s.EUFStatus = 1 -- AND DtEUFSent is NULL
what I am doing wrong in the case statment that I am getting an error. ?????
