March 18, 2009 at 7:11 am
I have to create a view of data from two tables. In doing so, I also need to translate a column's numeric value into an alpha value. I have the following:
CREATE VIEW AS mynewview
SELECT a.col1,a.col2,b.col3,b.col4,b.col5,
colMask = CASE
WHEN ((b.col3 % 8) /4) >= 1 THEN 'Submit'
WHEN ((b.col3 % 16) /8) >= 1 THEN 'Modify'
ELSE 'None'
END
FROM mytable a, myoldtable b
WHERE a.col1=b.col3
My issue is that for any given row, both of the When statements could be true. In my new view I
need the colMask result column to reflect both, something like:
col1 colMask
1 Submit
2 Submit
3 Modify
4 Submit;Modify
Is this possible? As you can see, I'm definitely a newbie. Any help would be greatly appreciated.
March 18, 2009 at 7:22 am
try this:
CREATE VIEW AS mynewview
SELECT a.col1,a.col2,b.col3,b.col4,b.col5,
colMask =
CASE
WHEN ((b.col3 % 8) /4) >= 1 --'Submit'
THEN CASE
WHEN ((b.col3 % 16) /8) >= 1 --'Modify'
THEN 'Submit,Modify'
ELSE 'Submit'
END
WHEN ((b.col3 % 16) /8) >= 1 THEN 'Modify'
ELSE 'None'
END
FROM mytable a, myoldtable b
WHERE a.col1=b.col3
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 18, 2009 at 7:30 am
Christopher Stobbs (3/18/2009)
try this:
CREATE VIEW AS mynewview
SELECT a.col1,a.col2,b.col3,b.col4,b.col5,
colMask =
CASE
WHEN ((b.col3 % 8) /4) >= 1 --'Submit'
THEN CASE
WHEN ((b.col3 % 16) /8) >= 1 --'Modify'
THEN 'Submit,Modify'
ELSE 'Submit'
END
WHEN ((b.col3 % 16) /8) >= 1 THEN 'Modify'
ELSE 'None'
END
FROM mytable a, myoldtable b
WHERE a.col1=b.col3
Christopher,
Thank you very much for the response. I apologize for being short-sighted. In an effort to keep things "simple" in my original post, I neglected to state that there are 20 different variations of the When statement and any combination could be correct, such as
WHEN ((b.col3 % 8) /4) >= 1 THEN 'Submit'
WHEN ((b.col3 % 16) /8) >= 1 THEN 'Modify'
WHEN ((b.col3 % 32) /16) >= 1 THEN 'Return'
WHEN ((b.col3 % 64) /32) = 1 THEN 'Button'
(There are 16 more like these). Any combination of these could be true.
March 18, 2009 at 7:36 am
try something like this.
CREATE VIEW mynewview
AS
WITH MyCTE AS
(SELECT a.col1,a.col2,b.col3,b.col4,b.col5,
colMask = CASE
WHEN ((b.col3 % 8) /4) >= 1 THEN 'Submit'
WHEN ((b.col3 % 16) /8) >= 1 THEN 'Modify'
ELSE 'None'
END
FROM mytable a
INNER JOIN myoldtable b ON a.col1=b.col3)
SELECT
Col1,Col2,Col3,Col4,Col5,
(SELECT colMask + ',' FROM MyCTE b WHERE a.col1 = b.col1 FOR XML PATH('')) as colMask
FROM MyCTE a
Please note that I have just assumed the Col1 is Unique if it's not you will need to change the where clause in the Correlated subquery in the last select
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 18, 2009 at 7:43 am
A separate case for each expression will do the trick:
SET colMask = ISNULL(NULLIF(
CASE WHEN ((b.col3 % 8) /4) >= 1 THEN 'Submit ' ELSE '' END +
CASE WHEN ((b.col3 % 16) /8) >= 1 THEN 'Modify ' ELSE '' END +
CASE WHEN ((b.col3 % 32) /16) >= 1 THEN 'Return ' ELSE '' END +
CASE WHEN ((b.col3 % 64) /32) = 1 THEN 'Button ' ELSE '' END
, ''), 'None')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 18, 2009 at 7:58 am
Thank you Christopher and Chris. I went with Chris' solution and it worked like a charm. NICE!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply