May 18, 2017 at 8:37 am
I have a view that I need to expand upon but not sure how to do it. Below I have a successfully running view (getting data from another view) that I need to join to a table that lists states and their integer ids for lookup:CREATE VIEW [dbo].[UserLogViewByDB]
AS
SELECT [LogID],[Database],[FormName],[ControlName],[FieldName],[RecordID],[UserName],[OldValue],[NewValue],[TimeStamp]
FROM [BCC_DB].[dbo].[UserLogView]
CREATE TABLE [dbo].[States](
[stateId] [int] NOT NULL,
[stateAbbrveation] [varchar](5) NOT NULL,
[state] [varchar](100) NOT NULL,
[countryId] [int] NULL,
CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
(
[stateId] ASC
)
What I need to do is join the table to the view and 'IF the FIELDNAME = 'stateID' THEN OLDVALUE and NEWVALUE = 'stateAbbreviation' but again only when the FIELDNAME = 'stateID'...
Problem is I have , so far, no field to create the join...
any ideas? THANKS!
May 18, 2017 at 9:36 am
One way I might be able to do this avoiding a join would be to place some type of case block in this:[stateID] = CASE [State Abbreviation]
when 'AA' then 1
when 'AE' then 2
when 'AK' then 3
when 'AL' then 4
when 'AP' then 5
when 'AR' then 6
when 'AS' then 7
when 'AZ' then 8
when 'CA' then 9
when 'CO' then 10
when 'CT' then 11
when 'DC' then 12
when 'DE' then 13
when 'FL' then 14
when 'FM' then 15
when 'GA' then 16
when 'GU' then 17
when 'HI' then 18
when 'IA' then 19
when 'ID' then 20
when 'IL' then 21
when 'IN' then 22
when 'KS' then 23
when 'KY' then 24
when 'LA' then 25
when 'MA' then 26
when 'MD' then 27
when 'ME' then 28
when 'MH' then 29
when 'MI' then 30
when 'MN' then 31
when 'MO' then 32
when 'MP' then 33
when 'MS' then 34
when 'MT' then 35
when 'NC' then 36
when 'ND' then 37
when 'NE' then 38
when 'NH' then 39
when 'NJ' then 40
when 'NM' then 41
when 'NV' then 42
when 'NY' then 43
when 'OH' then 44
when 'OK' then 45
when 'OR' then 46
when 'PA' then 47
when 'PR' then 48
when 'PW' then 49
when 'RI' then 50
when 'SC' then 51
when 'SD' then 52
when 'TN' then 53
when 'TX' then 54
when 'UT' then 55
when 'VA' then 56
when 'VI' then 57
when 'VT' then 58
when 'WA' then 59
when 'WI' then 60
when 'WV' then 61
when 'WY' then 62
when 'AB' then 63
when 'BC' then 64
when 'MB' then 65
when 'NB' then 66
when 'NL' then 67
when 'NT' then 68
when 'NS' then 69
when 'NU' then 70
when 'ON' then 71
when 'PE' then 72
when 'QC' then 73
when 'SK' then 74
when 'YT' then 75
when 'TT' then 76
when 'BW' then 77
END
7?<w
May 18, 2017 at 9:48 am
Note that I had the when 'AR' then 6 on those when I meant when 6 then 'AR' but hope you get the picture.
May 18, 2017 at 11:22 am
It isn't pretty but this is working fine... hopefully this will help someone:USE [BCC_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[UserLogViewByDB_withStates]
AS
SELECT [LogID],[Database],[FormName],[ControlName],[FieldName],[RecordID],[UserName],
CASE
WHEN [FieldName] = 'stateID' AND [OldValue] = 1 then 'AA'
WHEN [FieldName] = 'stateID' AND [OldValue] = 2 then 'AK'
WHEN [FieldName] = 'stateID' AND [OldValue] = 3 then 'AK'
WHEN [FieldName] = 'stateID' AND [OldValue] = 4 then 'AL'
WHEN [FieldName] = 'stateID' AND [OldValue] = 5 then 'AP'
WHEN [FieldName] = 'stateID' AND [OldValue] = 6 then 'AR'
WHEN [FieldName] = 'stateID' AND [OldValue] = 7 then 'AS'
WHEN [FieldName] = 'stateID' AND [OldValue] = 8 then 'AZ'
WHEN [FieldName] = 'stateID' AND [OldValue] = 9 then 'CA'
WHEN [FieldName] = 'stateID' AND [OldValue] = 10 then 'CO'
WHEN [FieldName] = 'stateID' AND [OldValue] = 11 then 'CT'
WHEN [FieldName] = 'stateID' AND [OldValue] = 12 then 'DC'
WHEN [FieldName] = 'stateID' AND [OldValue] = 13 then 'DE'
WHEN [FieldName] = 'stateID' AND [OldValue] = 14 then 'FL'
WHEN [FieldName] = 'stateID' AND [OldValue] = 15 then 'FM'
WHEN [FieldName] = 'stateID' AND [OldValue] = 16 then 'GA'
WHEN [FieldName] = 'stateID' AND [OldValue] = 17 then 'GU'
WHEN [FieldName] = 'stateID' AND [OldValue] = 18 then 'HI'
WHEN [FieldName] = 'stateID' AND [OldValue] = 19 then 'IA'
WHEN [FieldName] = 'stateID' AND [OldValue] = 20 then 'ID'
WHEN [FieldName] = 'stateID' AND [OldValue] = 21 then 'IL'
WHEN [FieldName] = 'stateID' AND [OldValue] = 22 then 'IN'
WHEN [FieldName] = 'stateID' AND [OldValue] = 23 then 'KS'
WHEN [FieldName] = 'stateID' AND [OldValue] = 24 then 'KY'
WHEN [FieldName] = 'stateID' AND [OldValue] = 25 then 'LA'
WHEN [FieldName] = 'stateID' AND [OldValue] = 26 then 'MA'
WHEN [FieldName] = 'stateID' AND [OldValue] = 27 then 'MD'
WHEN [FieldName] = 'stateID' AND [OldValue] = 28 then 'ME'
WHEN [FieldName] = 'stateID' AND [OldValue] = 29 then 'MH'
WHEN [FieldName] = 'stateID' AND [OldValue] = 30 then 'MI'
WHEN [FieldName] = 'stateID' AND [OldValue] = 31 then 'MN'
WHEN [FieldName] = 'stateID' AND [OldValue] = 32 then 'MO'
WHEN [FieldName] = 'stateID' AND [OldValue] = 33 then 'MP'
WHEN [FieldName] = 'stateID' AND [OldValue] = 34 then 'MS'
WHEN [FieldName] = 'stateID' AND [OldValue] = 35 then 'MT'
WHEN [FieldName] = 'stateID' AND [OldValue] = 36 then 'NC'
WHEN [FieldName] = 'stateID' AND [OldValue] = 37 then 'ND'
WHEN [FieldName] = 'stateID' AND [OldValue] = 38 then 'NE'
WHEN [FieldName] = 'stateID' AND [OldValue] = 39 then 'NH'
WHEN [FieldName] = 'stateID' AND [OldValue] = 40 then 'NJ'
WHEN [FieldName] = 'stateID' AND [OldValue] = 41 then 'NM'
WHEN [FieldName] = 'stateID' AND [OldValue] = 42 then 'NV'
WHEN [FieldName] = 'stateID' AND [OldValue] = 43 then 'NY'
WHEN [FieldName] = 'stateID' AND [OldValue] = 44 then 'OH'
WHEN [FieldName] = 'stateID' AND [OldValue] = 45 then 'OK'
WHEN [FieldName] = 'stateID' AND [OldValue] = 46 then 'OR'
WHEN [FieldName] = 'stateID' AND [OldValue] = 47 then 'PA'
WHEN [FieldName] = 'stateID' AND [OldValue] = 48 then 'PR'
WHEN [FieldName] = 'stateID' AND [OldValue] = 49 then 'PW'
WHEN [FieldName] = 'stateID' AND [OldValue] = 50 then 'RI'
WHEN [FieldName] = 'stateID' AND [OldValue] = 51 then 'SC'
WHEN [FieldName] = 'stateID' AND [OldValue] = 52 then 'SD'
WHEN [FieldName] = 'stateID' AND [OldValue] = 53 then 'TN'
WHEN [FieldName] = 'stateID' AND [OldValue] = 54 then 'TX'
WHEN [FieldName] = 'stateID' AND [OldValue] = 55 then 'UT'
WHEN [FieldName] = 'stateID' AND [OldValue] = 56 then 'VA'
WHEN [FieldName] = 'stateID' AND [OldValue] = 57 then 'VI'
WHEN [FieldName] = 'stateID' AND [OldValue] = 58 then 'VT'
WHEN [FieldName] = 'stateID' AND [OldValue] = 59 then 'WA'
WHEN [FieldName] = 'stateID' AND [OldValue] = 60 then 'WI'
WHEN [FieldName] = 'stateID' AND [OldValue] = 61 then 'WV'
WHEN [FieldName] = 'stateID' AND [OldValue] = 62 then 'WY'
WHEN [FieldName] = 'stateID' AND [OldValue] = 63 then 'AB'
WHEN [FieldName] = 'stateID' AND [OldValue] = 64 then 'BC'
WHEN [FieldName] = 'stateID' AND [OldValue] = 65 then 'MB'
WHEN [FieldName] = 'stateID' AND [OldValue] = 66 then 'NB'
WHEN [FieldName] = 'stateID' AND [OldValue] = 67 then 'NL'
WHEN [FieldName] = 'stateID' AND [OldValue] = 68 then 'NT'
WHEN [FieldName] = 'stateID' AND [OldValue] = 69 then 'NS'
WHEN [FieldName] = 'stateID' AND [OldValue] = 70 then 'NU'
WHEN [FieldName] = 'stateID' AND [OldValue] = 71 then 'ON'
WHEN [FieldName] = 'stateID' AND [OldValue] = 72 then 'PE'
WHEN [FieldName] = 'stateID' AND [OldValue] = 73 then 'QC'
WHEN [FieldName] = 'stateID' AND [OldValue] = 74 then 'SK'
WHEN [FieldName] = 'stateID' AND [OldValue] = 75 then 'YT'
WHEN [FieldName] = 'stateID' AND [OldValue] = 76 then 'TT'
WHEN [FieldName] = 'stateID' AND [OldValue] = 77 then 'BW'
ELSE [OldValue] END as 'OldValue',
CASE
WHEN [FieldName] = 'stateID' AND [NewValue] = 1 then 'AA'
WHEN [FieldName] = 'stateID' AND [NewValue] = 2 then 'AK'
WHEN [FieldName] = 'stateID' AND [NewValue] = 3 then 'AK'
WHEN [FieldName] = 'stateID' AND [NewValue] = 4 then 'AL'
WHEN [FieldName] = 'stateID' AND [NewValue] = 5 then 'AP'
WHEN [FieldName] = 'stateID' AND [NewValue] = 6 then 'AR'
WHEN [FieldName] = 'stateID' AND [NewValue] = 7 then 'AS'
WHEN [FieldName] = 'stateID' AND [NewValue] = 8 then 'AZ'
WHEN [FieldName] = 'stateID' AND [NewValue] = 9 then 'CA'
WHEN [FieldName] = 'stateID' AND [NewValue] = 10 then 'CO'
WHEN [FieldName] = 'stateID' AND [NewValue] = 11 then 'CT'
WHEN [FieldName] = 'stateID' AND [NewValue] = 12 then 'DC'
WHEN [FieldName] = 'stateID' AND [NewValue] = 13 then 'DE'
WHEN [FieldName] = 'stateID' AND [NewValue] = 14 then 'FL'
WHEN [FieldName] = 'stateID' AND [NewValue] = 15 then 'FM'
WHEN [FieldName] = 'stateID' AND [NewValue] = 16 then 'GA'
WHEN [FieldName] = 'stateID' AND [NewValue] = 17 then 'GU'
WHEN [FieldName] = 'stateID' AND [NewValue] = 18 then 'HI'
WHEN [FieldName] = 'stateID' AND [NewValue] = 19 then 'IA'
WHEN [FieldName] = 'stateID' AND [NewValue] = 20 then 'ID'
WHEN [FieldName] = 'stateID' AND [NewValue] = 21 then 'IL'
WHEN [FieldName] = 'stateID' AND [NewValue] = 22 then 'IN'
WHEN [FieldName] = 'stateID' AND [NewValue] = 23 then 'KS'
WHEN [FieldName] = 'stateID' AND [NewValue] = 24 then 'KY'
WHEN [FieldName] = 'stateID' AND [NewValue] = 25 then 'LA'
WHEN [FieldName] = 'stateID' AND [NewValue] = 26 then 'MA'
WHEN [FieldName] = 'stateID' AND [NewValue] = 27 then 'MD'
WHEN [FieldName] = 'stateID' AND [NewValue] = 28 then 'ME'
WHEN [FieldName] = 'stateID' AND [NewValue] = 29 then 'MH'
WHEN [FieldName] = 'stateID' AND [NewValue] = 30 then 'MI'
WHEN [FieldName] = 'stateID' AND [NewValue] = 31 then 'MN'
WHEN [FieldName] = 'stateID' AND [NewValue] = 32 then 'MO'
WHEN [FieldName] = 'stateID' AND [NewValue] = 33 then 'MP'
WHEN [FieldName] = 'stateID' AND [NewValue] = 34 then 'MS'
WHEN [FieldName] = 'stateID' AND [NewValue] = 35 then 'MT'
WHEN [FieldName] = 'stateID' AND [NewValue] = 36 then 'NC'
WHEN [FieldName] = 'stateID' AND [NewValue] = 37 then 'ND'
WHEN [FieldName] = 'stateID' AND [NewValue] = 38 then 'NE'
WHEN [FieldName] = 'stateID' AND [NewValue] = 39 then 'NH'
WHEN [FieldName] = 'stateID' AND [NewValue] = 40 then 'NJ'
WHEN [FieldName] = 'stateID' AND [NewValue] = 41 then 'NM'
WHEN [FieldName] = 'stateID' AND [NewValue] = 42 then 'NV'
WHEN [FieldName] = 'stateID' AND [NewValue] = 43 then 'NY'
WHEN [FieldName] = 'stateID' AND [NewValue] = 44 then 'OH'
WHEN [FieldName] = 'stateID' AND [NewValue] = 45 then 'OK'
WHEN [FieldName] = 'stateID' AND [NewValue] = 46 then 'OR'
WHEN [FieldName] = 'stateID' AND [NewValue] = 47 then 'PA'
WHEN [FieldName] = 'stateID' AND [NewValue] = 48 then 'PR'
WHEN [FieldName] = 'stateID' AND [NewValue] = 49 then 'PW'
WHEN [FieldName] = 'stateID' AND [NewValue] = 50 then 'RI'
WHEN [FieldName] = 'stateID' AND [NewValue] = 51 then 'SC'
WHEN [FieldName] = 'stateID' AND [NewValue] = 52 then 'SD'
WHEN [FieldName] = 'stateID' AND [NewValue] = 53 then 'TN'
WHEN [FieldName] = 'stateID' AND [NewValue] = 54 then 'TX'
WHEN [FieldName] = 'stateID' AND [NewValue] = 55 then 'UT'
WHEN [FieldName] = 'stateID' AND [NewValue] = 56 then 'VA'
WHEN [FieldName] = 'stateID' AND [NewValue] = 57 then 'VI'
WHEN [FieldName] = 'stateID' AND [NewValue] = 58 then 'VT'
WHEN [FieldName] = 'stateID' AND [NewValue] = 59 then 'WA'
WHEN [FieldName] = 'stateID' AND [NewValue] = 60 then 'WI'
WHEN [FieldName] = 'stateID' AND [NewValue] = 61 then 'WV'
WHEN [FieldName] = 'stateID' AND [NewValue] = 62 then 'WY'
WHEN [FieldName] = 'stateID' AND [NewValue] = 63 then 'AB'
WHEN [FieldName] = 'stateID' AND [NewValue] = 64 then 'BC'
WHEN [FieldName] = 'stateID' AND [NewValue] = 65 then 'MB'
WHEN [FieldName] = 'stateID' AND [NewValue] = 66 then 'NB'
WHEN [FieldName] = 'stateID' AND [NewValue] = 67 then 'NL'
WHEN [FieldName] = 'stateID' AND [NewValue] = 68 then 'NT'
WHEN [FieldName] = 'stateID' AND [NewValue] = 69 then 'NS'
WHEN [FieldName] = 'stateID' AND [NewValue] = 70 then 'NU'
WHEN [FieldName] = 'stateID' AND [NewValue] = 71 then 'ON'
WHEN [FieldName] = 'stateID' AND [NewValue] = 72 then 'PE'
WHEN [FieldName] = 'stateID' AND [NewValue] = 73 then 'QC'
WHEN [FieldName] = 'stateID' AND [NewValue] = 74 then 'SK'
WHEN [FieldName] = 'stateID' AND [NewValue] = 75 then 'YT'
WHEN [FieldName] = 'stateID' AND [NewValue] = 76 then 'TT'
WHEN [FieldName] = 'stateID' AND [NewValue] = 77 then 'BW'
ELSE [NewValue] END as 'NewValue',
[TimeStamp]
FROM [BCC_DB].[dbo].[UserLogViewByDB]
GO
May 18, 2017 at 12:13 pm
could you have done conditional OUTER JOINs? I suppose it depends on the data type of OldValue and NewValue, but maybe something like this:
CREATE VIEW [dbo].[UserLogViewByDB]
AS
SELECT [LogID],[Database],[FormName],[ControlName],[FieldName],[RecordID],[UserName],[OldValue],[NewValue],[TimeStamp],
ISNULL(so.stateAbbrveation,ul.OldValue) AS OldValue,
ISNULL(sn.stateAbbrveation,ul.NewValue) AS NewValue
FROM [dbo].[UserLogView] ul
LEFT OUTER JOIN dbo.States so ON ul.FieldName = 'StateID' AND ul.OldValue = so.stateId
LEFT OUTER JOIN dbo.States sn ON ul.FieldName = 'StateID' AND ul.NewValue = sn.stateId
edit: fixed second join to dbo.States
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply