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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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