Transact SQL -- How Do I Case &>?
Dec 8, 2005
Hello, I am having some transact SQL problems.
ALLOWED:
Column1,
CASE FOO
WHEN 1 THEN 'Hello'
ELSE 'Goodbye'
END as MyValue,
Column2
NOT ALLOWED:
Column1,
CASE FOO
WHEN > 1 THEN 'Hello'
ELSE 'Goodbye'
END as MyValue,
Column2
The problem seems to be the > symbol. Apparantly the case statement is only for equality checks, and nothing else.
So, what can I do to fix this? I have these kinds of comparisons all over.
~Le
View 2 Replies
ADVERTISEMENT
May 4, 2015
I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?
View 7 Replies
View Related
Sep 25, 2015
I've been beating my head against this for a bit and haven't been able to figure this out I want to pull 1 set of values between a date time range if @Report = '1' but if @Report = 2 or 3 I want it to drop 3 of the parameters so it will pull all items. by dropping the where parameters for @BeginRangeDate and @LookOutDate and L.COLineStatus. My report works great if I go in and run Report 1 with the date parameters in the where statement and also works great for Reports 2, 3 if I REM out those 3 items in the Where statement. Here is what I currently have
CASE@Report
WHEN'1'THEN
WHEREL.RequestedShipDate>=@BeginRangeDate
ANDL.RequestedShipDate<=@LookoutDate
ANDDO.ForecastDate>=@ForecastDate
[Code] .....
View 7 Replies
View Related
May 12, 2015
I need to breakdown some information with just initials (sometimes 2, sometimes 3 if the initials are already used) SQL Server throws the error of:
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.
What should I alter, or how should I write this query so I can use all of the needed case statements? (their are actually about 24 when statements, but this is just to get a working example to display)
Select
case
when employee_name Like 'Jorge Jones' Then 'JJ'
when employee_name Like 'Mike Mikes' Then 'MM'
when employee_name Like 'Albert Alvarez' Then 'AA'
when employee_name Like 'Hernandez-Sotata' Then 'HS'
[code]....
View 5 Replies
View Related
Jul 20, 2005
I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad
View 4 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
Sep 24, 2015
I have the following query in a user-defined function. It accepts a single string parameter, but for the sake of simplicity, I have substituted actual strings in the query. It basically checks the passed string. If it ends with "Id", it strips off the "Id" and returns the resulting string. If it ends with "Id" followed by a digit, it strips that off and returns the string.
SELECT CASE
WHEN LEN('IncidentViolationId') > 2 AND RIGHT('IncidentViolationId', 2) = 'Id' THEN LEFT('IncidentViolationId', LEN('IncidentViolationId') - 2)
WHEN PATINDEX('%Id[0-9]', 'IncidentViolationId') > 1 THEN LEFT('IncidentViolationId', PATINDEX('%Id[0-9]', 'IncidentViolationId') - 1)
ELSE 'IncidentViolationId' END
This code has worked flawlessly for quite some time and all of a sudden I get "Invalid length parameter passed to left function". I understand why LEFT() would normally fail if I passed it a -1 for the second parameter, but in this scenario, asI understand it, it never should have reached the second WHEN condition since the first one evaluates to true. Why and why all of a sudden?
View 10 Replies
View Related
Aug 9, 2015
In the below query, I can get the individual/single group by columns as well as multiple but I cannot control the order in which I would like to group by the data.
So lets say I want to group the data by OS->browser->browser_version(just one example) then I cannot achieve that as the order of OS column comes later in the query.
I know one option would be to write a dynamic SQL but i dont want to do that because of performance reasons. Any other way this can be achieved?
select
case when @include_browser =
1 then browser_name end as browser_name,
case when @include_browser_version
= 1 then browser_version end
as browser_version,
[Code] ....
View 4 Replies
View Related
Sep 24, 2015
I use Order By Used with Case Statement and may be this is the reason for which i am getting error
Windowed functions can only appear in the SELECT or ORDER BY clauses.
Here is my complete sql where i use Row_Number function for pagination with sorting.
DECLARE @StartIndex INT
DECLARE @EndIndex INT
DECLARE @SortColumn VARCHAR(MAX)
DECLARE @SortDirection VARCHAR(MAX)
SET @StartIndex = 1
[Code] ....
View 7 Replies
View Related
Aug 20, 2015
Tried a few ways and I can ge this to work at the end in then WHEN part. Just struggling to put this together to be accepted as a CASE WHEN statement, probably missing the obvious.
Case when Postcode like '%[abcdefghijklmnopqrstuvwxyz%]' then 'Lowercase Postcode' else 'Postcode OK' end as [DQPostcode]
collate Latin1_General_CS_AS
Simple terms looking for all instances of Lowercase characters in the Postcode field
View 5 Replies
View Related
Jun 3, 2015
So I'm thinking if I can have multiple statements within the CASE-THEN..or do I have to CASE out each individually? Kind of like this....
CASE
WHEN [AddressType] = 'M'
THEN [MailingAddress].[Address1]
[MailingAddress].[Address2]
[MailingAddress].[City]
[MailingAddress].[State]
[MailingAddress].[Zipcode]
WHEN [AddressType] = 'D'
THEN [DefaultAddress].[Address1]
[DefaultAddress].[Address2]
[DefaultAddress].[City]
[DefaultAddress].[State]
[DefaultAddress].[Zipcode]
View 3 Replies
View Related
Nov 6, 2015
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
View 9 Replies
View Related
Oct 20, 2015
Is it possible to combine a CASE statement with two different columns to retrieve data into one result column? In one column it has multiple JobCode, but this needs to be divided. The only way I can see to do this is take the data from another column to get the results.Ex. JobCode - has one code for several job descriptions (there are about 30), but all within this code cannot have the same level of access. So I need to divide them out, and put them in one column for AccessLevel.
JobTitle - has one code for one job, (but there are over 100).I want to pull from both columns to get the results I need to assign appropriate access level in one column.
Case JobCode (they all have same job code, but everyone in this job code should not have same access)
When '45' Then '1' (Principal, Asst. Prin, or any Administrator, Counselors)
When '25' Then '2' (this could be teacher, etc. )
Case JobTitle (this is how access should be)
When '12345' Then '1' (this is Administration only)
[code]....
View 12 Replies
View Related
Nov 11, 2015
I am trying to run the below but I get an error of 'Incorrect syntax ')'' --- I have tried every angle I can think of around the parens to fix this but nothing I do is working.
UPDATE abcdefg
SET [Date] = GETDate(),
[readytogo] =
(
CASE WHEN [customername] NOT IN (Select [customername] from [server].[database].[dbo].[view])
THEN 'Yes'
ELSE
'Needs Verification'
[code]....
View 5 Replies
View Related
Aug 5, 2015
I have a query which works absolutely fine when connecting to an actual server:
WITH CLAIMDATA AS(
SELECT DISTINCT
DB_NAME() AS DBName,
'UA' AS Client,
POLICY AS KMPONO,
[code]...
If i change the connector to REPLPROD (which is a linked server): From REPLPROD.XUNMDTAUAI.dbo.UPPOREP UP INNER JOIN REPLPROD.XUNMDTAUAI.dbo.UKKMREP UK
I get the error:
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.
View 7 Replies
View Related
Aug 11, 2015
While creating a table, can we default a column to be UPPER. So that even if we insert a lower case, it should be converted to UPPER case.
View 4 Replies
View Related
Jun 4, 2015
I have used the below update query. However, its updating only the first value. Like its updating AB with volume when c.Type = ABC, similarly for CD. Its not updating based on the 2nd or the next case condition.
Update XYZ Set AB = a.Amt * (CASE WHEN c.Type = 'ABC' THEN (c.volume)
WHEN c.TYPE = 'DEF' THEN (c.volume)
WHEN c.Type = 'GHI' THEN (c.volume)
Else 0
END),
CD = CASE WHEN c.Type = 'MARGIN' THEN '4105.31'
WHEN c.Type = 'ABC' THEN '123.1'
WHEN c.Type = 'DEF' THEN '234.2'
WHEN c.Type = 'GHI' THEN '567.1'
END
from table1 a join table2 b
on a.Cust = b.Customer
join table3 c
on b.account = c.account and b.channel =c.channel
Why its not working properly? But if i use Select statement instead of update query its working properly.
View 18 Replies
View Related
Jul 28, 2015
I am trying to add the letters 'MS' in front of value while using a case statement. If Dispo = 2 I want it to pull back 'Inactive', else I want it to pull back the Value with MS in front (eg. "MS14"). The data in the value column are numbers. Would I use a CONCAT? If so where does that need to go?
Case when dispo = 2 then 'Inactive' else cast(Value as varchar(11)) end ,
View 27 Replies
View Related
Oct 20, 2015
I need to update many rows in some table. I've made such SQL query:
UPDATE [%TableName%]
SET [%FieldName%] = CASE
WHEN ID = 1 THEN '1'
WHEN ID =2 THEN '2'
....
END;
[%FieldName2%] = CASE
WHEN ID = 1 THEN '1'
WHEN ID = 2 THEN '2'
....
END;
WHERE ID IN {1, 2, ...}
Are there some limitations for CASE operator? How many "when - then" conditions can I include in query?
View 6 Replies
View Related
Nov 6, 2015
below is my original query
select Value = count(*) from dbo.test
I have 20 rows in dbo.test so i will get 20 as my output, now i need to write a case statement here such that when count(*) = 0 then it should display text filed 'NO Data' else it should display the count.
View 5 Replies
View Related
Jul 27, 2015
below is the sql query i am trying to write and i receive an error saying Incorrect syntax near the keyword 'Between'.
Update dbo.Claims_Primary_Adjuster_Test_Sujith
SET
Claims_Primary_Adjuster_Test_Sujith.Supervisor = Claims_Internal_Transfer.Supervisor,
Claims_Primary_Adjuster_Test_Sujith.Office = Claims_Internal_Transfer.Office,
Claims_Primary_Adjuster_Test_Sujith.Specialty = Claims_Internal_Transfer.Specialty
from dbo.Claims_Primary_Adjuster_Test_Sujith
Left Outer Join Stg.HS_DW_RV_ClaimsON Claims_Primary_Adjuster_Test_Sujith.Claim_Number = HS_DW_RV_Claims.ClaimNumber
[code]....
how to modify my code such that it works for my condition?
View 10 Replies
View Related
May 18, 2015
I need to perform an update where there are multiple scenarios which determine the value that is entered. Below is a sort've psuedo code of how it needs to be.
Update MyTable SET MyColumn = CASE WHEN MyCol1 = 'Value1' Then NewValue Else
WHEN MyCol1 <> 'Value1' And MyCol2 = 'Active' Then 'Value1'
In the scenario where MyCol1 <> Value1 and MyCol2 <> 'Active' then no update would occur and the original value would remain intact.
View 2 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
Jun 22, 2015
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
View 6 Replies
View Related
Nov 19, 2015
That may not be the most exact TITLE I have used for a question as CASE may not be the best expression to use but I have a query:
select AR.ClientID,
AR.ReferenceID,
AAC.SequenceID,
AA.AppearanceDate,
CASE
WHEN AAC.Outcome IS NULL Then 'Cancelled' else
[Code]....
This returns the following data set:
ClientID ReferenceID
SequenceID AppeareanceDate
Outcome Rownum
1000000 2
2000358 2005-11-07
Yes 1
[Code] ...
What I want the eventually solution to be is that for each unique/distinct client, reference and sequence there is one record (row). Based on the row_number, I want the first 'AppearanceDate' renamed to 'AppearanceDate1' with the first AppearanceDate as the data and the first Outcome to be renamed 'Outcome1' with the first Outcome as the data. Any subsequent rows would follow as columns:
ClientID ReferenceID
SequenceID AppeareanceDate1
Outcome1 AppearanceDate2
Outcome2
1000000 2
2000358 2005-11-07
Yes NULL
[Code] ...
Only four AppearanceDates and outcomes are required based on the unique/distinct Client, Reference and SequenceIDs. How I can loop through the Appearances based on the row_number and show each record in one row?
View 3 Replies
View Related
Oct 27, 2015
I have a field called PPH and this field type float. so, this field could be null, 0.0, or values. I am using case statement:
[PPH] = case when h.HoursFloat = 0 then 0 else
round(s.Pledges / h.HoursFloat,2) end,
I am trying to add where if h.HourFloat is null then 0 but, it does not work. I have tried different ways such as
[PPH] = CASE isnull (h.HoursFloat,0)
when 0 then 0 else ....
How to add another condition to check on null then 0.
View 9 Replies
View Related
Jun 15, 2015
How do you incorporate a case statement so that you can add "sub total" and grand total" to each grouping set section? Trying to see how to incorporate case.
[URL] ....
SELECT
CustomerID,
SalesPersonID,
YEAR(OrderDate) AS 'OrderYear',
SUM(TotalDue) AS 'TotalDue'
FROM Sales.SalesOrderHeader
[Code] .....
View 4 Replies
View Related
Aug 3, 2015
With Sql Server Management Studio, while creating/modifying a table I want to specify one of its columns to store values in lower case only. Can it be done through the designer or by some other means?
N.B.: Using SQL Server 2005.
View 22 Replies
View Related
Oct 29, 2015
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')
View 7 Replies
View Related
May 17, 2015
Table Structure
EID COLA COLB
1 name A
1 age 23
1 city hyd
1 email abc@live.in
1 mobile 45126
2 name B
2 age 43
[code]....
how to display the result where any of the mandatory fields (name,age,city,email,mobile)are missing then it should display as that field as Null
View 9 Replies
View Related
Jun 30, 2015
If I make a search for instance
"Candy" in where statement I retrieve 12 hits however if I write "candy" I retrieve 0 hits.
Is it possible to make the database or table to be case insensitive?
View 2 Replies
View Related
Sep 24, 2015
For Below example when @x=1 to retrieve col>0 rows or all rows.
With out another if else blocks or Dynamic sql to solve only in where clause.
select 0 col into #x
union
select 1 col
union
select 2 col
declare @x INT =1
SELECT * FROM #x
where col>CASE WHEN @x=1 THEN 0 ELSE (col=col) END
--here in case i want to compare only when @x=1 then col>0 other wise select all rows with out filter
View 5 Replies
View Related
Nov 16, 2015
I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.
Store Type Sales
11 A 1000
23 A 1980
23 B 50
5 B 560
I want to filter the store in "where clause" where
1)- if the store has type A and B, then assign only A
2)- if the store has type A associated with it then assign A
3)- if the store has type B associated with it, then assign B.
Select Store, sum(sales), Type
from table1
where (TYPE]= (case when [TYPE] in ('A','B') then 'A'
when [TYPE]='A' then 'A' else 'B'end))
GROUP BY [store], [TYPE]
The above statement is not working for when store has only Type B associated with it.
View 7 Replies
View Related
