asked on

MS SQL Selecting a column value based on a Select statement

I am trying to verify that the State column in the Customer table is a valid state and if not force the state value of 'MI' in the State column.
Here is the code I currently have and I am trying to do a select in a Case statement and not certain if this is the correct way to do it or not.

--- Table @State with the valid state abbreviations.
DECLARE @State TABLE(State CHAR(2))

INSERT @State (State)
VALUES
            ('AK'),('AL'),('AR'),('AZ'),('CA'),('CO'),('CT'),('DE'),('FL'),('GA')
            ,('HI'),('IA'),('ID'),('IL'),('IN'),('KS'),('KY'),('LA'),('MA'),('MD')
            ,('ME'),('MI'),('MN'),('MO'),('MS'),('MT'),('NC'),('ND'),('NE'),('NH')
            ,('NJ'),('NM'),('NV'),('NY'),('OH'),('OK'),('OR'),('PA'),('RI'),('SC')
            ,('SD'),('TN'),('TX'),('UT'),('VA'),('VT'),('WA'),('WI'),('WV'),('WY')
            ,('DC'),('ON')

SELECT StreetAddress
,Phone
,City
,CASE WHEN (SELECT s.[state] FROM @State s WHERE s.[State] = c.[State]) = 'NULL' THEN 'MI'
                  WHEN LEN(RTRIM(c.[State])) = 0 THEN 'MI'
                  ELSE UPPER(c.[State])
            END AS [State]
,Zip
       ,AccountNumber
,FirstName
,LastName
,HomeStoreNumber AS PSP_HomeStore
FROM dbo.Customer c

With the CASE statement above I am getting some of the Customer States to be 'N', 'M', '4', '6I' etc. Where am I going wrong and is the CASE statement the best way to handle this?

Thanks,
John

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

The inverse of the above, which should be given to somebody to correct all of these bad state codes, is...

SELECT c.*
FROM dbo.Customer c
   -- LEFT means return all rows from dbo.Customer ...
   LEFT JOIN @state st ON c.state = st.state
-- ... which does not have a matching State code to a table of valid states
WHERE st.state IS NULL

Open in new window

F P

Is it State.State or State.state? You use both. How are you collating?

...

SELECT s.[state] FROM @State s WHERE s.[State] ....

j_heck

ASKER

Jim, I have tried both and I am not getting the results I am looking for.

Let me try and make it clearer what I am looking for in the result set. Currently the values in the column Customer.State can be a valid State (ie. MI, IL, KY, FL, etc), or it could be a blank/null, or it could be any other value of 1 or 2 characters (ie. NL, 40, OP, O5, TT, Z, X, 3, etc). What needs to be returned in the result set is a valid State Abbreviation based on a State table. The State table value of State.State is one of the 50 states plus District of Columbia (DC) & Ontario Canada (ON). So the data returned from the Customer.State column needs to be a valid state found in the State.State table or if anything else the value is defaulted to 'MI'. But I want all of the Customers in the results set with a valid State value.

Does this make more sense?

Thanks,
John

F P

JOIN won't work? Then check the value of the column if it exists?

http://www.mssqltips.com/sqlservertip/1667/sql-server-join-example/

Jim Horn

Getting there. So if there are 100 customers, say 90 with valid values in the State table and 10 that are not, would the final result set contain 90 or 100 rows?

>But I want all of the Customers in the results set with a valid State value.
>...or if anything else the value is defaulted to 'MI'.
The above implies 100, with the 10 oddballs showing 'MI', which is the comment I provided.

Perhaps poviding us with a more detailed data mockup would help.

j_heck

ASKER

The final result set would contain 100 rows. Of the 10 that are not valid states (as mentioned in the previous post) the state value would be 'MI'.

j_heck

ASKER

Frank, the LEFT OUTER JOIN and checking for NULL doesn't work. If the Customer.State is blank then it still returns a blank value for State in the result set.

F P

I'm not sure what system you're using other than SQL, but I was saying to parse the replacement of blank records for replacement/defaults in the logic of the code surrounding the query. Is that not an option to use IF THEN ELSE conditional statements in the language receiving the result set?

Jim Horn

A sample data set would be good, because based on the requirements of this question my code is a correct answer, so there has to be something else going on here.

j_heck

ASKER

Frank, I am only using MS SQL. The Customer table is an SQL table and the State table is temp table using the DECLARE statement and then inserting the individual State abbreviations. We are importing the data from the Customer table into another SQL table/database and the State column needs to be a valid state if it is not then the state column receives the default value of 'MI'. The value in the Customer.State column can be a valid state abbreviation already, any combination (up to 2 characters) of numbers, special characters, and/or letters or blank/null.

j_heck

ASKER

Jim, the only condition that is not getting fixed with the code you shared is Customer.State (c.State) columns that are blank or space filled.

j_heck

ASKER

Jim, here is the CASE Statement that worked:

,CASE WHEN st.state IS NULL THEN 'MI'
                  WHEN LEN(RTRIM(c.State)) = 0 THEN 'MI'
                  ELSE c.State
                  END

Jim Horn

again, need a sample data set, because if there is a match between the two tables then it will display c.State, so the only valid explanation I'm seeing is if the state table has blanks or is space filled.

Jim Horn

>WHEN LEN(RTRIM(c.State)) = 0 THEN 'MI'
Wonder if any of your customer.State values have a trailing space that should be deleted.
Run this to find out, and it's also worth checking whatever adds data to this table to see if it's validating for trailing spaces:

SELECT * FROM customer WHERE RIGHT(State, 1) = ' '

Thanks for the grade. Good luck with your project.

j_heck

ASKER

Thanks for the information Jim. I will look into that. The problem with data is that it is entered into a system out of my control and 250+ locations enter their own data.

Not a problem with the grade. You got me thinking in the right direction. For some reason I didn't think of that but knew about the Left Outer Join. I was stuck in my head with another solution, doing a Select for the column results, that I could not move off of so you really helped out. Thanks again.