August 8, 2017 at 12:18 am
Hi all,
I am trying to UPDATE the column SupplementaryInformation in the temp table #temptitle3, where the title is like the column val, TEMP Table @textsvalueBG. Val is a series of strings.
Required rows are is easily returned in regular T-SQL, but the values are stored in a matrix table and so are Dynamic.
--Simple easy statement
--Source table WINF with the lookup values using wildcards
--lookup values are also stored in Matrix table
--Returns 17770 rows
SELECT *
FROM [WINF] w
WHERE W.title like '%(%' AND WINFkey like 'BG%'
AND (Title like '%INCIDENTAL%'
OR Title like '%PART%'
OR Title like '%PT%'
OR Title like '%THEME%'
OR Title like '%CONCERT%'
OR Title like '%EPS%'
OR Title like '%SCORE%'
OR Title like '%DO NOT USE%'
OR Title like '%CUES%'
OR Title like '%EMI%'
OR Title like '%VOCAL%'
OR Title like '%INSTRUMENTAL%'
OR Title like '%MIX%'
OR Title like '%FROM%'
OR Title like '%END%'
OR Title like '%CONCERT%'
OR Title like '%VERSION%'
OR Title like '%EDIT%'
OR Title like '%REPRISE%'
OR Title like '%OVERTURE%'
OR Title like '%FEAT%'
OR Title like '%BONUS%'
OR Title like '%OPUS%'
OR Title like '%MAIN%'
OR Title like '%CREDIT%'
OR Title like '%BUILD@%'
OR Title like '%FR%'
OR Title like '%TV%'
OR Title like '%FILM%'
OR Title like '%KARAOKE%'
OR Title like '%PRODUCED%'
OR Title like '%OS CUES%'
OR Title like '%O/S PERF%'
OR Title like '%BACKGROUND%'
OR Title like '%BKG%' )
The Dynamic statement is not returning the same number of rows.
IF OBJECT_ID('tempdb..#temptitle3') IS NOT NULL
DROP TABLE #temptitle3
DECLARE @Information3 VARCHAR(MAX)
SELECT WINFkey,Title,SupplementaryInformation = @Information3
INTO #temptitle3
FROM [WINF]
WHERE title like '%(%' and (WINFkey Like 'BG%' )
DECLARE @textsvalueBG TABLE(catgeory VARCHAR(2),val VARCHAR(100),SupplementaryInformation VARCHAR(100))
Insert into @textsvalueBG SELECT category,texts , '' from [rpt].[Matrix] WHERE Category in ('BG')
UPDATE #temptitle3
SET SupplementaryInformation = SUBSTRING(Title,CHARINDEX('(',Title),LEN(Title))
FROM #temptitle3
WHERE RTRIM(LTRIM(SUBSTRING(Title,CHARINDEX('(',Title)+1 ,LEN(Title))))
IN
(select RTRIM(LTRIM(val)) from @textsvalueBG)
--This only returns 91 rows
SELECT t.*, w.title 'Originial Title'
FROM #temptitle3 t
JOIN [WINF] w
ON t.WINFkey = w.WINFkey
WHERE SupplementaryInformation is not null
Any assistance would be appreciated.
August 8, 2017 at 6:16 am
The second query has an additional WHERE clause that could be affecting the output. Validate that against the first query.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 8, 2017 at 5:44 pm
I think the problem is the IN it needs to be a IN Like from title to %val%. How do I make it a like?
UPDATE #temptitle3
SET SupplementaryInformation = SUBSTRING(Title,CHARINDEX('(',Title),LEN(Title))
FROM #temptitle3
WHERE RTRIM(LTRIM(SUBSTRING(Title,CHARINDEX('(',Title)+1,LEN(Title))))
IN
(select RTRIM(LTRIM(val)) from @textsvalueBG)
SELECT RTRIM(LTRIM(SUBSTRING(Title,CHARINDEX('(',Title)+1,LEN(Title))))
FROM #temptitle3
SELECT RTRIM(LTRIM(val)) from @textsvalueBG
[Title] Search Values:
GREECE)
EMI GOLDEN TORCH)
AIRSHIP DISASTER)
HOLLAND)
BATTLE FOR ANZIO)
LASSIE)
[VAL] Matrix Value:
INCIDENTAL
August 8, 2017 at 6:03 pm
I found a way to do the like in the join that appears to be working.
UPDATE #temptitle3
SET SupplementaryInformation = SUBSTRING(Title,CHARINDEX('(',Title),LEN(Title))
FROM #temptitle3
JOIN @textsvalueBG
ON RTRIM(LTRIM(SUBSTRING(Title,CHARINDEX('(',Title)+1,LEN(Title)))) LIKE RTRIM(LTRIM(val)) + '%'
August 14, 2017 at 12:29 pm
SELECT column1 FROM table1 AS T1 INNER JOIN Params AS P1 ON T1.column LIKE '%' + P1.param + '%';
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply