Hello everyone,
is there anyway to use an update sub query in a case expression , something like this for exampleselect username,(case when password ='606' then (update users set username = 'me' where id= '3') else password end) from users i have been googling this issue just with no usefull resultsthanks for any replyMahmoud Manasrah
I am one very frustrated beginner. If it were not for wonderful information in this forum I would have taken early retirement by now!
Table contains information about new and departing computer and phone users in several departments which we support. This is an existing table which I'm trying to clean up.
The essential part:
CREATE TABLE [dbo].[HelpDesk_NewUser](
[AutoNumber] [int] IDENTITY(1,1) NOT NULL,
[SubmittedDate] [datetime] NULL,
[FirstName] [varchar](100) NOT NULL,
[LastName] [varchar](100) NOT NULL,
[Department] [varchar](100) NOT NULL,
[StartDate] [datetime] NULL,
[DepartDate] [datetime] NULL
[RequestedBy] [varchar](100) NOT NULL,
[UpdatedBy] [varchar](16) NOT NULL,
[CurrentStatus] [varchar](10) NOT NULL ,
[DateCurrentStatus] [datetime] NULL,
[FormType] [varchar](10) NOT NULL,
) ON [PRIMARY]
There can be more than one record per FirstName, LastName. FormType can be N for new or D for departing.
I want to do this for each record:
Read the FormType and Department from the record with the most recent activity (SubmittedDate) for each user
Convert Department to a 4 character department number
Update CurrentStatus with (FormType concatenated with the 4 character dept number) in all records for that user.
I have created another table called UserMostRecent which contains the most recent record for each user.
I have written a query to do this by brute force (read a record, set local variables, update a record), but I would like learn a simpler way to do it. I don't understand the syntax of CASE because it seems to change depending on where it is used.
Here is what I have tried that does not work. Error is "Incorrect syntax near word CASE"
UPDATE HelpDesk_NewUser
SET DateCurrentStatus = b.DateMostRecent,
CurrentStatus = (b.FormType + a.Department
CASE
WHEN 'Roads Department' THEN '3000'
WHEN 'Engineering and Survey Services' THEN '1900'
WHEN 'Waste Management' THEN '8999'
WHEN 'Kern Air Pollution Control District' THEN '9149'
WHEN 'Environmental Health' THEN '4113'
WHEN 'Building Inspection' THEN '2625'
WHEN 'Animal Control' THEN '2760'
WHEN 'Planning Department' THEN '2750'
WHEN 'Community and Economic Development' THEN '5940'
WHEN 'Resource Management Agency' THEN '2730'
WHEN 'Code Compliance' THEN '2620'
WHEN 'Roads Kern Regional Transit' THEN '8998'
END)
FROM HelpDesk_NewUser a JOIN UserMostRecent b
ON (a.LastName = b.LastName and a.FirstName = b.FirstName)
Thank you, forum participants. You are the best!
An error is entered into the table, across two tables - tblErrors_ER and tblPolicyNumbers_ER - each error generates a PK (ErrorID) and can have any number of policy numbers which will be referenced by its own PK but linked to each error by its FK (ErrorID).I want to display each error in a Gridview in ASP.Net - columns included will be ErrorID, ErrorType, DateLogged from tblErrors_ER and PolicyNumber from tblPolicyNumbers_ER.If an Error has more than one policy number I only want to show the error once in the GridView with the word MULTIPLE under policy number.
tblErrors_ER
---------------
CREATE TABLE tblErrors_ER
{
ErrorID int,
ErrorType varchar(255),
DateLogged datetime,
[code]...
I have changed the Count(*) to Count(tblPolicyNumbers_ER.POlicyNUmber) which gives me the same undesired result as above. I have also left it as Count(*) and the entire CASE expression within the GROUP BY statement as suggest above which generated an error saying I can not use an expression in a group by clause.
If I leave Count(*) = 1 where it is in the original SELECT statement but swap the = for > then something happens, close to what I require but not as intended. It returns:
ErrorID ErrorType DateLogged PolicyNumber
---------------------------------------------------------------
1 Test 08/08/2012 Multiple
2 Test 08/08/2012 Multiple
this would suggest the original syntax is close to being accurate but I can not get it to work.
Here is what I have:
UPDATE A SET ConsumerLogon =
(SELECT email FROM TABLE B
WHERE userNumber = LEFT (A.ConsumerLogon ,CHARINDEX ( '@' , A.ConsumerLogon , 1 ) -1) )
Seems like the subquery expression does not receive ConsumerLogon from the outer query.is this possible to refer to outer query in subquery expression?
I have a table A  and lookup table B.
table A:
| ID | FRUIT | VEGETABLE | GOOD |
--------------------------------------------
|  1 | orange | cabbage    |  no  |
|  1 | apple | lettuce       | yes  |
|  1 | kiwi    | broccoli     |  no  |
|  1 | pear   | kale          | yes  |
table B:
| ID | FRUIT | VEGETABLE |
-------------------------------
| 1 | apple | lettuce      |
| 2 | pear   |  kale        |
If the fruit and vegetable in table A is found in table B, then set the GOOD column = yes, else no.
This is what I have so far.
update tableA
set GOOD =
(case when tableA.id = C.id then 'yes'
else 'no'
end
)
from
(select tableA.id as id
from tableA A
left join tableB B on B.fruit = A.fruit
and B.vegetable = A.vegetable) C
I know i can do a "IF" using expressions, the question is, can i do a CASE?
Thanks!
Hello:Is it possible to use CASE expression in AND condition? i.e.------------------------------------------CREATE PROC spBlah( @id INT, @val INT)ASSELECT * FROM aTableWHERE tableID = @idAND ( CASE @val WHEN 1 THEN otherCol = someValue END CASE @val WHEN 2 THEN otherCol != someOtherVlaue END )-------------------------------------------
View 2 Replies View RelatedI there a way to differ the filtering field through a Case expression in the Where claus?
ex:
Where Case When @StuNum = '' Then S.SSN = @SSN Else S.StuNum = @StuNum End
And ...
The actual field to filter by differs, but I can't seem to find the right syntax to do this.
Any help is appreciated : )
The SUBSTRING function returns @domain.com. I don't want any results to be displayed from my sql statement if it equals @myemail.com. I have other cases too that are not relevant to my question. I'm getting an error that says invalid syntax near <
SELECT DISTINCT CASE CUST_EMLAD_TX
WHEN SUBSTRING(CUST_EMLAD_TX, CHARINDEX('@',CUST_EMLAD_TX), LEN(CUST_EMLAD_TX)) <> '@myemail.com' THEN CUST_EMLAD_TX
END
FROM ...
In the Portal1 case expression in the script at the bottom I would liketo replace where the result 1 is returned, with the substring functionreturned as Portal{SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',Field1) - 8) - (CHARINDEX('tonep', Field1) + 4))}However, I am experiencing errors. I think it is because The substringfunction will not return a number as the case expression expects so Imust incorporate cast or convert, but do not know how. Can you help???SELECT portal1 = CASE WHEN len(Field1) > 5 THEN 1 ELSE '' END,SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',Field1) - 8)- (CHARINDEX('tonep', Field1) + 4)) AS portal,Table.*FROM Table
View 2 Replies View RelatedI'm trying to write a case expression similar to the expression listed below. I basically want to compare the Anniversary date to the current date. Whenever I write the expression like this I get a syntax error.
Msg 102, Level 15, State 1, Line 147
Incorrect syntax near '<'.
Case AnniversaryDate
WHEN AnniversaryDate < GETDATE() Then 'Not Yet'
WHEN AnniversaryDate = GETDATE() Then 'Now"
ELSE 'You Missed it'
END AS Anniversary
Could someone please guide me in the correct syntax for this expression.
Thanks
I would appreciate assistance as to how I should correct the following CASE expression::
BilledCarrier = CASE LEN(cur_billed_carrier) WHEN >0 THEN cur_billed_carrier ELSE 'Not Billed' END
I want to return the cur_billed_carrier when its length is >0 but I can't find the correct syntax. My attempt returns an error at the '>' operator. Thank you.
Hi, all here,
I have a problem with CASE expression in my SQL staments.
the problem is:
when I tried to just partly update the column a , I used the CASE expression : set a=case when b=null then 'null' end
the result was strange: then all the values for column a turned to null.
so what is the problem tho?
Thanks a lot in advance for any guidance.
ALTER PROCEDURE dbo.TEST_TOTALCALLS
(
@varDate as varchar (255),
@StartDate as datetime,
@EndDate as datetime
)
AS
SELECT
CASE @varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME)
END,
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @StartDate) AND (CALLENDTIME <= @EndDate)
GROUP BY
CASE @varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME) ' <---this part gave me an error, because of the comma,
END
ORDER BY
CASE @varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME)
END
The month case is giving me an error. I think it has to do with two expressions in one line.
Anyone know how to combine that into 1 expression? or is there away to work around it?
As I would like to display the month as Name, but group and sort by number.
Thx!~
I have to use the Case expression in my query, so I search arround the web and got the following: SELECT title, price, Budget = CASE price WHEN price > 20.00 THEN 'Expensive' WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate' WHEN price < 10.00 THEN 'Inexpensive' ELSE 'Unknown' END,FROM titles It should run OK base on my research in the internet. But my SQL Server gave me error: syntax error arround '>'.I did several search and many people can use the ">" sign or "<" sign in the Case expression, but I just can't use it in my SQL Server, I can't even use any boolean expression, I can only use values.can anyone help me out? My SQL Server Version is SQL Server 2000 Sevice Pack 4.Thanks!
View 5 Replies View RelatedHi,
Maybe this is just totally wrong but this is what I'm trying to do. I want to dynamically insert a value using a CASE expression while executing an external SP from my current SP.
EXEC uspAddMessage @UserId,
CASE @MyAction
WHEN 'DELETE' THEN 'DELETED TRANSACTION.'
WHEN 'APPROVE' THEN 'TRANSACTION APPROVED.'
WHEN 'REJECT' THEN 'TRANSACTION REJECTED.'
END,
@DateStamp
Keep in mind that this is in my stored proc that is doing the processing. I'm calling this common proc when I complete my transactions.
Thanks,
S
Is it possible? I have a request to create a stored proc that willdynamically add a range to a WHERE clause based on a numeric value of acomment type. If the incoming comment type request is say 10, thewhere clause needs to be set to IN(10,11,12,13,14,15,16,17,18,19)OR ifa 20 is passed in the clause would read IN(20,21.....)So I was thinking that a CASE expression within the proc would be thebest way to go, but have had no luck in finding an example or any otherrelated information regarding CASE exp in a proc.TIABill
View 3 Replies View RelatedDECLARE @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.
I have a scenario where the End User is going to select two parameters. @AgentID and @Location If the End User selects @AgentID parameter, in my WHERE clause, I do not want to check the location condition. By default, the agent knows their location already.
If the End User select @Location parameter, in my WHERE clause, I still want to check the Agent condition.
@AgentID parameter would populate either with "All" or their actual agent# (76754 for example).
@Location parameter would populate either with "All" or their actual location (Birmingham or Bessemer for example)Sample of my current WHERE clause:
WHERE
Â
 gl.ReceiptDate >= @BeginDate and gl.ReceiptDate < DATEADD(D,1,@EndDate)
 AND gl.AgentId = CASE WHEN @AgentID = 'All' THEN gl.AgentID ELSE @AgentID END
 AND gl.Location = @Location
