October 5, 2016 at 10:27 am
Hi All, I'm working on this for some time but ran into a dead end I'm afraid. So hope to get some help.
We have this table with many colums and what I need to do is for every row (record):
-get three column values
-compare each of those three value again a lookup table
-merge the three results from the lookup table together in 1 string
-update this row --> insert the string from previous step into another column
That's it. So on for every row.
For now I focussed on the 'read 1 column and update another depending on the value' part. The lookup and string part is the next step and I think I can do that.
But I'm not getting this to work. What I tried is:
DECLARE @id NVARCHAR(32)
DECLARE @name NVARCHAR(50)
DECLARE @loc VARCHAR(50)
DECLARE @getid CURSOR
SET @getid = CURSOR FOR
SELECT ExtUserID,
LastName,
GPF5
FROM dbo.SaltoCardholders
OPEN @getid
FETCH NEXT
FROM @getid INTO @id, @name, @loc
WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT @id
FETCH NEXT
FROM @getid INTO @id, @name, @loc
IF @loc = 'VWB'
--select @id, @name;
UPDATE dbo.SaltoCardholders SET <column_x> = '<some_value>'
END
CLOSE @getid
DEALLOCATE @getid
If I uncomment the 'select' and comment the 'UPDATE' I get a good result but with the 'UPDATE' nothing happens (although management studio says a good amount of record have been affected)
Maybe I'm going completely in the wrong direction, so anyone out there to help me a bit? Doesn't have to be the path I'm going; as long as it works and it's proper code 🙂
October 5, 2016 at 10:35 am
Right now, your code can be simplified to the following.
UPDATE dbo.SaltoCardholders
SET <column_x> = '<some_value>'
WHERE GPF5 = 'VWB';
I'm sure that doesn't solve your problem, but we need more information to get you to a solution. Please, read the links in my signature to understand what we need and how (DDL, sample data & expected results).
You don't need to include all of the columns in the tables, but be sure to present the ones used (names can be changed).
October 5, 2016 at 10:42 am
Luis Cazares (10/5/2016)
Right now, your code can be simplified to the following.
UPDATE dbo.SaltoCardholders
SET <column_x> = '<some_value>'
WHERE GPF5 = 'VWB';
I'm sure that doesn't solve your problem, but we need more information to get you to a solution. Please, read the links in my signature to understand what we need and how (DDL, sample data & expected results).
You don't need to include all of the columns in the tables, but be sure to present the ones used (names can be changed).
Thank you Luis! I will! I'll read your links first and will come up with more data. But that'll be tomorrow.
October 5, 2016 at 2:48 pm
My best guess is something like this:
UPDATE sc
SET column_x = ISNULL(sot1.value, '') + ISNULL(sot2.value, '') + ISNULL(sot3.value, '')
FROM dbo.SaltoCardholders sc
LEFT OUTER JOIN dbo.some_other_table1 sot1 ON sot1.lookup_col = sc.loc
LEFT OUTER JOIN dbo.some_other_table2 sot2 ON sot2.lookup_col = sc.id
LEFT OUTER JOIN dbo.some_other_table3 sot3 ON sot3.lookup_col = sc.name
WHERE sc.id > '' OR sc.name > '' OR sc.loc > ''
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 6, 2016 at 1:12 pm
Okay, so I'll to post some relevant info and while keeping it compact.
This is table: [dbo].[SaltoCardholders]
with some columns:
[ExtUserID] [nvarchar](32)
[FirstName] [nvarchar](40)
[LastName] [nvarchar](40)
[GPF1] [nvarchar](32)
[GPF2] [nvarchar](32)
[GPF3] [nvarchar](32)
[GPF4] [nvarchar](32)
[GPF5] [nvarchar](32)
[ExtAccessLevelIDList] [nvarchar](1024)
[AutoKeyEditROMCode] [varchar](14)
[UserActivation] [datetime]
[UserExpirationExpDate] [datetime]
[ProcessedDateTime] [datetime]
[ErrorCode] [int]
[ErrorMessage] [nvarchar](512)
[Functie_full] [nvarchar](250)
For every row I need to read the values from GPF2 , GPF3 and GPF5. Then each of those three values I need to look up a matching value in three lookup tables. The three values from the three lookup tables form one string. The merged string need to go into [ExtAccessLevelIDList].
Some sample data:
ExtUserIDFirstNameLastNameGPF1GPF2 GPF3 GPF4 GPF5 ExtAccessLevelIDList
1006001jdsfn kdmg 100600manager Business Unit loc_1Stg CuNULL
100601kgermgkldfyy 10060kmdfmdlab loc_3NRB NULL
100703khthl kmg 10070some jobsales loc_1 CM NULL
100901krgk ewrk 10090blabla ekfmef loc_4 NRB NULL
Lookup tables something like (sorry they haven't been created yet):
lookup GPF2:
source | output
Manager man
blabla bl
kmdfmd kmm
lookup for GPF3:
source | output
lab lab
sales sal
etc, about the same for GPF5
So the output string all together could be something like 'mansalxx' which need to go into [ExtAccessLevelIDList]
What would be the way to go?
October 6, 2016 at 1:47 pm
Have you tried Scott's approach?
I also want to add that this violates 2nd Normal Form. Have you considered how you want to handle it when one of the codes gets updated?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 7, 2016 at 12:27 am
drew.allen (10/6/2016)
Have you tried Scott's approach?I also want to add that this violates 2nd Normal Form. Have you considered how you want to handle it when one of the codes gets updated?
Drew
Yes I'm gonna try Scott's approach now. It was late last night when I posted my last reply, so new day, new chances 🙂
About the violation, it's an existing staging area of an existing programm. Nothing I can (am allowed to) change about that, so it has te be this way I'm afraid
October 7, 2016 at 5:09 am
Okay, so I created and (partly) filled the three lookup tables and took a go on Scott's approach.
Turned out I was thinking waaay too complex. I ended up with a very simple query and all seems to work fine:
UPDATE dbo.SaltoCardholders
SET ExtAccessLevelIDList = (select ISNULL((select code from dbo.Tbl_LU_Loc where FullDescr = GPF5), '_') +
ISNULL((select code from dbo.Tbl_LU_Dept where FullDescr = GPF3), '_') +
ISNULL((select code from dbo.Tbl_LU_Func where FullDescr = GPF2),'_'))
So, sorry for the noob question. 😉
October 7, 2016 at 6:42 am
Super_Grover (10/7/2016)
Okay, so I created and (partly) filled the three lookup tables and took a go on Scott's approach.Turned out I was thinking waaay too complex. I ended up with a very simple query and all seems to work fine:
UPDATE dbo.SaltoCardholders
SET ExtAccessLevelIDList = (select ISNULL((select code from dbo.Tbl_LU_Loc where FullDescr = GPF5), '_') +
ISNULL((select code from dbo.Tbl_LU_Dept where FullDescr = GPF3), '_') +
ISNULL((select code from dbo.Tbl_LU_Func where FullDescr = GPF2),'_'))
So, sorry for the noob question. 😉
If you examine the execution plan of this query and tweak the query to more closely match what SQL Server is doing, you end up with this:
UPDATE sc SET
ExtAccessLevelIDList = ISNULL(l.code, '_') + ISNULL(d.code, '_') + ISNULL(f.code,'_')
FROM dbo.SaltoCardholders sc
LEFT JOIN dbo.Tbl_LU_Loc l
ON l.FullDescr = sc.GPF5
LEFT JOIN dbo.Tbl_LU_Dept d
ON d.FullDescr = sc.GPF3
LEFT JOIN dbo.Tbl_LU_Func f
ON f.FullDescr = sc.GPF2
- which I think is the same as Scott's query 😉
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
October 7, 2016 at 9:55 am
ChrisM@Work (10/7/2016)
Super_Grover (10/7/2016)
Okay, so I created and (partly) filled the three lookup tables and took a go on Scott's approach.Turned out I was thinking waaay too complex. I ended up with a very simple query and all seems to work fine:
UPDATE dbo.SaltoCardholders
SET ExtAccessLevelIDList = (select ISNULL((select code from dbo.Tbl_LU_Loc where FullDescr = GPF5), '_') +
ISNULL((select code from dbo.Tbl_LU_Dept where FullDescr = GPF3), '_') +
ISNULL((select code from dbo.Tbl_LU_Func where FullDescr = GPF2),'_'))
So, sorry for the noob question. 😉
If you examine the execution plan of this query and tweak the query to more closely match what SQL Server is doing, you end up with this:
UPDATE sc SETExtAccessLevelIDList = ISNULL(l.code, '_') + ISNULL(d.code, '_') + ISNULL(f.code,'_')
FROM dbo.SaltoCardholders sc
LEFT JOIN dbo.Tbl_LU_Loc l
ON l.FullDescr = sc.GPF5
LEFT JOIN dbo.Tbl_LU_Dept d
ON d.FullDescr = sc.GPF3
LEFT JOIN dbo.Tbl_LU_Func f
ON f.FullDescr = sc.GPF2
- which I think is the same as Scott's query 😉
You're absolutely right Chris. Works excellent and does the same. But it's better coding. I need to go advanced but still learning 😉
Thanks very much Chris and Scott!
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply