CODEWITHSUNDEEP
sqlt-sql
Mike Marks
Mike Marks

Reputation: 10139

Trying to combine three individual SELECT statements into one main SELECT statement

Here's the problem. There are 3 fields in my table which may contain data with an extra quotation appended at the end. So, I'm trying to run a select statement that will remove this extra character from these fields, IF that extra character exists. I can write 3 individual queries just fine, and they work, but I'm trying to combine them all into one query. Here's what I have so far, and I know it's probably incorrect the way I have it:

enter image description here

Here's the result set that comes back. Notice that all three columns are NULL. They shouldn't be:

enter image description here

Here's an individual query that works for one field at a time:

enter image description here

Can you tell me what I'm doing wrong?

Upvotes: 0

Views: 44

Answers (1)

StingyJack
StingyJack

Reputation: 19469

You can remove the WHEREs, as well as the non-correlated subqueries in the select list, and probably simplify it to this...

SELECT 
  AID
  , EID
  , STOREID
  , [Language]
  , 'BrandLabel' = CASE WHEN BrandLabel LIKE '%"' 
                         THEN LEFT(BrandLabel, LEN(BrandLabel) -1)
                        ELSE BrandLabel
                   END
  , 'Terms' = CASE WHEN Terms LIKE '%"' 
                         THEN LEFT(Terms, LEN(Terms) -1)
                        ELSE Terms
                   END
  , 'TrackOrderLbl' = CASE WHEN TrackOrderLbl LIKE '%"' 
                         THEN LEFT(TrackOrderLbl, LEN(TrackOrderLbl) -1)
                        ELSE TrackOrderLbl
                   END
FROM parallel_Purchase_Email_Content_OMS WITH (NOLOCK)

Upvotes: 2

Related Questions