How would I convert an expression like on of these to all upper case first letters with remaining letters lower case? VB has a function for that but sql doesn't seem to. I thought about having a loop go through each character to check for spaces. I've written a couple of similar pieces of code in VB when a while ago, but is there a better way? Thanks :)
Just a couple of typical examples of how the data should appear ~
payment, credit card ==> Payment, Credit Card
butcher & singer ==> Butcher & Singer
Dear all,
I am using sql server 2005.
I have two field firstname and lastname. Actual requirment is if category is doctor then display name as Dr. name else name but when I use following sql stament
Whenever one of the name is null it show name as null that is whenever firstname or lastname is null I get name as null
select @DOCNAME = CASE
WHEN DR_CUST_CATEGORY = 1 THEN 'Dr.' + ' ' + dr_cust_name + dr_cust_lastname
ELSE dr_cust_name + dr_cust_lastname
END FROM DOCTOR_MASTER WHERE DR_CUST_DOCTORCODE =@DOCCODE
Please suggest some ideas or any other idea please suggest
Thank you.
I'm trying to generate the data for a 2-column table, where both columns are defined as NOT NULL and the second column is a uniqueidentifier.
In SQL Server Management Studio, this works fine:insert into table_3(column_a, column_b)
select table_1.column_a, (case when table_2.column_b is NULL then newid() else table_2.column_b end) as column_b
from table_1 left outer join table_2 on table_1.column_c = table_2.column_c
That is, column_b of the SELECT result has no NULL values, and all 35,986 rows are successfully inserted into a previously empty table_3. (If I comment out the INSERT INTO clause and project table_2.column_b instead of "(case ... end) as column_b", the SELECT result includes 380 rows with a NULL in column_b, so I know the case expression plus the outer join are working as expected.)
But when I use the SELECT query as the SQL command in an OLE DB Source component that is connected directly to the OLE DB Destination for the result table, I get this error:There was an error with input column "column_b" (445) on input "OLE DB Destination Input" (420
The column status returned was: "The value violated the integrity constraints for the column.".
And sure enough, when I modify the result table to allow NULL in column_b, truncate it, and re-run the data flow, it inserts the exact same 380 rows with a NULL in column_b among the 35,986 rows.
So what is SSIS doing to screw up the results of the SELECT command???
I have been trying to get the following Selects to work using a case expression in the order by section.
I know I can easily separate out the two statements but I want to do a select using the case statement below ;
however, I keep getting error 16 --"Order by items must appear in the select list if the statement contains a union.
If remove the case statement and put order by "internalID desc" I receive no errors. Moreover, when I take out the union statement and execute the two select statements with each including the case expression it runs as planned.
Can anyone tell what the problem is here? I have combed the web looking for an answer, but it seems that the statement is valid.
Thanks
J
declare @date
set @date = '2001'
select internalID from section_data_v3
union
select internalID from section_data_v4
order by
case when (@date = '2001') then internalID end desc
How do you make the "Like" comparison operator ignore case in an expression??
Expression looks like this:
Code SnippetParameters!CompanyFilterOp.Value = "%",Fields!company.Value like "*" & Parameters!Company.Value & "*"
BTW, the expression above is part of a switch expression, and is at the table level.
Data contains "General Industry" in Company column from database.
User enters "indust" in Company Parameter text box, and result is no data found.
User enters "Indust" in Company Parameter text box, and data is returned.
Thanks in advance for your time in responding.
Hi guys,
I have experienced problem while trying to use variable with expression based on several other variables in tasks running parallel.
The details are as following:
There is a SSIS package with simple Control flow: one Script Task which actually do nothing and two Execute Process Tasks, they run after Script Task in parallel. Then there are three simple (EvaluateAsExpression = False) string variables ServerName, Folder and JobNumber with values ServerName = €œ\test€?, Folder = €œtest€? and JobNumber = €œ12345€?. And there is one variable FullPath with expression @[User:: ServerName] + "\" + @[User::Folder] + "_" + @[User::JobNumber]. All the variables are of the Package scope. Then in Execute Process Tasks I have similar expressions based on FullPath variable: Execute Process Task 1 has expression @[User::FullPath] + "\date.bat" and Execute Process Task 2 has @[User::FullPath] + "\time.bat" one. As you understand these expressions define what exactly task should execute.
Then I€™m going to execute package from command line so appropriate XML configuration file has been created. The file contains following values for variables described above: ServerName = €œ\LiveServer€?, Folder = €œJob€? and JobNumber = €œ33091€?.
After series of consequent executions I have got following log file:
€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?
€¦ Execute Process Task 1€¦ Executing the process €œ\Test est_12345date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?
€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?
€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?
€¦
As you can see one of Execute Process Tasks usually receive correct value of the expression (based on values of variables from the configuration file) while another - incorrect one (based on €œdefault€? values of variables set directly in package). Sometimes wrong value appears in Task 1, next time in Task 2. Situations when both expressions in tasks evaluated correctly are very rare.
Then if you add some more Execute Process Tasks with similar expressions in the package (for ex. simply by copying existing tasks) you€™ll get a good chance to catch error like this:
OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073450774,0x,Reading the variable "User::FullPath" failed with error code 0xC0047084.
OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073647613,0x,The expression "@[User::FullPath] + "\time.bat"" on property "Executable" cannot be evaluated. Modify the expression to be valid.
Seems variable with expression FullPath is locked during evaluation by one of the parallel tasks in such a way that another task can€™t read it value correctly. Can someone help me with the issue? Maybe there are some options I missed which could prevent such behavior of application? Please let me know how I can make the package work correctly.
Hi everyone,
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.
Thanks for all your help,
regards,
Chris
I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
Jacob
Hi guys,
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.
thanks for any assistance you can provide.
regards,
Chris
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
I have 1 table (Test1)
A B C D
-----------------------------
010 15.2 20 0
030 13.8 25 0
090 10.3 1.2 0
010 7.25 10 0
030 25.1 25.23 0
010 21.01 14 0
090 10.50 8 0
I want to use SP to do something like this
UPDATE Test1
If [A] = '010' then
SET [D] = [B]
end if
If [A] = '090' then
SET [D] = [C]
end if
It is possible to use SP and only one UPDATE statement ?
This is not acceptable (the table is read twice, 200.000 - 300.000 rows)
UPDATE Test1
set [D] = [B]
WHERE [A] ='010'
+
UPDATE Test1
set [D] = [C]
WHERE [A] ='090'
Okay, so i have to check to tables against each other.
if record in Table1 IS NOT in Table2 i want to set dnc to '' or null if record in Table1 IS in Table2 i want to set dnc to 'Y'
currently, i have to do two passes. i was wonder if anyone would help me make this one statement.
Thanks!
update t1
set dnc = ''
from test_new.dbo.consumer t1
go
update t1
set dnc = 'y'
from test_new.dbo.consumer t1
inner join residential.dbo.dnc on tl1=phone
go
Im trying to do the following but get the error.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
The code is
update [table]
set [field 1] = (select (case [field 2]
when 'B' then 'Y'
when 'C' then 'N'
when 'N' then 'N'
when 'P' then 'Y'
else 'N'
end)
from [table]),
[field 3] = (select (case [field 2]
when 'B' then 'Y'
when 'C' then 'Y'
when 'N' then 'N'
when 'P' then 'N'
else 'N'
end)
from [table])
ANy ideas?
I am selecting from the database table and while I am selecting I am changing the value of the field
so thisis how it goes.
SELECT
.InCompleteOther = CASE WHEN (ErrMessage='Beneficiary in status') THEN 'You have End_Stage'
ELSE InCompleteOther
END
From MYTABLE
some how I am getting an error what could be it.