November 29, 2018 at 11:37 am
I want to add to a variable in a case statement:
SELECT name, city, state,
rank =
CASE
WHEN len(city>10) THEN rank = len(city)
WHEN len(state>15) THEN rank + len(state)
END
FROM cities
ORDER By rank
I think I need to create a temp table and then do an update based on my cases, but was wondering if there is another method?
November 29, 2018 at 11:54 am
I think you are trying to add the results of two different calculations. If so, then split the calculation into two individual steps, then add the results, like this.
SELECT name, city, state,
CASE WHEN len(city) >10 THEN len(city) ELSE 0 END +
CASE WHEN len(state) >15 THEN len(state) ELSE 0 END AS rank
FROM cities
ORDER By rank
If that's not what you were trying to do, please explain further.
November 29, 2018 at 12:03 pm
Sailor - Thursday, November 29, 2018 11:37 AMI want to add to a variable in a case statement:SELECT name, city, state,
rank =
CASE
WHEN len(city>10) THEN rank = len(city)
WHEN len(state>15) THEN rank + len(state)
END
FROM cities
ORDER By rankI think I need to create a temp table and then do an update based on my cases, but was wondering if there is another method?
I think we need a lot more information.
Where do you need a variable? There are none in your code.
You code will not run, the syntax is incorrect.
How do you want to "rank" based upon the length of the data in the columns?
Can you provide some sample data, and the expected results???
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 29, 2018 at 12:07 pm
Sailor - Thursday, November 29, 2018 11:37 AMI want to add to a variable in a case statement:SELECT name, city, state,
rank =
CASE
WHEN len(city>10) THEN rank = len(city)
WHEN len(state>15) THEN rank + len(state)
END
FROM cities
ORDER By rankI think I need to create a temp table and then do an update based on my cases, but was wondering if there is another method?
And what happens when both lengths are < 10?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 29, 2018 at 12:11 pm
Phil Parkin - Thursday, November 29, 2018 12:07 PMSailor - Thursday, November 29, 2018 11:37 AMI want to add to a variable in a case statement:SELECT name, city, state,
rank =
CASE
WHEN len(city>10) THEN rank = len(city)
WHEN len(state>15) THEN rank + len(state)
END
FROM cities
ORDER By rankI think I need to create a temp table and then do an update based on my cases, but was wondering if there is another method?
And what happens when both lengths are < 10?
If you are using a dataset that consists of the States and Territories of the USA, I think the only one that is longer than 15 characters may be the District Of Columbia.
If the data set spells out everything, then there are a few territories that exceed that.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 29, 2018 at 7:21 pm
There is no such thing as a case state_codement in SQL. There is no such thing as a case state_codement in SQL. It's a case expression, and an expression returns a single scaler value of known data type.
We also don't have any DDL to work with; the column names you posted are vague and generic. Also, did you know that RANK is a keyword in SQL? The Postal Service has standards for the length of city names and for the two-letter state codes. City names can be longer than 10 characters, but the state codes are always two letters.
The column city_rank is created all at once and all of the rows of the query result. The query result also appears all at once. This is because SQL is a set oriented language. This means you can't use it before it's created. Here's my guess at translating your query into valid SQL; but having valid syntax doesn't make it reasonable or logical.
SELECT something_name, city_name, state_code,
CASE
WHEN LEN(city_name) >10 THEN LEN(city_name)
WHEN LEN(state_code) >15 THEN LEN(state_code) -- always fails
END AS city_rank
FROM Cities;
Please post DDL and follow ANSI/ISO standards when asking for help.
November 30, 2018 at 12:42 pm
I think you are looking for something like this:
Create table test
(City nvarchar(50),
state nvarchar (50)
)
Insert into test
values ('Cape Coral', 'Florida'), ('Atlanta', 'Georgia'), ('Albequerque', 'New Mexico'), ('Greater Dallas Fort Worth', 'United Republic of Texas')
Select City, State, --len(city), len(state),
case
When len (state) > 15 and len(city) > 10 then len(City) + len(state)
When len(City) > 10 Then len(city)
END as rank
from test
order by rank
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply