Issue with slow find and replace function code from a lookup table can I do this in a better way

• Northern Monkey

  Ten Centuries

  Points: 1017

  More actions

  August 3, 2016 at 5:10 am

  #328806

  I have some dim tables for find and replace for colours and sizes as I have multiple columns in a table that could hold the value see below

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  The function cod is here

  ALTER FUNCTION [dbo].[UTfn_LikeColourTableFilter]

  (

  @Value VARCHAR(8000)

  )

  RETURNS TABLE AS

  RETURN

  WITH ColourList AS ( -- replace this section with your Colour table when the time comes.

  SELECT SCT.LookupValue,SCT.DisplayInFilter FROM dbo.Dim_Colour SCT

  ),

  LookupList

  AS

  (

  SELECT

  ROW_NUMBER() OVER (ORDER BY LEN(cl.LookupValue) DESC) AS rn,-- prevents "Yellow" from butchering up "LightGoldenRodYellow"..

  cl.LookupValue,cl.DisplayInFilter

  FROM

  dbo.Dim_Colour cl

  WHERE

  @Value LIKE '%' + cl.LookupValue + '%'

  ), RecursiveReplace AS (

  -- -- My select 1

  SELECT cis.DisplayInFilter AS ReturnString,

  cis.rn

  FROM

  LookupList cis

  WHERE

  cis.rn = 1

  UNION ALL

  -- -- My select 2

  SELECT cis.DisplayInFilter AS ReturnString,

  cis.rn

  FROM

  LookupList cis

  JOIN RecursiveReplace rr

  ON cis.rn = rr.rn + 1

  )

  SELECT TOP 1

  rr.ReturnString

  FROM

  RecursiveReplace rr

  ORDER BY

  rr.rn DESC;

  The dbo.Dim_Colour table has 519 rows

  I use the same processes for size but that dim table has over 2500 rows and runs even slower

  I did think about adding another parameter for size type but couldn't get it to work 🙁

  The function was the one I used from a post here about a year ago but it's just to slow 🙁

  Many thanks for any help with this issue 🙂

• Phil Parkin

  SSC Guru

  Points: 246959

  More actions

  August 3, 2016 at 5:56 am

  #1893448

  I cannot see a question here. What is the 'issue', exactly?

  Can you provide some test data and expected results? That is, a working test harness, rather than just code fragments.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 3, 2016 at 7:11 am

  #1893474

  Northern Monkey (8/3/2016)

  ---

  I have some dim tables for find and replace for colours and sizes as I have multiple columns in a table that could hold the value see below

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  The function cod is here

  ALTER FUNCTION [dbo].[UTfn_LikeColourTableFilter]

  (

  @Value VARCHAR(8000)

  )

  RETURNS TABLE AS

  RETURN

  WITH ColourList AS ( -- replace this section with your Colour table when the time comes.

  SELECT SCT.LookupValue,SCT.DisplayInFilter FROM dbo.Dim_Colour SCT

  ),

  LookupList

  AS

  (

  SELECT

  ROW_NUMBER() OVER (ORDER BY LEN(cl.LookupValue) DESC) AS rn,-- prevents "Yellow" from butchering up "LightGoldenRodYellow"..

  cl.LookupValue,cl.DisplayInFilter

  FROM

  dbo.Dim_Colour cl

  WHERE

  @Value LIKE '%' + cl.LookupValue + '%'

  ), RecursiveReplace AS (

  -- -- My select 1

  SELECT cis.DisplayInFilter AS ReturnString,

  cis.rn

  FROM

  LookupList cis

  WHERE

  cis.rn = 1

  UNION ALL

  -- -- My select 2

  SELECT cis.DisplayInFilter AS ReturnString,

  cis.rn

  FROM

  LookupList cis

  JOIN RecursiveReplace rr

  ON cis.rn = rr.rn + 1

  )

  SELECT TOP 1

  rr.ReturnString

  FROM

  RecursiveReplace rr

  ORDER BY

  rr.rn DESC;

  The dbo.Dim_Colour table has 519 rows

  I use the same processes for size but that dim table has over 2500 rows and runs even slower

  I did think about adding another parameter for size type but couldn't get it to work 🙁

  The function was the one I used from a post here about a year ago but it's just to slow 🙁

  Many thanks for any help with this issue 🙂

  We need some sample data. The function seems to be doing unnecessary work. Also, calling it up to 20 times per row is extremely ineffective.

  Does the following function returns the same results?

  ALTER FUNCTION [dbo].[UTfn_LikeColourTableFilter]

  (

  @Value VARCHAR(8000)

  )

  RETURNS TABLE AS

  RETURN

  WITH LookupList AS (

  SELECT ROW_NUMBER() OVER (ORDER BY LEN(cl.LookupValue)) AS rn,-- prevents "Yellow" from butchering up "LightGoldenRodYellow"..

  cl.DisplayInFilter

  FROM dbo.Dim_Colour cl

  WHERE @Value LIKE '%' + cl.LookupValue + '%'

  )

  SELECT DisplayInFilter AS ReturnString

  FROM LookupList

  WHERE rn = 1;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Northern Monkey

  Ten Centuries

  Points: 1017

  More actions

  August 3, 2016 at 7:16 am

  #1893476

  Hi Phil

  Sorry for the vague question and not making things clear

  The main question is can I use a different method / code for the function as this one seems slow? it's checking for any matching text in the columns in the case statement lower down the page I've also included a small result set from using the function at the bottom of the post along with an extract from the table for the colours it looks for.

  CREATE FUNCTION [dbo].[UTfn_LikeColourTableFilter]

  (

  @Value VARCHAR(8000)

  )

  RETURNS TABLE AS

  RETURN

  WITH ColourList AS ( -- replace this section with your Colour table when the time comes.

  SELECT SCT.LookupValue,SCT.DisplayInFilter FROM dbo.Dim_Colour SCT

  ),

  LookupList

  AS

  (

  SELECT

  ROW_NUMBER() OVER (ORDER BY LEN(cl.LookupValue) DESC) AS rn,-- prevents "Yellow" from butchering up "LightGoldenRodYellow"..

  cl.LookupValue,cl.DisplayInFilter

  FROM

  dbo.Dim_Colour cl

  WHERE

  @Value LIKE '%' + cl.LookupValue + '%'

  ), RecursiveReplace AS (

  -- -- My select 1

  SELECT cis.DisplayInFilter AS ReturnString,

  cis.rn

  FROM

  LookupList cis

  WHERE

  cis.rn = 1

  UNION ALL

  -- -- My select 2

  SELECT cis.DisplayInFilter AS ReturnString,

  cis.rn

  FROM

  LookupList cis

  JOIN RecursiveReplace rr

  ON cis.rn = rr.rn + 1

  )

  SELECT TOP 1

  rr.ReturnString

  FROM

  RecursiveReplace rr

  ORDER BY

  rr.rn DESC;

  Calling the function in the select to find if a column contains a colour name within the text and if it finds one it moves onto the next row to processes

  I would like to know if I could do this any better please?

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  Here is a sample from the dim_Colour table used in the function

  LookupValue|LookupFirstValue|LookupFirstValueNoSpace|LookupLastValue|LookupLastValueNoSpace|DisplayInDetails|DisplayInFilter|ReplaceValue|ReplaceFirstValue|ReplaceLastValue

  AliceBlue|AliceBlue|AliceBlue| AliceBlue|AliceBlue| Alice Blue|Blue

  Azure|Azure|Azure| Azure|Azure| Azure|Assorted Colours

  Beige|Beige|Beige| Beige|Beige| Beige|Beige

  Bisque|Bisque|Bisque| Bisque|Bisque| Bisque|Bisque

  Black|Black|Black| Black|Black| Black|Black

  Black Combination|Black Combination|Black Combination| Black Combination|Black Combination| Black Combination|Black

  Black Currant|Black Currant|Black Currant| Black Currant|Black Currant| Black Currant|Black

  return from the case with the source columns containing the data

  SELECT top 1

  SourceColuumnName = 'colour'

  ,SFA.colour

  ,SFA.model_number

  ,SFA.commission_group

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,ResultFromFunction = CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  FROM dbo.Staging_Feed_AffW SFA

  WHERE SFA.colour like '% Black %'

  UNION

  SELECT top 1

  SourceColuumnName = 'model_number'

  ,SFA.colour

  ,SFA.model_number

  ,SFA.commission_group

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,ResultFromFunction = CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  FROM dbo.Staging_Feed_AffW SFA

  WHERE SFA.model_number like '% Black %'

  UNION

  SELECT top 1

  SourceColuumnName = 'commission_group'

  ,SFA.colour

  ,SFA.model_number

  ,SFA.commission_group

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,ResultFromFunction = CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  FROM dbo.Staging_Feed_AffW SFA

  WHERE SFA.commission_group like '% Black %'

  UNION

  SELECT top 1

  SourceColuumnName = 'custom_1'

  ,SFA.colour

  ,SFA.model_number

  ,SFA.commission_group

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,ResultFromFunction = CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  FROM dbo.Staging_Feed_AffW SFA

  WHERE SFA.custom_1 like '% Black %'

  UNION

  SELECT top 1

  SourceColuumnName = 'custom_2'

  ,SFA.colour

  ,SFA.model_number

  ,SFA.commission_group

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,ResultFromFunction = CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  FROM dbo.Staging_Feed_AffW SFA

  WHERE SFA.custom_2 like '% Black %'

  UNION

  SELECT top 1

  SourceColuumnName = 'custom_3'

  ,SFA.colour

  ,SFA.model_number

  ,SFA.commission_group

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,ResultFromFunction = CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  FROM dbo.Staging_Feed_AffW SFA

  WHERE SFA.custom_3 like '% Black %'

  UNION

  SELECT top 1

  SourceColuumnName = 'product_name'

  ,SFA.colour

  ,SFA.model_number

  ,SFA.commission_group

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,ResultFromFunction = CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  FROM dbo.Staging_Feed_AffW SFA

  WHERE SFA.product_name like '% Black %'

  SourceColuumnName|colour|model_number|commission_group|custom_1|custom_2|custom_3|ResultFromFunction

  colour|Intense Black (01)|B1855713|bodycare|Health and Beauty > Make-Up|adult|new|Black

  model_number|NULL|DRM-INFANTS BROOKLEE LACE BOOT - BLACK JAKE T CANVAS|NULL|NULL|NULL|NULL|Black

  custom_1|navy|356960249|NULL|Women > Dresses > Black Dresses|NULL|NULL|Black

  custom_2|NULL|NULL|NULL|Charges directly from the sun, no batteries required.|It has 3 x Accessories: Zipped case, Black travel pouch, Velcro strap|Complete with 10 x mobile device tips|Black

  custom_3|Pale pink|NULL|NULL|Jumpers|60cm for an XS + 2cm for each additional size|Soft to the touch and a flattering fit, this short sleeve round neck jumper is an everyday essential at a great value price. A perfect wardrobe addition which can be worn with black trousers or skirt for a smarter look to jeans for a more casual feel|Pink

  product_name|Black|NULL|NULL|6-11 Mens|NULL|NULL|Black

• Smendle

  Hall of Fame

  Points: 3969

  More actions

  August 3, 2016 at 7:23 am

  #1893477

  Please provide the following to help us help you:

  Table Definition:

  i.e.

  CREATE TABLE [dbo].[Dim_Colour](

  [colour] [varchar](30) NULL,

  [model_number] [varchar](30) NULL,

  [commission_group] [varchar](30) NULL,

  [custom_1] [varchar](30) NULL,

  [custom_2] [varchar](30) NULL,

  [custom_3] [varchar](30) NULL,

  [custom_4] [varchar](30) NULL,

  [custom_5] [varchar](30) NULL,

  [product_name] [varchar](30) NULL,

  [description] [varchar](512) NULL,

  [LookupValue] [varchar](30) NULL,

  [DisplayInFilter] [varchar](30) NULL

  ) ON [PRIMARY]

  Sample data....

  INSERT INTO Dim_colour (1,2,3,4,5,6)....

  THE FULL Query you are working with (already provided but add it again for clarity and ease of access)

  Select * from.....

  expected results (again you provided but please add it again to the same post for clarity and ease of access)

  col1 col2 col3

  value1 value2 value3

• Northern Monkey

  Ten Centuries

  Points: 1017

  More actions

  August 3, 2016 at 7:29 am

  #1893479

  Also I would like to add a parameter to be passed from the case statement to the function to limit the type of product I have a column named TypeID INT in the dim_colour table and at the time of processing I would like to pass 1 or 2 or any other number depending on the type of product to give me more functionality 🙂

  Could that be done please? if so could you give examples of how as I have been trying to get this to work for the past few days 🙁

  S

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 3, 2016 at 7:54 am

  #1893486

  Northern Monkey (8/3/2016)

  ---

  Also I would like to add a parameter to be passed from the case statement to the function to limit the type of product I have a column named TypeID INT in the dim_colour table and at the time of processing I would like to pass 1 or 2 or any other number depending on the type of product to give me more functionality 🙂

  Could that be done please? if so could you give examples of how as I have been trying to get this to work for the past few days 🙁

  S

  This whole process looks to me to be massively over-engineered. If you can provide table scripts and scripts to populate those tables with some sample data, we can write code against it. Something like this should do:

  SELECT sfa.*, rc.FilterColour

  FROM Whatever AS sfa

  OUTER APPLY (

  SELECT TOP 1

  FilterColour = cl.DisplayInFilter

  FROM dbo.Dim_Colour cl

  CROSS APPLY (

  SELECT * FROM (VALUES

  (1, sfa.Colour),

  (2, sfa.model_number) -- continue the list here

  ) x ([Priority], Colour)

  ) ls

  WHERE ls.Colour LIKE '%' + cl.LookupValue + '%'

  ORDER BY [Priority], LEN(cl.LookupValue)

  ) rc

  - and doesn't require much in the way of explanation or maintenance.

  Keep it simple 😉 Some bu88er's made this far harder for you than it should be.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  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

• Northern Monkey

  Ten Centuries

  Points: 1017

  More actions

  August 3, 2016 at 8:36 am

  #1893500

  Arrrrrrrrr I got an error trying to post the info you wanted

  CREATE TABLE [dbo].[Staging_Feed_AffW](

  [product_name] [nvarchar](max) NULL,

  [commission_group] [nvarchar](max) NULL,

  [model_number] [nvarchar](max) NULL,

  [colour] [nvarchar](max) NULL,

  [keywords] [nvarchar](max) NULL,

  [custom_1] [nvarchar](max) NULL,

  [custom_2] [nvarchar](max) NULL,

  [custom_3] [nvarchar](max) NULL,

  [custom_4] [nvarchar](max) NULL,

  [custom_5] [nvarchar](max) NULL,

  [DataID] [int] IDENTITY(1,1) NOT NULL,

  CONSTRAINT [PK_Staging_Feed_AffW2] PRIMARY KEY NONCLUSTERED

  (

  [DataID] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  SET IDENTITY_INSERT [dbo].[Staging_Feed_AffW] ON

  GO

  INSERT INTO Staging_Feed_AffW VALUES ('2.5W 3600mAh Dual-USB 8-LED White Light Solar Powered Charger Power Bank - Black + Orange','','','','','','','','','','1872819)

  INSERT INTO Staging_Feed_AffW VALUES ('5 BLACK Roses handtied into a Luxury Bouquet','','','','','','','','','','55544)

  INSERT INTO Staging_Feed_AffW VALUES ('Black Watch Strap 18mm width Westfalia','','T+Q BLACK 22 MM','','','','','','','','715875)

  INSERT INTO Staging_Feed_AffW VALUES ('Brother LC980 B/C/M/Y Multipack Compatible Ink Cartridge by JetTec - B98','','','','101B0980021, 101B0980021, jet tec, jettec, ink cartridge, ink cartridges, inkjet cartridge, inkjet cartridges, inkjet, ink jet, ink refill, ink, cartridge, cartridges, cheap ink, cheap inks, printer ink, printer inks, printer cartridge, printer cartridges, compatible, brother, lc980, b98, multipack, multi pack, b98, cmyk, cmy, bcmy, ','2 Year Warranty','OEM Ref: LC980/1100 Black, Cyan, Magenta, Yellow Multipack','Jettec Ref: B98B/C/M/Y','Capacity: 80% Black 100% Extra Colour','Ink ml: 1x18 3x13 ','2695659)

  INSERT INTO Staging_Feed_AffW VALUES ('Cash Short Sleeve Sweater','','','Aqua','','Jumpers','60cm for an XS + 2cm for each additional size','Soft to the touch and a flattering fit, this short sleeve round neck jumper is an everyday essential at a great value price. A perfect wardrobe addition which can be worn with black trousers or skirt for a smarter look to jeans for a more casual feel','Knitted','','2621893)

  INSERT INTO Staging_Feed_AffW VALUES ('Celestron PentaView LCD Digital Microscope','','','','celestron, 44348, c44348, 00150884, 150884, penta view, pentaview, lcd microscope, digital microscope, telescope accessories, ','Illumination: Upside Down ','LCD Monitor 4.3” ','Max. Magnification Factor: 2400','Colour: Black ','','2696168)

  INSERT INTO Staging_Feed_AffW VALUES ('Core Print 1460 8 Eye Boot - Black Jake Printed T Canvas','','DRM-CORE PRINT 1460 8 EYE BOOT - BLACK JAKE PRINTED T CANVAS','','','','','','','','19682)

  INSERT INTO Staging_Feed_AffW VALUES ('DAOUDA 3 Drawer Metal Chest of Drawers','','','walnut stained oak with black drawers','','','','','','','2028092)

  INSERT INTO Staging_Feed_AffW VALUES ('Delta Sledge 2 Pack - Black','','Delta Sledge 2 Pack - BlackSet includes: 2 Black SledgeErgonomic designHigh density plasticRope pull for easy manoeuvrabilityManufactured in the UKNEXT DAY DELIVERY','','','','','','','','977996)

  INSERT INTO Staging_Feed_AffW VALUES ('Delta Sledge 2 Pack - Blue & Black','','Delta Sledge 2 Pack - Blue & BlackSet includes: 1 Blue Sledge 1 Black SledgeErgonomic designHigh density plasticRope pull for easy manoeuvrabilityManufactured in the UKNEXT DAY DELIVERY','','','','','','','','977998)

  INSERT INTO Staging_Feed_AffW VALUES ('EPITRE Solid Oak & Metal Desk','','','light oak wood with black feet','','','','','','','2025657)

  INSERT INTO Staging_Feed_AffW VALUES ('EPITRE Solid Oak & Metal Desk','','','walnut stained oak with black feet','','','','','','','2025660)

  INSERT INTO Staging_Feed_AffW VALUES ('Hama Omega Premium I Tripod','','','','4260, 4260, 4260, hama, omega, tripod, omega, premium, omega premium i tripod,','2 Year Warranty','3-D panorama head','Includes black tripod case ','Weight: 2.5 kg','Height: Min. 56.5 cm / Max. 141 cm','2694868)

  INSERT INTO Staging_Feed_AffW VALUES ('Hama Omega Premium II Tripod','','','','4261, 4261, 4261, hama, omega, tripod, omega, premium, omega premium ii tripod, 004261,','2 Year Warranty','3-D panorama head','Includes black tripod case ','Weight: 3.1 kg','Height: Min. 62.5 cm / Max. 148 cm','2694869)

  INSERT INTO Staging_Feed_AffW VALUES ('HP300 BLK CC640EE Black Remanufactured Ink Cartridge by JetTec - H300B','','','','jettec, printer ink, ink, ink cartridge, 101H030001, hewlett packard, hp, hp300blk, hp300 -blk, 300, h300b, hp300, hp300 blk, CC640EE, ','2 Year Warranty','OEM Ref: HP300/CC640EE Black ','Jettec Ref: H300B','Capacity: Standard','Ink ml: 7','2695603)

  INSERT INTO Staging_Feed_AffW VALUES ('HP350 CB335EE Black Remanufactured Ink Cartridge by JetTec - H350','','','','101H035001, jet tec, jettec, ink cartridge, ink cartridges, inkjet cartridge, inkjet cartridges, inkjet, ink jet, ink refill, ink, cartridge, cartridges, cheap ink, cheap inks, printer ink, printer inks, printer cartridge, printer cartridges, compatible, remanufactured, hewlett packard, hp, hp350, hp350, CB335EE, m350l, h350, ','2 Year Warranty','OEM Ref: HP350 CB335 Black ','Jettec Ref: H350','Capacity: Standard','Ink ml: 10','2695858)

  INSERT INTO Staging_Feed_AffW VALUES ('HP364XL CN684EE Black Remanufactured Ink Cartridge by JetTec - H364BXL','','','','101H136430, jet tec, jettec, ink cartridge, ink cartridges, inkjet cartridge, inkjet cartridges, inkjet, ink jet, ink refill, ink, cartridge, cartridges, cheap ink, cheap inks, printer ink, printer inks, printer cartridge, printer cartridges, compatible, remanufactured, hewlett packard, hp, hp364, cn684EE, hp364bxl, hp364xl, hp364 xl, h364bxl,','2 Year Warranty','OEM Ref: HP364XL/CN684EE Black ','Jettec Ref: H364BXL','Capacity: Standard','Ink ml: 18','2695605)

  INSERT INTO Staging_Feed_AffW VALUES ('HYELEC MS6208A Digital 1.7" LCD Contact Tachometer - Black + Yellow (4 x AAA)','','','','','','','','','','1872818)

  INSERT INTO Staging_Feed_AffW VALUES ('HYELEC MS8233C 1.8" Manual Digital Multimeter w/ Temperature Probe - Black + Yellow (1 x 6F22)','','','','','','','','','','1872817)

  INSERT INTO Staging_Feed_AffW VALUES ('Infants Brooklee B Lace Boot - Black Wonderflora','','DRM-INFANTS BROOKLEE B LACE BOOT - BLACK WONDERFLORA','','','','','','','','17940)

  INSERT INTO Staging_Feed_AffW VALUES ('Infants Brooklee Lace Boot - Black Jake T Canvas','','DRM-INFANTS BROOKLEE LACE BOOT - BLACK JAKE T CANVAS','','','','','','','','17225)

  INSERT INTO Staging_Feed_AffW VALUES ('Infants Brooklee Lace Boot - Black Wonderflora','','DRM-INFANTS BROOKLEE LACE BOOT - BLACK WONDERFLORA','','','','','','','','17949)

  INSERT INTO Staging_Feed_AffW VALUES ('Juniors Everley Lace Shoe - Black Softy T','','DRM-JUNIORS EVERLEY SHOE BLACK SOFTY T','','','','','','','','17958)

  INSERT INTO Staging_Feed_AffW VALUES ('Kodak 10B Black & 10C Colour Multipack Compatible Ink Cartridge by JetTec - K10B/K10C','','','','101K001036, jet tec, jettec, ink cartridge, ink cartridges, inkjet cartridge, inkjet cartridges, inkjet, ink jet, ink refill, ink, cartridge, cartridges, cheap ink, cheap inks, printer ink, printer inks, printer cartridge, printer cartridges, compatible, kodak, kodak 10, 10b/10c, k10b/k10c, multipack, ','2 Year Warranty','OEM Ref: Kodak 10 Black and Colour Multipack','Jettec Ref: K10B/C','Capacity: Standard','Ink ml: ','2695641)

  INSERT INTO Staging_Feed_AffW VALUES ('Lafuma Canvas Folding chair','','','white with black braiding','','','','','','','2024099)

  INSERT INTO Staging_Feed_AffW VALUES ('Lexmark 10N0016 Black Remanufactured Ink Cartridge by JetTec - L16','','','','101L001601, 101L001601, jet tec, jettec, ink cartridge, ink cartridges, inkjet cartridge, inkjet cartridges, inkjet, ink jet, ink refill, ink, cartridge, cartridges, cheap ink, cheap inks, printer ink, printer inks, printer cartridge, printer cartridges, compatible, lexmark, 10n0016, ml16, 862jt, l16,','2 Year Warranty','OEM Ref: 10N0016 Black ','Jettec Ref: L16','Capacity: Standard','Ink ml: 9.7','2695733)

  INSERT INTO Staging_Feed_AffW VALUES ('Lexmark 10N0017 Black Remanufactured Ink Cartridge by JetTec - L17','','','','101L001701, 101L001701, jet tec, jettec, ink cartridge, ink cartridges, inkjet cartridge, inkjet cartridges, inkjet, ink jet, ink refill, ink, cartridge, cartridges, cheap ink, cheap inks, printer ink, printer inks, printer cartridge, printer cartridges, compatible, lexmark, 10n0017, ml17, 1164jt, l17,','2 Year Warranty','OEM Ref: 10N0017 Black ','Jettec Ref: L17','Capacity: Up to50% Extra','Ink ml: 16','2695743)

  INSERT INTO Staging_Feed_AffW VALUES ('Lexmark 17G0050 Black Remanufactured Ink Cartridge by JetTec - L50','','','','101L005001, 101L005001, jet tec, jettec, ink cartridge, ink cartridges, inkjet cartridge, inkjet cartridges, inkjet, ink jet, ink refill, ink, cartridge, cartridges, cheap ink, cheap inks, printer ink, printer inks, printer cartridge, printer cartridges, compatible, lexmark, 17g0050, ml50, 796jt, l50,','2 Year Warranty','OEM Ref: 17G0050 Black ','Jettec Ref: L50','Capacity: Standard','Ink ml: 26','2695731)

  INSERT INTO Staging_Feed_AffW VALUES ('Lexmark 18L0032 Black Remanufactured Ink Cartridge by JetTec - L82','','','','101L008201, 101L008201, jet tec, jettec, ink cartridge, ink cartridges, inkjet cartridge, inkjet cartridges, inkjet, ink jet, ink refill, ink, cartridge, cartridges, cheap ink, cheap inks, printer ink, printer inks, printer cartridge, printer cartridges, compatible, lexmark, 18l0032, ml82, 1166jt, l82,','2 Year Warranty','OEM Ref: 18L0032 Black Lightfast Ink 82','Jettec Ref: L82','Capacity: Standard','Ink ml: 27','2695737)

  INSERT INTO Staging_Feed_AffW VALUES ('Lexmark 18Y0144E Black Remanufactured Ink Cartridge by JetTec - L44','','','','101L004401, 101L004401, jet tec, jettec, ink cartridge, ink cartridges, inkjet cartridge, inkjet cartridges, inkjet, ink jet, ink refill, ink, cartridge, cartridges, cheap ink, cheap inks, printer ink, printer inks, printer cartridge, printer cartridges, compatible, lexmark, 18Y0144E, l44, 101L004401, no. 44, 44, ','2 Year Warranty','OEM Ref: 44 018Y0144 Black (High Capacity)','Jettec Ref: L44','Capacity: Standard','Ink ml: 19.5','2695649)

  INSERT INTO Staging_Feed_AffW VALUES ('Lindsie Black Asymmetrical Cross Back Slinky Dress-12','','','Black','','0.2000 lb','','TRUE','','','2687535)

  INSERT INTO Staging_Feed_AffW VALUES ('M&S Collection Fit & Flare Tea Dress','Free standard delivery on orders over £50.','P22455439','Navy','','1','','2016-03-02','Women --> Clothing --> Dresses --> Little Black Dresses','','743058)

  INSERT INTO Staging_Feed_AffW VALUES ('M&S Collection PETITE Sleeveless Maxi Dress','Free standard delivery on orders over £50.','P22448214','Black','','1','','2016-02-25','Women --> Clothing --> Dresses --> Little Black Dresses','','743298)

  INSERT INTO Staging_Feed_AffW VALUES ('M&S Collection Scoop Neck Maxi Dress','Free standard delivery on orders over £50.','P22445566','Black','','1','','2016-03-02','Women --> Clothing --> Dresses --> Little Black Dresses','','743287)

  INSERT INTO Staging_Feed_AffW VALUES ('M&S Collection Scoop Neck Maxi Dress','Free standard delivery on orders over £50.','P22445566','Navy','','1','','2016-03-02','Women --> Clothing --> Dresses --> Little Black Dresses','','743290)

  INSERT INTO Staging_Feed_AffW VALUES ('M&S Collection Sleeveless Ribbed Midi Dress','Free standard delivery on orders over £50.','P22459654','Black','','1','','2016-03-02','Women --> Clothing --> Dresses --> Little Black Dresses','','743031)

  INSERT INTO Staging_Feed_AffW VALUES ('M&S Collection Swing Dress','Free standard delivery on orders over £50.','P22432101','Black','','1','','2015-08-18','Women --> Clothing --> Dresses --> Little Black Dresses','','743096)

  INSERT INTO Staging_Feed_AffW VALUES ('Maybelline Lasting Drama Gel Eyeliner - Intense Black','bodycare','B1855713','Intense Black (01)','','Health and Beauty > Make-Up','adult','new','13-06-2013','','1031388)

  INSERT INTO Staging_Feed_AffW VALUES ('Maybelline Lasting Drama Gel Eyeliner - Intense Black','bodycare','B1855713','Intense Black (01)','','Health and Beauty > Make-Up','adult','new','13-06-2013','','460075)

  INSERT INTO Staging_Feed_AffW VALUES ('Maybelline Lasting Drama Gel Eyeliner (Various Shades)','bodycare','','Intense Black (01),Brown (02),Black Chrome','','Health and Beauty > Make-Up','adult','','05-07-2013','','1968453)

  INSERT INTO Staging_Feed_AffW VALUES ('NYMPHEA Pedestal Table with Glossy Top','','','pack of black + teal blue','','','','','','','2026915)

  INSERT INTO Staging_Feed_AffW VALUES ('Orchid Eos Massage Table','','Orchid Eos Massage TableAvailable in Blue, Black and WhiteFREE carry bagRemovable head and arm restsFully height adjustableNo stain PU leather coveringMemory foam face cradleLightweight beech frameExtra thick 5cm foam45° adjustable face cradleLength 192cm x Width 70cmFREE NEXT DAY DELIVERY AVAILABLE','','','','','','','','978035)

  INSERT INTO Staging_Feed_AffW VALUES ('Orchid Penia Massage Table','','Orchid Penia Massage TableAvailable in Pink, Black & CreamFREE carry bagRemovable head and arm restFully height adjustableNo stain PU leather coveringMemory foam face cradleLightweight beech frameExtra thick 6cm foam45° adjustable face cradleLength 186cm x Width 74cmFREE NEXT DAY DELIVERY AVAILABLE','','','','','','','','978037)

  INSERT INTO Staging_Feed_AffW VALUES ('PCI-e 16X to 16X Riser Card Extender Ribbon Cable w/ Molex Connector - Black + Grey (23cm)','','','','','','','','','','2975305)

  INSERT INTO Staging_Feed_AffW VALUES ('Per Una Plaited Neckline Belted Maxi Dress','Free standard delivery on orders over £50.','P60080790','Navy','','1','','2016-03-08','Women --> Clothing --> Dresses --> Little Black Dresses','','743160)

  INSERT INTO Staging_Feed_AffW VALUES ('Pixi Endless Silky Eye Pen No.2 Black Blue','bodycare','23001','Black Blue','','Health and Beauty > Make-Up','adult','new','22-06-2012','','459031)

  INSERT INTO Staging_Feed_AffW VALUES ('PowerTraveller Solarmonkey & Solarnut Portable Charger','','','','SOLM/NUT1-GRY, powertraveller, power traveller, solarmonkey, solar monkey, solarnut, solar nut, solm/nut1-gry, mp3 charger, mp3 travel charger, sansa charger, ipod charger, apple, iPod, iPhone, usb charger, charger kit, portable charger, mobile phone,','Charges directly from the sun, no batteries required.','It has 3 x Accessories: Zipped case, Black travel pouch, Velcro strap','Complete with 10 x mobile device tips','It is included in the Made for iPod program','Comes complete with a velcro strap, enabling you to attach it to a rucksack','2694914)

  INSERT INTO Staging_Feed_AffW VALUES ('REGLISSE Wood & Metal Scandinavian Sideboard','','','walnut stained with black doors','','','','','','','2026937)

  INSERT INTO Staging_Feed_AffW VALUES ('Road Racer Y Shaped 3 Wheel Scooter','','','White and Black ','','','','','','','1232136)

  INSERT INTO Staging_Feed_AffW VALUES ('Ruby Full Cup Non Wired Black Bra','','HP68220','BLACK','','36E','','HP682','HP68220','','2197271)

  INSERT INTO Staging_Feed_AffW VALUES ('Short Sleeve Cash Jumper','','','Pale pink','','Jumpers','60cm for an XS + 2cm for each additional size','Soft to the touch and a flattering fit, this short sleeve round neck jumper is an everyday essential at a great value price. A perfect wardrobe addition which can be worn with black trousers or skirt for a smarter look to jeans for a more casual feel','Knitted','','2621499)

  INSERT INTO Staging_Feed_AffW VALUES ('The Little Black Book Of Scales','','AM1005752','','','','','','','','321750)

  INSERT INTO Staging_Feed_AffW VALUES ('Womens Navy Mix & Match Fit and Flare Dress, navy','','356960249','navy','','Women > Dresses > Black Dresses','','','','','1106920)

  INSERT INTO Staging_Feed_AffW VALUES ('Womens Navy Mix & Match Fit and Flare Dress, navy','','356960249','navy','','Women > Dresses > Black Dresses','','','','','1106921)

  INSERT INTO Staging_Feed_AffW VALUES ('Womens Navy Mix & Match Fit and Flare Dress, navy','','356960249','navy','','Women > Dresses > Black Dresses','','','','','1106922)

  INSERT INTO Staging_Feed_AffW VALUES ('Womens Navy Mix & Match Fit and Flare Dress, navy','','356960249','navy','','Women > Dresses > Black Dresses','','','','','1106923)

  INSERT INTO Staging_Feed_AffW VALUES ('Womens Navy Mix & Match Fit and Flare Dress, navy','','356960249','navy','','Women > Dresses > Black Dresses','','','','','1106924)

  INSERT INTO Staging_Feed_AffW VALUES ('Womens Navy Mix & Match Fit and Flare Dress, navy','','356960249','navy','','Women > Dresses > Black Dresses','','','','','1106925)

  INSERT INTO Staging_Feed_AffW VALUES ('Womens Navy Mix & Match Fit and Flare Dress, navy','','356960249','navy','','Women > Dresses > Black Dresses','','','','','1106926)

  INSERT INTO Staging_Feed_AffW VALUES ('Womens Navy Mix & Match Fit and Flare Dress, navy','','356960249','navy','','Women > Dresses > Black Dresses','','','','','1106927)

  INSERT INTO Staging_Feed_AffW VALUES ('Womens Sleeveless Square Neck Floral Print Dress, Black, black','','2425378513','black','','Women > Dresses > Black Dresses','','','','','1109643)

  INSERT INTO Staging_Feed_AffW VALUES ('Womens Sleeveless Square Neck Floral Print Dress, Black, black','','2425378513','black','','Women > Dresses > Black Dresses','','','','','1109644)

  INSERT INTO Staging_Feed_AffW VALUES ('Womens TLC Leather Wide Fit Black Metal Button Sporty, black','','2853578513','black','','Women > See All Shoes & Sandals > wide fit > TLC Wide Fit','','','','','1094711)

  GO

  SET IDENTITY_INSERT [dbo].[Staging_Feed_AffW] OFF

  GO

  CREATE TABLE [dbo].[Dim_Colour](

  [LookupValue] [nvarchar](max) NULL,

  [LookupFirstValue] [nvarchar](max) NULL,

  [LookupFirstValueNoSpace] [nvarchar](max) NULL,

  [LookupLastValue] [nvarchar](max) NULL,

  [LookupLastValueNoSpace] [nvarchar](max) NULL,

  [DisplayInDetails] [nvarchar](max) NULL,

  [DisplayInFilter] [nvarchar](max) NULL,

  [ReplaceValue] [nvarchar](max) NULL,

  [ReplaceFirstValue] [nvarchar](max) NULL,

  [ReplaceLastValue] [nvarchar](max) NULL,

  [TypeID] [int] NULL,

  [DataID] [int] IDENTITY(1,1) NOT NULL

  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  SET IDENTITY_INSERT [dbo].[Dim_Colour] ON

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' AliceBlue ', N'AliceBlue ', N'AliceBlue', N' AliceBlue', N'AliceBlue', N' Alice Blue', N'Blue', N' ', N'', N'', 1, 1)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Amethyst ', N'Amethyst ', N'Amethyst', N' Amethyst', N'Amethyst', N' Amethyst', N'Amethyst', N' ', N'', N'', 1, 2)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' AntiqueWhite ', N'AntiqueWhite ', N'AntiqueWhite', N' AntiqueWhite', N'AntiqueWhite', N' Antique White', N'Off White', N' ', N'', N'', 1, 3)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Aqua ', N'Aqua ', N'Aqua', N' Aqua', N'Aqua', N' Aqua', N'Aqua', N' ', N'', N'', 1, 4)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Aquamarine ', N'Aquamarine ', N'Aquamarine', N' Aquamarine', N'Aquamarine', N' Aquamarine', N'Aqua', N' ', N'', N'', 1, 5)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Assorted Colours ', N'Assorted Colours ', N'Assorted Colours', N' Assorted Colours', N'Assorted Colours', N' Assorted Colours', N'Assorted Colours', N' ', N'', N'', 1, 6)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Azure ', N'Azure ', N'Azure', N' Azure', N'Azure', N' Azure', N'Assorted Colours', N' ', N'', N'', 1, 7)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Beige ', N'Beige ', N'Beige', N' Beige', N'Beige', N' Beige', N'Beige', N' ', N'', N'', 1, 8)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Bisque ', N'Bisque ', N'Bisque', N' Bisque', N'Bisque', N' Bisque', N'Bisque', N' ', N'', N'', 1, 9)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Black ', N'Black ', N'Black', N' Black', N'Black', N' Black', N'Black', N' ', N'', N'', 1, 10)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Black Combination ', N'Black Combination ', N'Black Combination', N' Black Combination', N'Black Combination', N' Black Combination', N'Black', N' ', N'', N'', 1, 11)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Black Currant ', N'Black Currant ', N'Black Currant', N' Black Currant', N'Black Currant', N' Black Currant', N'Black', N' ', N'', N'', 1, 12)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Black/Nude ', N'Black/Nude ', N'Black/Nude', N' Black/Nude', N'Black/Nude', N' Black / Nude', N'Assorted Colours', N' ', N'', N'', 1, 13)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Black/Pink ', N'Black/Pink ', N'Black/Pink', N' Black/Pink', N'Black/Pink', N' Black / Pink', N'Assorted Colours', N' ', N'', N'', 1, 14)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Black/Pink Ash ', N'Black/Pink Ash ', N'Black/Pink Ash', N' Black/Pink Ash', N'Black/Pink Ash', N' Black / Pink Ash', N'Assorted Colours', N' ', N'', N'', 1, 15)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Black/Skin ', N'Black/Skin ', N'Black/Skin', N' Black/Skin', N'Black/Skin', N' Black / Skin', N'Assorted Colours', N' ', N'', N'', 1, 16)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Black/White ', N'Black/White ', N'Black/White', N' Black/White', N'Black/White', N' Black / White', N'Assorted Colours', N' ', N'', N'', 1, 17)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' BlanchedAlmond ', N'BlanchedAlmond ', N'BlanchedAlmond', N' BlanchedAlmond', N'BlanchedAlmond', N' Blanched Almond', N'Assorted Colours', N' ', N'', N'', 1, 18)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Blossom ', N'Blossom ', N'Blossom', N' Blossom', N'Blossom', N' Blossom', N'Blossom', N' ', N'', N'', 1, 19)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Blue ', N'Blue ', N'Blue', N' Blue', N'Blue', N' Blue', N'Blue', N' ', N'', N'', 1, 20)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Bluebell ', N'Bluebell ', N'Bluebell', N' Bluebell', N'Bluebell', N' Bluebell', N'Blue', N' ', N'', N'', 1, 21)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' BlueViolet ', N'BlueViolet ', N'BlueViolet', N' BlueViolet', N'BlueViolet', N' Blue Violet', N'Blue', N' ', N'', N'', 1, 22)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Blush ', N'Blush ', N'Blush', N' Blush', N'Blush', N' Blush', N'Blush', N' ', N'', N'', 1, 23)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Botanical ', N'Botanical ', N'Botanical', N' Botanical', N'Botanical', N' Botanical', N'Botanical', N' ', N'', N'', 1, 24)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Brown ', N'Brown ', N'Brown', N' Brown', N'Brown', N' Brown', N'Brown', N' ', N'', N'', 1, 25)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' BurlyWood ', N'BurlyWood ', N'BurlyWood', N' BurlyWood', N'BurlyWood', N' Burly Wood', N'Assorted Colours', N' ', N'', N'', 1, 26)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' CadetBlue ', N'CadetBlue ', N'CadetBlue', N' CadetBlue', N'CadetBlue', N' Cadet Blue', N'Blue', N' ', N'', N'', 1, 27)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Cappuccino ', N'Cappuccino ', N'Cappuccino', N' Cappuccino', N'Cappuccino', N' Cappuccino', N'Cappuccino', N' ', N'', N'', 1, 28)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Caramel ', N'Caramel ', N'Caramel', N' Caramel', N'Caramel', N' Caramel', N'Caramel', N' ', N'', N'', 1, 29)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Champagne ', N'Champagne ', N'Champagne', N' Champagne', N'Champagne', N' Champagne', N'Champagne', N' ', N'', N'', 1, 30)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Charcoal ', N'Charcoal ', N'Charcoal', N' Charcoal', N'Charcoal', N' Charcoal', N'Black', N' ', N'', N'', 1, 31)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Chartreuse ', N'Chartreuse ', N'Chartreuse', N' Chartreuse', N'Chartreuse', N' Chartreuse', N'Chartreuse', N' ', N'', N'', 1, 32)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Cherry Red ', N'Cherry Red ', N'Cherry Red', N' Cherry Red', N'Cherry Red', N' Cherry Red', N'Red', N' ', N'', N'', 1, 33)

  GO

  INSERT [dbo].[Dim_Colour] ([LookupValue], [LookupFirstValue], [LookupFirstValueNoSpace], [LookupLastValue], [LookupLastValueNoSpace], [DisplayInDetails], [DisplayInFilter], [ReplaceValue], [ReplaceFirstValue], [ReplaceLastValue], [TypeID], [DataID]) VALUES (N' Chocolate ', N'Chocolate ', N'Chocolate', N' Chocolate', N'Chocolate', N' Chocolate', N'Chocolate', N' ', N'', N'', 1, 34)

  GO

  SET IDENTITY_INSERT [dbo].[Dim_Colour] OFF

  GO

  Create the function

  CREATE FUNCTION [dbo].[UTfn_LikeColourTableFilter]

  (

  @Value VARCHAR(8000)

  )

  RETURNS TABLE AS

  RETURN

  WITH ColourList AS ( -- replace this section with your Colour table when the time comes.

  SELECT SCT.LookupValue,SCT.DisplayInFilter FROM dbo.Dim_Colour SCT

  ),

  LookupList

  AS

  (

  SELECT

  ROW_NUMBER() OVER (ORDER BY LEN(cl.LookupValue) DESC) AS rn,-- prevents "Yellow" from butchering up "LightGoldenRodYellow"..

  cl.LookupValue,cl.DisplayInFilter

  FROM

  dbo.Dim_Colour cl

  WHERE

  @Value LIKE '%' + cl.LookupValue + '%'

  ), RecursiveReplace AS (

  -- -- My select 1

  SELECT cis.DisplayInFilter AS ReturnString,

  cis.rn

  FROM

  LookupList cis

  WHERE

  cis.rn = 1

  UNION ALL

  -- -- My select 2

  SELECT cis.DisplayInFilter AS ReturnString,

  cis.rn

  FROM

  LookupList cis

  JOIN RecursiveReplace rr

  ON cis.rn = rr.rn + 1

  )

  SELECT TOP 1

  rr.ReturnString

  FROM

  RecursiveReplace rr

  ORDER BY

  rr.rn DESC;

  GO

  This is the chopped down version to see the results

  SELECT top 1

  SourceColuumnName = 'colour'

  ,SFA.colour

  ,SFA.model_number

  ,SFA.commission_group

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,ResultFromFunction = CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  FROM dbo.Staging_Feed_AffW SFA

  WHERE SFA.colour like '% Black %'

  UNION

  SELECT top 1

  SourceColuumnName = 'model_number'

  ,SFA.colour

  ,SFA.model_number

  ,SFA.commission_group

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,ResultFromFunction = CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  FROM dbo.Staging_Feed_AffW SFA

  WHERE SFA.model_number like '% Black %'

  UNION

  SELECT top 1

  SourceColuumnName = 'commission_group'

  ,SFA.colour

  ,SFA.model_number

  ,SFA.commission_group

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,ResultFromFunction = CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  FROM dbo.Staging_Feed_AffW SFA

  WHERE SFA.commission_group like '% Black %'

  UNION

  SELECT top 1

  SourceColuumnName = 'custom_1'

  ,SFA.colour

  ,SFA.model_number

  ,SFA.commission_group

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,ResultFromFunction = CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  FROM dbo.Staging_Feed_AffW SFA

  WHERE SFA.custom_1 like '% Black %'

  UNION

  SELECT top 1

  SourceColuumnName = 'custom_2'

  ,SFA.colour

  ,SFA.model_number

  ,SFA.commission_group

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,ResultFromFunction = CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  FROM dbo.Staging_Feed_AffW SFA

  WHERE SFA.custom_2 like '% Black %'

  UNION

  SELECT top 1

  SourceColuumnName = 'custom_3'

  ,SFA.colour

  ,SFA.model_number

  ,SFA.commission_group

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,ResultFromFunction = CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  FROM dbo.Staging_Feed_AffW SFA

  WHERE SFA.custom_3 like '% Black %'

  UNION

  SELECT top 1

  SourceColuumnName = 'product_name'

  ,SFA.colour

  ,SFA.model_number

  ,SFA.commission_group

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,ResultFromFunction = CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  FROM dbo.Staging_Feed_AffW SFA

  WHERE SFA.product_name like '% Black %'

  This is the whole query but I have given just the section in question above

  ALTER PROCEDURE [dbo].[USP_STEP_D_1_3_0_Run_USP_AutoBuildDataTable_Fact_Feed_Products]

  AS

  --------UPDATE dbo.Dim_MasterImportLookups

  --------SET UsedDate = NULL

  --------,FlagAsUsed = NULL

  WHILE EXISTS (

  SELECT TOP 1

  A.DataID

  from dbo.Dim_MasterImportLookups A

  WHERE A.FlagAsUsed IS NULL

  )

  BEGIN

  DECLARE

  @WhereProdNameStartWith NVARCHAR(500),

  @WhereProdNameEndsWith NVARCHAR(500),

  @WhereProdNameStartLike NVARCHAR(500),

  @WhereProdNameEndsLike NVARCHAR(500),

  @WhereProdNameLike1 NVARCHAR(500),

  @WhereProdNameLike2 NVARCHAR(500),

  @WhereProdNameLike3 NVARCHAR(500),

  @WhereProdNameLike4 NVARCHAR(500),

  @WhereDescriptionStartWith NVARCHAR(500),

  @WhereDescriptionEndsWith NVARCHAR(500),

  @WhereDescriptionStartLike NVARCHAR(500),

  @WhereDescriptionEndsLike NVARCHAR(500),

  @WhereDescriptionLike1 NVARCHAR(500),

  @WhereDescriptionLike2 NVARCHAR(500),

  @WhereDescriptionLike3 NVARCHAR(500),

  @WhereDescriptionLike4 NVARCHAR(500),

  @MerchantCategory NVARCHAR(500),

  @WebSiteID NVARCHAR(500),

  @CategoryMainID NVARCHAR(500),

  @CategorySubID NVARCHAR(500),

  @CategorySubSubID NVARCHAR(500),

  @WebSiteCategoryName NVARCHAR(500),

  @WebSiteSubCategoryName NVARCHAR(500),

  @GsScriptCategoryName NVARCHAR(500),

  @BoScriptCategoryName NVARCHAR(500),

  @InsertSortOrder1 INT,

  @InsertSortOrder2 INT,

  @DisplayTypeWebDescCompSecID INT,

  @DataID INT,

  @DateNow DATE

  SELECT TOP 1

  @WhereProdNameStartWith = W1.WhereProdNameStartWith,

  @WhereProdNameEndsWith = W1.WhereProdNameEndsWith,

  @WhereProdNameStartLike = W1.WhereProdNameStartLike,

  @WhereProdNameEndsLike = W1.WhereProdNameEndsLike,

  @WhereProdNameLike1 = W1.WhereProdNameLike1,

  @WhereProdNameLike2 = W1.WhereProdNameLike2,

  @WhereProdNameLike3 = W1.WhereProdNameLike3,

  @WhereProdNameLike4 = W1.WhereProdNameLike4,

  @WhereDescriptionStartWith = W1.WhereDescriptionStartWith,

  @WhereDescriptionEndsWith = W1.WhereDescriptionEndsWith,

  @WhereDescriptionStartLike = W1.WhereDescriptionStartLike,

  @WhereDescriptionEndsLike = W1.WhereDescriptionEndsLike,

  @WhereDescriptionLike1 = W1.WhereDescriptionLike1,

  @WhereDescriptionLike2 = W1.WhereDescriptionLike2,

  @WhereDescriptionLike3 = W1.WhereDescriptionLike3,

  @WhereDescriptionLike4 = W1.WhereDescriptionLike4,

  @MerchantCategory = W1.MerchantCategory,

  @WebSiteID = W1.WebSiteID,

  @CategoryMainID = W1.CategoryMainID,

  @CategorySubID = W1.CategorySubID,

  @CategorySubSubID = W1.CategorySubSubID,

  @WebSiteCategoryName = W1.WebSiteCategoryName,

  @WebSiteSubCategoryName = W1.WebSiteSubCategoryName,

  @GsScriptCategoryName = W1.GsScriptCategoryName,

  @BoScriptCategoryName = W1.BoScriptCategoryName,

  @InsertSortOrder1 = W1.InsertSortOrder1,

  @InsertSortOrder2 = W1.InsertSortOrder2,

  @DisplayTypeWebDescCompSecID = W1.DisplayTypeWebDescCompSecID,

  @DataID = W1.DataID,

  @DateNow = CONVERT(DATE,GETDATE())

  FROM dbo.Dim_MasterImportLookups W1

  WHERE FlagAsUsed IS NULL

  ORDER BY W1.DataID

  SELECT

  ProdNameStartWith=@WhereProdNameStartWith

  ,ProdNameEndsWith=@WhereProdNameEndsWith

  ,ProdNameStartLike=@WhereProdNameStartLike

  ,ProdNameEndsLike=@WhereProdNameEndsLike

  ,ProdNameLike1=@WhereProdNameLike1

  ,ProdNameLike2=@WhereProdNameLike2

  ,ProdNameLike1=@WhereProdNameLike3

  ,ProdNameLike1=@WhereProdNameLike4

  ,DescriptionStartWith=@WhereDescriptionStartWith

  ,DescriptionEndsWith=@WhereDescriptionEndsWith

  ,DescriptionStartLike=@WhereDescriptionStartLike

  ,DescriptionEndsLike=@WhereDescriptionEndsLike

  ,DescriptionLike1=@WhereDescriptionLike1

  ,DescriptionLike2=@WhereDescriptionLike2

  ,DescriptionLike3=@WhereDescriptionLike3

  ,DescriptionLike4=@WhereDescriptionLike4

  IF EXISTS(

  SELECT @InsertSortOrder2

  WHERE @InsertSortOrder2 = 1

  )

  INSERT INTO dbo.Fact_Feed_Products

  SELECT

  Dim_AffiliateProviderID=1

  ,Dim_AffiliateProgramID=1

  ,Dim_WebSiteID= @WebSiteID

  ,Dim_CategoryMainID= @CategoryMainID -- -- MIL.CategoryMainID

  ,Dim_CategorySub= @CategorySubID -- -- MIL.CategorySubID

  ,Dim_CategorySubSub= @CategorySubSubID -- -- MIL.CategorySubSubID

  ,Dim_GsScriptCategoryName= @GsScriptCategoryName -- -- MIL.GsScriptCategoryName

  ,Dim_BoScriptCategoryName= @BoScriptCategoryName -- -- MIL.BoScriptCategoryName

  ,Dim_WebSiteCategoryName= @WebSiteCategoryName -- -- MIL.WebSiteCategoryName

  ,Dim_WebSiteCategorySubName= @WebSiteSubCategoryName -- -- MIL.WebSiteSubCategoryName

  ,Dim_DisplayTypeWebDescCompSecID=@DisplayTypeWebDescCompSecID

  ,Dim_MasterImportLookupsID=@DataID

  ,Dim_DateCreated=@DateNow

  ,DIM_PostedToGs=0

  ,Dim_PostedToGsDate=''

  ,SFA.aw_product_id

  ,SFA.merchant_product_id

  ,SFA.merchant_category

  ,SFA.aw_deep_link

  ,SFA.merchant_image_url

  ,SFA.search_price

  ,[description]=REPLACE(SFA.[description],'Â','')

  ,SFA.product_name

  ,SFA.merchant_deep_link

  ,SFA.aw_image_url

  ,SFA.merchant_name

  ,SFA.merchant_id

  ,SFA.category_name

  ,SFA.category_id

  ,SFA.delivery_cost

  ,SFA.currency

  ,SFA.store_price

  ,SFA.display_price

  ,SFA.data_feed_id

  ,SFA.rrp_price

  ,SFA.specifications

  ,SFA.condition

  ,SFA.promotional_text

  ,SFA.warranty

  ,SFA.merchant_thumb_url

  ,SFA.aw_thumb_url

  ,SFA.brand_name

  ,SFA.brand_id

  ,SFA.delivery_time

  ,SFA.valid_from

  ,SFA.valid_to

  ,SFA.web_offer

  ,SFA.pre_order

  ,SFA.in_stock

  ,SFA.stock_quantity

  ,SFA.is_for_sale

  ,SFA.product_type

  ,SFA.commission_group

  ,SFA.upc

  ,SFA.ean

  ,SFA.mpn

  ,SFA.isbn

  ,SFA.model_number

  ,SFA.parent_product_id

  ,SFA.[language]

  ,SFA.last_updated

  ,SFA.dimensions

  ,SFA.colour

  ,SFA.keywords

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,SFA.custom_4

  ,SFA.custom_5

  ,SFA.saving

  ,SFA.delivery_weight

  ,SFA.delivery_restrictions

  ,SFA.reviews

  ,SFA.average_rating

  ,SFA.number_stars

  ,SFA.number_available

  ,SFA.rating

  ,SFA.alternate_image

  ,SFA.large_image

  ,SFA.basket_link

  ,FilterProductType=CASEWHENDCM.CategoryMainName IS NULLTHENdbo.UfnProductType(REPLACE(SFA.commission_group,' ' ,''))

  ELSEDCM.CategoryMainNameEND

  ,FilterBackSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterCupSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.UfnCupSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterFullSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_1,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_2,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_3,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_4,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_5,' ' ,''),'Mixed Sizes')

  ELSEISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes') END

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  ,FilterBrand=CASEWHENSFA.brand_name IS NOT NULLTHENSFA.brand_nameELSE'No Brand'END

  ,FilterSeller=CASEWHENSFA.merchant_name IS NOT NULLTHENSFA.merchant_nameELSE'No Store Name'END

  ,FilterProductTypeLevel1=''

  ,FilterProductTypeLevel2=''

  ,FilterProductTypeLevel3=''

  ,FilterProductTypeLevel4=''

  ,FilterProductTypeLevel5=''

  ,FilterProductTypeLevel6=''

  ,FilterProductTypeLevel7=''

  ,FilterProductTypeLevel8=''

  ,FilterProductTypeLevel9=''

  ,FilterProductTypeLevel10=''

  ,MerchantActiveInactive=ISNULL(S.MerchantActiveInactive,1)

  ,ClosedDownMerchantURL=''

  ,EtlDateInserted=@DateNow

  ,EtlDateUpdated=@DateNow

  ,EtlDescriptionLastWord=@WhereDescriptionEndsWith

  ,EtlProductNameLastWord=@WhereProdNameEndsWith

  ,EtlProcessedFlag=NULL

  ,EtlMasterLookupFilterID=@InsertSortOrder1

  ,EtlMasterLookupFilterSubID=@InsertSortOrder2

  ,EtlDescriptionFirstWord=@WhereDescriptionStartWith

  ,EtlProductNameFirstWord=@WhereProdNameStartWith

  FROM dbo.Staging_Feed_AffW SFA

  LEFT OUTER JOIN dbo.Dim_CategoryMain DCM

  ON @CategoryMainID = DCM.CategoryMainFeedID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),SFA.merchant_id) = S.SellerID

  WHERE

  SFA.EtlInsertedRows = 'NotSet'

  AND @InsertSortOrder2 = 1

  AND CONTAINS((SFA.EtlProductNameFirstWord), @WhereProdNameStartWith)

  AND CONTAINS((SFA.EtlProductNameLastWord), @WhereProdNameEndsWith)

  AND SFA.product_name LIKE '%' + @WhereProdNameLike1 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike2 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike3 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike4 + '%'

  ELSE

  IF EXISTS(

  SELECT @InsertSortOrder2

  WHERE @InsertSortOrder2 = 2

  )

  INSERT INTO dbo.Fact_Feed_Products

  SELECT

  Dim_AffiliateProviderID=1

  ,Dim_AffiliateProgramID=1

  ,Dim_WebSiteID= @WebSiteID

  ,Dim_CategoryMainID= @CategoryMainID -- -- MIL.CategoryMainID

  ,Dim_CategorySub= @CategorySubID -- -- MIL.CategorySubID

  ,Dim_CategorySubSub= @CategorySubSubID -- -- MIL.CategorySubSubID

  ,Dim_GsScriptCategoryName= @GsScriptCategoryName -- -- MIL.GsScriptCategoryName

  ,Dim_BoScriptCategoryName= @BoScriptCategoryName -- -- MIL.BoScriptCategoryName

  ,Dim_WebSiteCategoryName= @WebSiteCategoryName -- -- MIL.WebSiteCategoryName

  ,Dim_WebSiteCategorySubName= @WebSiteSubCategoryName -- -- MIL.WebSiteSubCategoryName

  ,Dim_DisplayTypeWebDescCompSecID=@DisplayTypeWebDescCompSecID

  ,Dim_MasterImportLookupsID=@DataID

  ,Dim_DateCreated=@DateNow

  ,DIM_PostedToGs=0

  ,Dim_PostedToGsDate=''

  ,SFA.aw_product_id

  ,SFA.merchant_product_id

  ,SFA.merchant_category

  ,SFA.aw_deep_link

  ,SFA.merchant_image_url

  ,SFA.search_price

  ,[description]=REPLACE(SFA.[description],'Â','')

  ,SFA.product_name

  ,SFA.merchant_deep_link

  ,SFA.aw_image_url

  ,SFA.merchant_name

  ,SFA.merchant_id

  ,SFA.category_name

  ,SFA.category_id

  ,SFA.delivery_cost

  ,SFA.currency

  ,SFA.store_price

  ,SFA.display_price

  ,SFA.data_feed_id

  ,SFA.rrp_price

  ,SFA.specifications

  ,SFA.condition

  ,SFA.promotional_text

  ,SFA.warranty

  ,SFA.merchant_thumb_url

  ,SFA.aw_thumb_url

  ,SFA.brand_name

  ,SFA.brand_id

  ,SFA.delivery_time

  ,SFA.valid_from

  ,SFA.valid_to

  ,SFA.web_offer

  ,SFA.pre_order

  ,SFA.in_stock

  ,SFA.stock_quantity

  ,SFA.is_for_sale

  ,SFA.product_type

  ,SFA.commission_group

  ,SFA.upc

  ,SFA.ean

  ,SFA.mpn

  ,SFA.isbn

  ,SFA.model_number

  ,SFA.parent_product_id

  ,SFA.[language]

  ,SFA.last_updated

  ,SFA.dimensions

  ,SFA.colour

  ,SFA.keywords

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,SFA.custom_4

  ,SFA.custom_5

  ,SFA.saving

  ,SFA.delivery_weight

  ,SFA.delivery_restrictions

  ,SFA.reviews

  ,SFA.average_rating

  ,SFA.number_stars

  ,SFA.number_available

  ,SFA.rating

  ,SFA.alternate_image

  ,SFA.large_image

  ,SFA.basket_link

  ,FilterProductType=CASEWHENDCM.CategoryMainName IS NULLTHENdbo.UfnProductType(REPLACE(SFA.commission_group,' ' ,''))

  ELSEDCM.CategoryMainNameEND

  ,FilterBackSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterCupSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.UfnCupSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterFullSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_1,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_2,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_3,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_4,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_5,' ' ,''),'Mixed Sizes')

  ELSEISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes') END

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  ,FilterBrand=CASEWHENSFA.brand_name IS NOT NULLTHENSFA.brand_nameELSE'No Brand'END

  ,FilterSeller=CASEWHENSFA.merchant_name IS NOT NULLTHENSFA.merchant_nameELSE'No Store Name'END

  ,FilterProductTypeLevel1=''

  ,FilterProductTypeLevel2=''

  ,FilterProductTypeLevel3=''

  ,FilterProductTypeLevel4=''

  ,FilterProductTypeLevel5=''

  ,FilterProductTypeLevel6=''

  ,FilterProductTypeLevel7=''

  ,FilterProductTypeLevel8=''

  ,FilterProductTypeLevel9=''

  ,FilterProductTypeLevel10=''

  ,MerchantActiveInactive=ISNULL(S.MerchantActiveInactive,1)

  ,ClosedDownMerchantURL=''

  ,EtlDateInserted=@DateNow

  ,EtlDateUpdated=@DateNow

  ,EtlDescriptionLastWord=@WhereDescriptionEndsWith

  ,EtlProductNameLastWord=@WhereProdNameEndsWith

  ,EtlProcessedFlag=NULL

  ,EtlMasterLookupFilterID=@InsertSortOrder1

  ,EtlMasterLookupFilterSubID=@InsertSortOrder2

  ,EtlDescriptionFirstWord=@WhereDescriptionStartWith

  ,EtlProductNameFirstWord=@WhereProdNameStartWith

  FROM dbo.Staging_Feed_AffW SFA

  LEFT OUTER JOIN dbo.Dim_CategoryMain DCM

  ON @CategoryMainID = DCM.CategoryMainFeedID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),SFA.merchant_id) = S.SellerID

  WHERE

  SFA.EtlInsertedRows = 'NotSet'

  AND @InsertSortOrder2 = 2

  AND CONTAINS((SFA.EtlProductNameFirstWord), @WhereProdNameStartWith)

  AND CONTAINS((SFA.EtlProductNameLastWord), @WhereProdNameEndsWith)

  AND SFA.product_name LIKE '%' + @WhereProdNameLike1 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike2 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike3 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike4 + '%'

  ELSE

  IF EXISTS(

  SELECT @InsertSortOrder2

  WHERE @InsertSortOrder2 = 3

  )

  INSERT INTO dbo.Fact_Feed_Products

  SELECT

  Dim_AffiliateProviderID=1

  ,Dim_AffiliateProgramID=1

  ,Dim_WebSiteID= @WebSiteID

  ,Dim_CategoryMainID= @CategoryMainID -- -- MIL.CategoryMainID

  ,Dim_CategorySub= @CategorySubID -- -- MIL.CategorySubID

  ,Dim_CategorySubSub= @CategorySubSubID -- -- MIL.CategorySubSubID

  ,Dim_GsScriptCategoryName= @GsScriptCategoryName -- -- MIL.GsScriptCategoryName

  ,Dim_BoScriptCategoryName= @BoScriptCategoryName -- -- MIL.BoScriptCategoryName

  ,Dim_WebSiteCategoryName= @WebSiteCategoryName -- -- MIL.WebSiteCategoryName

  ,Dim_WebSiteCategorySubName= @WebSiteSubCategoryName -- -- MIL.WebSiteSubCategoryName

  ,Dim_DisplayTypeWebDescCompSecID=@DisplayTypeWebDescCompSecID

  ,Dim_MasterImportLookupsID=@DataID

  ,Dim_DateCreated=@DateNow

  ,DIM_PostedToGs=0

  ,Dim_PostedToGsDate=''

  ,SFA.aw_product_id

  ,SFA.merchant_product_id

  ,SFA.merchant_category

  ,SFA.aw_deep_link

  ,SFA.merchant_image_url

  ,SFA.search_price

  ,[description]=REPLACE(SFA.[description],'Â','')

  ,SFA.product_name

  ,SFA.merchant_deep_link

  ,SFA.aw_image_url

  ,SFA.merchant_name

  ,SFA.merchant_id

  ,SFA.category_name

  ,SFA.category_id

  ,SFA.delivery_cost

  ,SFA.currency

  ,SFA.store_price

  ,SFA.display_price

  ,SFA.data_feed_id

  ,SFA.rrp_price

  ,SFA.specifications

  ,SFA.condition

  ,SFA.promotional_text

  ,SFA.warranty

  ,SFA.merchant_thumb_url

  ,SFA.aw_thumb_url

  ,SFA.brand_name

  ,SFA.brand_id

  ,SFA.delivery_time

  ,SFA.valid_from

  ,SFA.valid_to

  ,SFA.web_offer

  ,SFA.pre_order

  ,SFA.in_stock

  ,SFA.stock_quantity

  ,SFA.is_for_sale

  ,SFA.product_type

  ,SFA.commission_group

  ,SFA.upc

  ,SFA.ean

  ,SFA.mpn

  ,SFA.isbn

  ,SFA.model_number

  ,SFA.parent_product_id

  ,SFA.[language]

  ,SFA.last_updated

  ,SFA.dimensions

  ,SFA.colour

  ,SFA.keywords

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,SFA.custom_4

  ,SFA.custom_5

  ,SFA.saving

  ,SFA.delivery_weight

  ,SFA.delivery_restrictions

  ,SFA.reviews

  ,SFA.average_rating

  ,SFA.number_stars

  ,SFA.number_available

  ,SFA.rating

  ,SFA.alternate_image

  ,SFA.large_image

  ,SFA.basket_link

  ,FilterProductType=CASEWHENDCM.CategoryMainName IS NULLTHENdbo.UfnProductType(REPLACE(SFA.commission_group,' ' ,''))

  ELSEDCM.CategoryMainNameEND

  ,FilterBackSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterCupSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.UfnCupSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterFullSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_1,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_2,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_3,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_4,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_5,' ' ,''),'Mixed Sizes')

  ELSEISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes') END

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  ,FilterBrand=CASEWHENSFA.brand_name IS NOT NULLTHENSFA.brand_nameELSE'No Brand'END

  ,FilterSeller=CASEWHENSFA.merchant_name IS NOT NULLTHENSFA.merchant_nameELSE'No Store Name'END

  ,FilterProductTypeLevel1=''

  ,FilterProductTypeLevel2=''

  ,FilterProductTypeLevel3=''

  ,FilterProductTypeLevel4=''

  ,FilterProductTypeLevel5=''

  ,FilterProductTypeLevel6=''

  ,FilterProductTypeLevel7=''

  ,FilterProductTypeLevel8=''

  ,FilterProductTypeLevel9=''

  ,FilterProductTypeLevel10=''

  ,MerchantActiveInactive=ISNULL(S.MerchantActiveInactive,1)

  ,ClosedDownMerchantURL=''

  ,EtlDateInserted=@DateNow

  ,EtlDateUpdated=@DateNow

  ,EtlDescriptionLastWord=@WhereDescriptionEndsWith

  ,EtlProductNameLastWord=@WhereProdNameEndsWith

  ,EtlProcessedFlag=NULL

  ,EtlMasterLookupFilterID=@InsertSortOrder1

  ,EtlMasterLookupFilterSubID=@InsertSortOrder2

  ,EtlDescriptionFirstWord=@WhereDescriptionStartWith

  ,EtlProductNameFirstWord=@WhereProdNameStartWith

  FROM dbo.Staging_Feed_AffW SFA

  LEFT OUTER JOIN dbo.Dim_CategoryMain DCM

  ON @CategoryMainID = DCM.CategoryMainFeedID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),SFA.merchant_id) = S.SellerID

  WHERE

  SFA.EtlInsertedRows = 'NotSet'

  AND @InsertSortOrder2 = 3

  --AND CONTAINS((SFA.EtlProductNameFirstWord), @WhereProdNameStartWith)

  AND CONTAINS((SFA.EtlProductNameLastWord), @WhereProdNameEndsWith)

  AND SFA.product_name LIKE '%' + @WhereProdNameStartLike + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike1 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike2 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike3 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike4 + '%'

  ELSE

  IF EXISTS(

  SELECT @InsertSortOrder2

  WHERE @InsertSortOrder2 = 4

  )

  INSERT INTO dbo.Fact_Feed_Products

  SELECT

  Dim_AffiliateProviderID=1

  ,Dim_AffiliateProgramID=1

  ,Dim_WebSiteID= @WebSiteID

  ,Dim_CategoryMainID= @CategoryMainID -- -- MIL.CategoryMainID

  ,Dim_CategorySub= @CategorySubID -- -- MIL.CategorySubID

  ,Dim_CategorySubSub= @CategorySubSubID -- -- MIL.CategorySubSubID

  ,Dim_GsScriptCategoryName= @GsScriptCategoryName -- -- MIL.GsScriptCategoryName

  ,Dim_BoScriptCategoryName= @BoScriptCategoryName -- -- MIL.BoScriptCategoryName

  ,Dim_WebSiteCategoryName= @WebSiteCategoryName -- -- MIL.WebSiteCategoryName

  ,Dim_WebSiteCategorySubName= @WebSiteSubCategoryName -- -- MIL.WebSiteSubCategoryName

  ,Dim_DisplayTypeWebDescCompSecID=@DisplayTypeWebDescCompSecID

  ,Dim_MasterImportLookupsID=@DataID

  ,Dim_DateCreated=@DateNow

  ,DIM_PostedToGs=0

  ,Dim_PostedToGsDate=''

  ,SFA.aw_product_id

  ,SFA.merchant_product_id

  ,SFA.merchant_category

  ,SFA.aw_deep_link

  ,SFA.merchant_image_url

  ,SFA.search_price

  ,[description]=REPLACE(SFA.[description],'Â','')

  ,SFA.product_name

  ,SFA.merchant_deep_link

  ,SFA.aw_image_url

  ,SFA.merchant_name

  ,SFA.merchant_id

  ,SFA.category_name

  ,SFA.category_id

  ,SFA.delivery_cost

  ,SFA.currency

  ,SFA.store_price

  ,SFA.display_price

  ,SFA.data_feed_id

  ,SFA.rrp_price

  ,SFA.specifications

  ,SFA.condition

  ,SFA.promotional_text

  ,SFA.warranty

  ,SFA.merchant_thumb_url

  ,SFA.aw_thumb_url

  ,SFA.brand_name

  ,SFA.brand_id

  ,SFA.delivery_time

  ,SFA.valid_from

  ,SFA.valid_to

  ,SFA.web_offer

  ,SFA.pre_order

  ,SFA.in_stock

  ,SFA.stock_quantity

  ,SFA.is_for_sale

  ,SFA.product_type

  ,SFA.commission_group

  ,SFA.upc

  ,SFA.ean

  ,SFA.mpn

  ,SFA.isbn

  ,SFA.model_number

  ,SFA.parent_product_id

  ,SFA.[language]

  ,SFA.last_updated

  ,SFA.dimensions

  ,SFA.colour

  ,SFA.keywords

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,SFA.custom_4

  ,SFA.custom_5

  ,SFA.saving

  ,SFA.delivery_weight

  ,SFA.delivery_restrictions

  ,SFA.reviews

  ,SFA.average_rating

  ,SFA.number_stars

  ,SFA.number_available

  ,SFA.rating

  ,SFA.alternate_image

  ,SFA.large_image

  ,SFA.basket_link

  ,FilterProductType=CASEWHENDCM.CategoryMainName IS NULLTHENdbo.UfnProductType(REPLACE(SFA.commission_group,' ' ,''))

  ELSEDCM.CategoryMainNameEND

  ,FilterBackSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterCupSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.UfnCupSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterFullSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_1,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_2,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_3,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_4,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_5,' ' ,''),'Mixed Sizes')

  ELSEISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes') END

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  ,FilterBrand=CASEWHENSFA.brand_name IS NOT NULLTHENSFA.brand_nameELSE'No Brand'END

  ,FilterSeller=CASEWHENSFA.merchant_name IS NOT NULLTHENSFA.merchant_nameELSE'No Store Name'END

  ,FilterProductTypeLevel1=''

  ,FilterProductTypeLevel2=''

  ,FilterProductTypeLevel3=''

  ,FilterProductTypeLevel4=''

  ,FilterProductTypeLevel5=''

  ,FilterProductTypeLevel6=''

  ,FilterProductTypeLevel7=''

  ,FilterProductTypeLevel8=''

  ,FilterProductTypeLevel9=''

  ,FilterProductTypeLevel10=''

  ,MerchantActiveInactive=ISNULL(S.MerchantActiveInactive,1)

  ,ClosedDownMerchantURL=''

  ,EtlDateInserted=@DateNow

  ,EtlDateUpdated=@DateNow

  ,EtlDescriptionLastWord=@WhereDescriptionEndsWith

  ,EtlProductNameLastWord=@WhereProdNameEndsWith

  ,EtlProcessedFlag=NULL

  ,EtlMasterLookupFilterID=@InsertSortOrder1

  ,EtlMasterLookupFilterSubID=@InsertSortOrder2

  ,EtlDescriptionFirstWord=@WhereDescriptionStartWith

  ,EtlProductNameFirstWord=@WhereProdNameStartWith

  FROM dbo.Staging_Feed_AffW SFA

  LEFT OUTER JOIN dbo.Dim_CategoryMain DCM

  ON @CategoryMainID = DCM.CategoryMainFeedID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),SFA.merchant_id) = S.SellerID

  WHERE

  SFA.EtlInsertedRows = 'NotSet'

  AND @InsertSortOrder2 = 4

  --AND CONTAINS((SFA.EtlProductNameFirstWord), @WhereProdNameStartWith)

  AND CONTAINS((SFA.EtlProductNameLastWord), @WhereProdNameEndsWith)

  AND SFA.product_name LIKE '%' + @WhereProdNameStartLike + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike1 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike2 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike3 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike4 + '%'

  ELSE

  IF EXISTS(

  SELECT @InsertSortOrder2

  WHERE @InsertSortOrder2 = 5

  )

  INSERT INTO dbo.Fact_Feed_Products

  SELECT

  Dim_AffiliateProviderID=1

  ,Dim_AffiliateProgramID=1

  ,Dim_WebSiteID= @WebSiteID

  ,Dim_CategoryMainID= @CategoryMainID -- -- MIL.CategoryMainID

  ,Dim_CategorySub= @CategorySubID -- -- MIL.CategorySubID

  ,Dim_CategorySubSub= @CategorySubSubID -- -- MIL.CategorySubSubID

  ,Dim_GsScriptCategoryName= @GsScriptCategoryName -- -- MIL.GsScriptCategoryName

  ,Dim_BoScriptCategoryName= @BoScriptCategoryName -- -- MIL.BoScriptCategoryName

  ,Dim_WebSiteCategoryName= @WebSiteCategoryName -- -- MIL.WebSiteCategoryName

  ,Dim_WebSiteCategorySubName= @WebSiteSubCategoryName -- -- MIL.WebSiteSubCategoryName

  ,Dim_DisplayTypeWebDescCompSecID=@DisplayTypeWebDescCompSecID

  ,Dim_MasterImportLookupsID=@DataID

  ,Dim_DateCreated=@DateNow

  ,DIM_PostedToGs=0

  ,Dim_PostedToGsDate=''

  ,SFA.aw_product_id

  ,SFA.merchant_product_id

  ,SFA.merchant_category

  ,SFA.aw_deep_link

  ,SFA.merchant_image_url

  ,SFA.search_price

  ,[description]=REPLACE(SFA.[description],'Â','')

  ,SFA.product_name

  ,SFA.merchant_deep_link

  ,SFA.aw_image_url

  ,SFA.merchant_name

  ,SFA.merchant_id

  ,SFA.category_name

  ,SFA.category_id

  ,SFA.delivery_cost

  ,SFA.currency

  ,SFA.store_price

  ,SFA.display_price

  ,SFA.data_feed_id

  ,SFA.rrp_price

  ,SFA.specifications

  ,SFA.condition

  ,SFA.promotional_text

  ,SFA.warranty

  ,SFA.merchant_thumb_url

  ,SFA.aw_thumb_url

  ,SFA.brand_name

  ,SFA.brand_id

  ,SFA.delivery_time

  ,SFA.valid_from

  ,SFA.valid_to

  ,SFA.web_offer

  ,SFA.pre_order

  ,SFA.in_stock

  ,SFA.stock_quantity

  ,SFA.is_for_sale

  ,SFA.product_type

  ,SFA.commission_group

  ,SFA.upc

  ,SFA.ean

  ,SFA.mpn

  ,SFA.isbn

  ,SFA.model_number

  ,SFA.parent_product_id

  ,SFA.[language]

  ,SFA.last_updated

  ,SFA.dimensions

  ,SFA.colour

  ,SFA.keywords

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,SFA.custom_4

  ,SFA.custom_5

  ,SFA.saving

  ,SFA.delivery_weight

  ,SFA.delivery_restrictions

  ,SFA.reviews

  ,SFA.average_rating

  ,SFA.number_stars

  ,SFA.number_available

  ,SFA.rating

  ,SFA.alternate_image

  ,SFA.large_image

  ,SFA.basket_link

  ,FilterProductType=CASEWHENDCM.CategoryMainName IS NULLTHENdbo.UfnProductType(REPLACE(SFA.commission_group,' ' ,''))

  ELSEDCM.CategoryMainNameEND

  ,FilterBackSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterCupSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.UfnCupSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterFullSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_1,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_2,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_3,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_4,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_5,' ' ,''),'Mixed Sizes')

  ELSEISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes') END

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  ,FilterBrand=CASEWHENSFA.brand_name IS NOT NULLTHENSFA.brand_nameELSE'No Brand'END

  ,FilterSeller=CASEWHENSFA.merchant_name IS NOT NULLTHENSFA.merchant_nameELSE'No Store Name'END

  ,FilterProductTypeLevel1=''

  ,FilterProductTypeLevel2=''

  ,FilterProductTypeLevel3=''

  ,FilterProductTypeLevel4=''

  ,FilterProductTypeLevel5=''

  ,FilterProductTypeLevel6=''

  ,FilterProductTypeLevel7=''

  ,FilterProductTypeLevel8=''

  ,FilterProductTypeLevel9=''

  ,FilterProductTypeLevel10=''

  ,MerchantActiveInactive=ISNULL(S.MerchantActiveInactive,1)

  ,ClosedDownMerchantURL=''

  ,EtlDateInserted=@DateNow

  ,EtlDateUpdated=@DateNow

  ,EtlDescriptionLastWord=@WhereDescriptionEndsWith

  ,EtlProductNameLastWord=@WhereProdNameEndsWith

  ,EtlProcessedFlag=NULL

  ,EtlMasterLookupFilterID=@InsertSortOrder1

  ,EtlMasterLookupFilterSubID=@InsertSortOrder2

  ,EtlDescriptionFirstWord=@WhereDescriptionStartWith

  ,EtlProductNameFirstWord=@WhereProdNameStartWith

  FROM dbo.Staging_Feed_AffW SFA

  LEFT OUTER JOIN dbo.Dim_CategoryMain DCM

  ON @CategoryMainID = DCM.CategoryMainFeedID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),SFA.merchant_id) = S.SellerID

  WHERE

  SFA.EtlInsertedRows = 'NotSet'

  AND @InsertSortOrder2 = 5

  AND CONTAINS((SFA.EtlProductNameFirstWord), @WhereProdNameStartWith)

  --AND CONTAINS((SFA.EtlProductNameLastWord), @WhereProdNameEndsWith)

  AND SFA.product_name LIKE '%' + @WhereProdNameEndsLike + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike1 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike2 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike3 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike4 + '%'

  ELSE

  IF EXISTS(

  SELECT @InsertSortOrder2

  WHERE @InsertSortOrder2 = 6

  )

  INSERT INTO dbo.Fact_Feed_Products

  SELECT

  Dim_AffiliateProviderID=1

  ,Dim_AffiliateProgramID=1

  ,Dim_WebSiteID= @WebSiteID

  ,Dim_CategoryMainID= @CategoryMainID -- -- MIL.CategoryMainID

  ,Dim_CategorySub= @CategorySubID -- -- MIL.CategorySubID

  ,Dim_CategorySubSub= @CategorySubSubID -- -- MIL.CategorySubSubID

  ,Dim_GsScriptCategoryName= @GsScriptCategoryName -- -- MIL.GsScriptCategoryName

  ,Dim_BoScriptCategoryName= @BoScriptCategoryName -- -- MIL.BoScriptCategoryName

  ,Dim_WebSiteCategoryName= @WebSiteCategoryName -- -- MIL.WebSiteCategoryName

  ,Dim_WebSiteCategorySubName= @WebSiteSubCategoryName -- -- MIL.WebSiteSubCategoryName

  ,Dim_DisplayTypeWebDescCompSecID=@DisplayTypeWebDescCompSecID

  ,Dim_MasterImportLookupsID=@DataID

  ,Dim_DateCreated=@DateNow

  ,DIM_PostedToGs=0

  ,Dim_PostedToGsDate=''

  ,SFA.aw_product_id

  ,SFA.merchant_product_id

  ,SFA.merchant_category

  ,SFA.aw_deep_link

  ,SFA.merchant_image_url

  ,SFA.search_price

  ,[description]=REPLACE(SFA.[description],'Â','')

  ,SFA.product_name

  ,SFA.merchant_deep_link

  ,SFA.aw_image_url

  ,SFA.merchant_name

  ,SFA.merchant_id

  ,SFA.category_name

  ,SFA.category_id

  ,SFA.delivery_cost

  ,SFA.currency

  ,SFA.store_price

  ,SFA.display_price

  ,SFA.data_feed_id

  ,SFA.rrp_price

  ,SFA.specifications

  ,SFA.condition

  ,SFA.promotional_text

  ,SFA.warranty

  ,SFA.merchant_thumb_url

  ,SFA.aw_thumb_url

  ,SFA.brand_name

  ,SFA.brand_id

  ,SFA.delivery_time

  ,SFA.valid_from

  ,SFA.valid_to

  ,SFA.web_offer

  ,SFA.pre_order

  ,SFA.in_stock

  ,SFA.stock_quantity

  ,SFA.is_for_sale

  ,SFA.product_type

  ,SFA.commission_group

  ,SFA.upc

  ,SFA.ean

  ,SFA.mpn

  ,SFA.isbn

  ,SFA.model_number

  ,SFA.parent_product_id

  ,SFA.[language]

  ,SFA.last_updated

  ,SFA.dimensions

  ,SFA.colour

  ,SFA.keywords

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,SFA.custom_4

  ,SFA.custom_5

  ,SFA.saving

  ,SFA.delivery_weight

  ,SFA.delivery_restrictions

  ,SFA.reviews

  ,SFA.average_rating

  ,SFA.number_stars

  ,SFA.number_available

  ,SFA.rating

  ,SFA.alternate_image

  ,SFA.large_image

  ,SFA.basket_link

  ,FilterProductType=CASEWHENDCM.CategoryMainName IS NULLTHENdbo.UfnProductType(REPLACE(SFA.commission_group,' ' ,''))

  ELSEDCM.CategoryMainNameEND

  ,FilterBackSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterCupSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.UfnCupSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterFullSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_1,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_2,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_3,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_4,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_5,' ' ,''),'Mixed Sizes')

  ELSEISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes') END

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  ,FilterBrand=CASEWHENSFA.brand_name IS NOT NULLTHENSFA.brand_nameELSE'No Brand'END

  ,FilterSeller=CASEWHENSFA.merchant_name IS NOT NULLTHENSFA.merchant_nameELSE'No Store Name'END

  ,FilterProductTypeLevel1=''

  ,FilterProductTypeLevel2=''

  ,FilterProductTypeLevel3=''

  ,FilterProductTypeLevel4=''

  ,FilterProductTypeLevel5=''

  ,FilterProductTypeLevel6=''

  ,FilterProductTypeLevel7=''

  ,FilterProductTypeLevel8=''

  ,FilterProductTypeLevel9=''

  ,FilterProductTypeLevel10=''

  ,MerchantActiveInactive=ISNULL(S.MerchantActiveInactive,1)

  ,ClosedDownMerchantURL=''

  ,EtlDateInserted=@DateNow

  ,EtlDateUpdated=@DateNow

  ,EtlDescriptionLastWord=@WhereDescriptionEndsWith

  ,EtlProductNameLastWord=@WhereProdNameEndsWith

  ,EtlProcessedFlag=NULL

  ,EtlMasterLookupFilterID=@InsertSortOrder1

  ,EtlMasterLookupFilterSubID=@InsertSortOrder2

  ,EtlDescriptionFirstWord=@WhereDescriptionStartWith

  ,EtlProductNameFirstWord=@WhereProdNameStartWith

  FROM dbo.Staging_Feed_AffW SFA

  LEFT OUTER JOIN dbo.Dim_CategoryMain DCM

  ON @CategoryMainID = DCM.CategoryMainFeedID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),SFA.merchant_id) = S.SellerID

  WHERE

  SFA.EtlInsertedRows = 'NotSet'

  AND @InsertSortOrder2 = 6

  AND CONTAINS((SFA.EtlProductNameFirstWord), @WhereProdNameStartWith)

  --AND CONTAINS((SFA.EtlProductNameLastWord), @WhereProdNameEndsWith)

  AND SFA.product_name LIKE '%' + @WhereProdNameEndsLike + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike1 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike2 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike3 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike4 + '%'

  ELSE

  IF EXISTS(

  SELECT @InsertSortOrder2

  WHERE @InsertSortOrder2 = 7

  )

  INSERT INTO dbo.Fact_Feed_Products

  SELECT

  Dim_AffiliateProviderID=1

  ,Dim_AffiliateProgramID=1

  ,Dim_WebSiteID= @WebSiteID

  ,Dim_CategoryMainID= @CategoryMainID -- -- MIL.CategoryMainID

  ,Dim_CategorySub= @CategorySubID -- -- MIL.CategorySubID

  ,Dim_CategorySubSub= @CategorySubSubID -- -- MIL.CategorySubSubID

  ,Dim_GsScriptCategoryName= @GsScriptCategoryName -- -- MIL.GsScriptCategoryName

  ,Dim_BoScriptCategoryName= @BoScriptCategoryName -- -- MIL.BoScriptCategoryName

  ,Dim_WebSiteCategoryName= @WebSiteCategoryName -- -- MIL.WebSiteCategoryName

  ,Dim_WebSiteCategorySubName= @WebSiteSubCategoryName -- -- MIL.WebSiteSubCategoryName

  ,Dim_DisplayTypeWebDescCompSecID=@DisplayTypeWebDescCompSecID

  ,Dim_MasterImportLookupsID=@DataID

  ,Dim_DateCreated=@DateNow

  ,DIM_PostedToGs=0

  ,Dim_PostedToGsDate=''

  ,SFA.aw_product_id

  ,SFA.merchant_product_id

  ,SFA.merchant_category

  ,SFA.aw_deep_link

  ,SFA.merchant_image_url

  ,SFA.search_price

  ,[description]=REPLACE(SFA.[description],'Â','')

  ,SFA.product_name

  ,SFA.merchant_deep_link

  ,SFA.aw_image_url

  ,SFA.merchant_name

  ,SFA.merchant_id

  ,SFA.category_name

  ,SFA.category_id

  ,SFA.delivery_cost

  ,SFA.currency

  ,SFA.store_price

  ,SFA.display_price

  ,SFA.data_feed_id

  ,SFA.rrp_price

  ,SFA.specifications

  ,SFA.condition

  ,SFA.promotional_text

  ,SFA.warranty

  ,SFA.merchant_thumb_url

  ,SFA.aw_thumb_url

  ,SFA.brand_name

  ,SFA.brand_id

  ,SFA.delivery_time

  ,SFA.valid_from

  ,SFA.valid_to

  ,SFA.web_offer

  ,SFA.pre_order

  ,SFA.in_stock

  ,SFA.stock_quantity

  ,SFA.is_for_sale

  ,SFA.product_type

  ,SFA.commission_group

  ,SFA.upc

  ,SFA.ean

  ,SFA.mpn

  ,SFA.isbn

  ,SFA.model_number

  ,SFA.parent_product_id

  ,SFA.[language]

  ,SFA.last_updated

  ,SFA.dimensions

  ,SFA.colour

  ,SFA.keywords

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,SFA.custom_4

  ,SFA.custom_5

  ,SFA.saving

  ,SFA.delivery_weight

  ,SFA.delivery_restrictions

  ,SFA.reviews

  ,SFA.average_rating

  ,SFA.number_stars

  ,SFA.number_available

  ,SFA.rating

  ,SFA.alternate_image

  ,SFA.large_image

  ,SFA.basket_link

  ,FilterProductType=CASEWHENDCM.CategoryMainName IS NULLTHENdbo.UfnProductType(REPLACE(SFA.commission_group,' ' ,''))

  ELSEDCM.CategoryMainNameEND

  ,FilterBackSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterCupSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.UfnCupSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterFullSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_1,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_2,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_3,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_4,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_5,' ' ,''),'Mixed Sizes')

  ELSEISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes') END

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  ,FilterBrand=CASEWHENSFA.brand_name IS NOT NULLTHENSFA.brand_nameELSE'No Brand'END

  ,FilterSeller=CASEWHENSFA.merchant_name IS NOT NULLTHENSFA.merchant_nameELSE'No Store Name'END

  ,FilterProductTypeLevel1=''

  ,FilterProductTypeLevel2=''

  ,FilterProductTypeLevel3=''

  ,FilterProductTypeLevel4=''

  ,FilterProductTypeLevel5=''

  ,FilterProductTypeLevel6=''

  ,FilterProductTypeLevel7=''

  ,FilterProductTypeLevel8=''

  ,FilterProductTypeLevel9=''

  ,FilterProductTypeLevel10=''

  ,MerchantActiveInactive=ISNULL(S.MerchantActiveInactive,1)

  ,ClosedDownMerchantURL=''

  ,EtlDateInserted=@DateNow

  ,EtlDateUpdated=@DateNow

  ,EtlDescriptionLastWord=@WhereDescriptionEndsWith

  ,EtlProductNameLastWord=@WhereProdNameEndsWith

  ,EtlProcessedFlag=NULL

  ,EtlMasterLookupFilterID=@InsertSortOrder1

  ,EtlMasterLookupFilterSubID=@InsertSortOrder2

  ,EtlDescriptionFirstWord=@WhereDescriptionStartWith

  ,EtlProductNameFirstWord=@WhereProdNameStartWith

  FROM dbo.Staging_Feed_AffW SFA

  LEFT OUTER JOIN dbo.Dim_CategoryMain DCM

  ON @CategoryMainID = DCM.CategoryMainFeedID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),SFA.merchant_id) = S.SellerID

  WHERE

  SFA.EtlInsertedRows = 'NotSet'

  AND @InsertSortOrder2 = 7

  --AND CONTAINS((SFA.EtlProductNameFirstWord), @WhereProdNameStartWith)

  --AND CONTAINS((SFA.EtlProductNameLastWord), @WhereProdNameEndsWith)

  AND SFA.product_name LIKE '%' + @WhereProdNameStartLike + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameEndsLike + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike1 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike2 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike3 + '%'

  AND SFA.product_name LIKE '%' + @WhereProdNameLike4 + '%'

  ELSE

  IF EXISTS(

  SELECT @InsertSortOrder2

  WHERE @InsertSortOrder2 = 8

  )

  INSERT INTO dbo.Fact_Feed_Products

  SELECT

  Dim_AffiliateProviderID=1

  ,Dim_AffiliateProgramID=1

  ,Dim_WebSiteID= @WebSiteID

  ,Dim_CategoryMainID= @CategoryMainID -- -- MIL.CategoryMainID

  ,Dim_CategorySub= @CategorySubID -- -- MIL.CategorySubID

  ,Dim_CategorySubSub= @CategorySubSubID -- -- MIL.CategorySubSubID

  ,Dim_GsScriptCategoryName= @GsScriptCategoryName -- -- MIL.GsScriptCategoryName

  ,Dim_BoScriptCategoryName= @BoScriptCategoryName -- -- MIL.BoScriptCategoryName

  ,Dim_WebSiteCategoryName= @WebSiteCategoryName -- -- MIL.WebSiteCategoryName

  ,Dim_WebSiteCategorySubName= @WebSiteSubCategoryName -- -- MIL.WebSiteSubCategoryName

  ,Dim_DisplayTypeWebDescCompSecID=@DisplayTypeWebDescCompSecID

  ,Dim_MasterImportLookupsID=@DataID

  ,Dim_DateCreated=@DateNow

  ,DIM_PostedToGs=0

  ,Dim_PostedToGsDate=''

  ,SFA.aw_product_id

  ,SFA.merchant_product_id

  ,SFA.merchant_category

  ,SFA.aw_deep_link

  ,SFA.merchant_image_url

  ,SFA.search_price

  ,[description]=REPLACE(SFA.[description],'Â','')

  ,SFA.product_name

  ,SFA.merchant_deep_link

  ,SFA.aw_image_url

  ,SFA.merchant_name

  ,SFA.merchant_id

  ,SFA.category_name

  ,SFA.category_id

  ,SFA.delivery_cost

  ,SFA.currency

  ,SFA.store_price

  ,SFA.display_price

  ,SFA.data_feed_id

  ,SFA.rrp_price

  ,SFA.specifications

  ,SFA.condition

  ,SFA.promotional_text

  ,SFA.warranty

  ,SFA.merchant_thumb_url

  ,SFA.aw_thumb_url

  ,SFA.brand_name

  ,SFA.brand_id

  ,SFA.delivery_time

  ,SFA.valid_from

  ,SFA.valid_to

  ,SFA.web_offer

  ,SFA.pre_order

  ,SFA.in_stock

  ,SFA.stock_quantity

  ,SFA.is_for_sale

  ,SFA.product_type

  ,SFA.commission_group

  ,SFA.upc

  ,SFA.ean

  ,SFA.mpn

  ,SFA.isbn

  ,SFA.model_number

  ,SFA.parent_product_id

  ,SFA.[language]

  ,SFA.last_updated

  ,SFA.dimensions

  ,SFA.colour

  ,SFA.keywords

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,SFA.custom_4

  ,SFA.custom_5

  ,SFA.saving

  ,SFA.delivery_weight

  ,SFA.delivery_restrictions

  ,SFA.reviews

  ,SFA.average_rating

  ,SFA.number_stars

  ,SFA.number_available

  ,SFA.rating

  ,SFA.alternate_image

  ,SFA.large_image

  ,SFA.basket_link

  ,FilterProductType=CASEWHENDCM.CategoryMainName IS NULLTHENdbo.UfnProductType(REPLACE(SFA.commission_group,' ' ,''))

  ELSEDCM.CategoryMainNameEND

  ,FilterBackSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterCupSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.UfnCupSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterFullSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_1,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_2,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_3,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_4,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_5,' ' ,''),'Mixed Sizes')

  ELSEISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes') END

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  ,FilterBrand=CASEWHENSFA.brand_name IS NOT NULLTHENSFA.brand_nameELSE'No Brand'END

  ,FilterSeller=CASEWHENSFA.merchant_name IS NOT NULLTHENSFA.merchant_nameELSE'No Store Name'END

  ,FilterProductTypeLevel1=''

  ,FilterProductTypeLevel2=''

  ,FilterProductTypeLevel3=''

  ,FilterProductTypeLevel4=''

  ,FilterProductTypeLevel5=''

  ,FilterProductTypeLevel6=''

  ,FilterProductTypeLevel7=''

  ,FilterProductTypeLevel8=''

  ,FilterProductTypeLevel9=''

  ,FilterProductTypeLevel10=''

  ,MerchantActiveInactive=ISNULL(S.MerchantActiveInactive,1)

  ,ClosedDownMerchantURL=''

  ,EtlDateInserted=@DateNow

  ,EtlDateUpdated=@DateNow

  ,EtlDescriptionLastWord=@WhereDescriptionEndsWith

  ,EtlProductNameLastWord=@WhereProdNameEndsWith

  ,EtlProcessedFlag=NULL

  ,EtlMasterLookupFilterID=@InsertSortOrder1

  ,EtlMasterLookupFilterSubID=@InsertSortOrder2

  ,EtlDescriptionFirstWord=@WhereDescriptionStartWith

  ,EtlProductNameFirstWord=@WhereProdNameStartWith

  FROM dbo.Staging_Feed_AffW SFA

  LEFT OUTER JOIN dbo.Dim_CategoryMain DCM

  ON @CategoryMainID = DCM.CategoryMainFeedID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),SFA.merchant_id) = S.SellerID

  WHERE

  SFA.EtlInsertedRows = 'NotSet'

  AND @InsertSortOrder2 = 8

  AND CONTAINS((SFA.EtlDescriptionFirstWord), @WhereDescriptionStartWith)

  AND CONTAINS((SFA.EtlDescriptionLastWord), @WhereDescriptionEndsWith)

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike1 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike2 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike3 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike4 + '%'

  ELSE

  IF EXISTS(

  SELECT @InsertSortOrder2

  WHERE @InsertSortOrder2 = 9

  )

  INSERT INTO dbo.Fact_Feed_Products

  SELECT

  Dim_AffiliateProviderID=1

  ,Dim_AffiliateProgramID=1

  ,Dim_WebSiteID= @WebSiteID

  ,Dim_CategoryMainID= @CategoryMainID -- -- MIL.CategoryMainID

  ,Dim_CategorySub= @CategorySubID -- -- MIL.CategorySubID

  ,Dim_CategorySubSub= @CategorySubSubID -- -- MIL.CategorySubSubID

  ,Dim_GsScriptCategoryName= @GsScriptCategoryName -- -- MIL.GsScriptCategoryName

  ,Dim_BoScriptCategoryName= @BoScriptCategoryName -- -- MIL.BoScriptCategoryName

  ,Dim_WebSiteCategoryName= @WebSiteCategoryName -- -- MIL.WebSiteCategoryName

  ,Dim_WebSiteCategorySubName= @WebSiteSubCategoryName -- -- MIL.WebSiteSubCategoryName

  ,Dim_DisplayTypeWebDescCompSecID=@DisplayTypeWebDescCompSecID

  ,Dim_MasterImportLookupsID=@DataID

  ,Dim_DateCreated=@DateNow

  ,DIM_PostedToGs=0

  ,Dim_PostedToGsDate=''

  ,SFA.aw_product_id

  ,SFA.merchant_product_id

  ,SFA.merchant_category

  ,SFA.aw_deep_link

  ,SFA.merchant_image_url

  ,SFA.search_price

  ,[description]=REPLACE(SFA.[description],'Â','')

  ,SFA.product_name

  ,SFA.merchant_deep_link

  ,SFA.aw_image_url

  ,SFA.merchant_name

  ,SFA.merchant_id

  ,SFA.category_name

  ,SFA.category_id

  ,SFA.delivery_cost

  ,SFA.currency

  ,SFA.store_price

  ,SFA.display_price

  ,SFA.data_feed_id

  ,SFA.rrp_price

  ,SFA.specifications

  ,SFA.condition

  ,SFA.promotional_text

  ,SFA.warranty

  ,SFA.merchant_thumb_url

  ,SFA.aw_thumb_url

  ,SFA.brand_name

  ,SFA.brand_id

  ,SFA.delivery_time

  ,SFA.valid_from

  ,SFA.valid_to

  ,SFA.web_offer

  ,SFA.pre_order

  ,SFA.in_stock

  ,SFA.stock_quantity

  ,SFA.is_for_sale

  ,SFA.product_type

  ,SFA.commission_group

  ,SFA.upc

  ,SFA.ean

  ,SFA.mpn

  ,SFA.isbn

  ,SFA.model_number

  ,SFA.parent_product_id

  ,SFA.[language]

  ,SFA.last_updated

  ,SFA.dimensions

  ,SFA.colour

  ,SFA.keywords

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,SFA.custom_4

  ,SFA.custom_5

  ,SFA.saving

  ,SFA.delivery_weight

  ,SFA.delivery_restrictions

  ,SFA.reviews

  ,SFA.average_rating

  ,SFA.number_stars

  ,SFA.number_available

  ,SFA.rating

  ,SFA.alternate_image

  ,SFA.large_image

  ,SFA.basket_link

  ,FilterProductType=CASEWHENDCM.CategoryMainName IS NULLTHENdbo.UfnProductType(REPLACE(SFA.commission_group,' ' ,''))

  ELSEDCM.CategoryMainNameEND

  ,FilterBackSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterCupSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.UfnCupSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterFullSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_1,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_2,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_3,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_4,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_5,' ' ,''),'Mixed Sizes')

  ELSEISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes') END

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  ,FilterBrand=CASEWHENSFA.brand_name IS NOT NULLTHENSFA.brand_nameELSE'No Brand'END

  ,FilterSeller=CASEWHENSFA.merchant_name IS NOT NULLTHENSFA.merchant_nameELSE'No Store Name'END

  ,FilterProductTypeLevel1=''

  ,FilterProductTypeLevel2=''

  ,FilterProductTypeLevel3=''

  ,FilterProductTypeLevel4=''

  ,FilterProductTypeLevel5=''

  ,FilterProductTypeLevel6=''

  ,FilterProductTypeLevel7=''

  ,FilterProductTypeLevel8=''

  ,FilterProductTypeLevel9=''

  ,FilterProductTypeLevel10=''

  ,MerchantActiveInactive=ISNULL(S.MerchantActiveInactive,1)

  ,ClosedDownMerchantURL=''

  ,EtlDateInserted=@DateNow

  ,EtlDateUpdated=@DateNow

  ,EtlDescriptionLastWord=@WhereDescriptionEndsWith

  ,EtlProductNameLastWord=@WhereProdNameEndsWith

  ,EtlProcessedFlag=NULL

  ,EtlMasterLookupFilterID=@InsertSortOrder1

  ,EtlMasterLookupFilterSubID=@InsertSortOrder2

  ,EtlDescriptionFirstWord=@WhereDescriptionStartWith

  ,EtlProductNameFirstWord=@WhereProdNameStartWith

  FROM dbo.Staging_Feed_AffW SFA

  LEFT OUTER JOIN dbo.Dim_CategoryMain DCM

  ON @CategoryMainID = DCM.CategoryMainFeedID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),SFA.merchant_id) = S.SellerID

  WHERE

  SFA.EtlInsertedRows = 'NotSet'

  AND @InsertSortOrder2 = 9

  AND CONTAINS((SFA.EtlDescriptionFirstWord), @WhereDescriptionStartWith)

  AND CONTAINS((SFA.EtlDescriptionLastWord), @WhereDescriptionEndsWith)

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike1 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike2 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike3 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike4 + '%'

  ELSE

  IF EXISTS(

  SELECT @InsertSortOrder2

  WHERE @InsertSortOrder2 = 10

  )

  INSERT INTO dbo.Fact_Feed_Products

  SELECT

  Dim_AffiliateProviderID=1

  ,Dim_AffiliateProgramID=1

  ,Dim_WebSiteID= @WebSiteID

  ,Dim_CategoryMainID= @CategoryMainID -- -- MIL.CategoryMainID

  ,Dim_CategorySub= @CategorySubID -- -- MIL.CategorySubID

  ,Dim_CategorySubSub= @CategorySubSubID -- -- MIL.CategorySubSubID

  ,Dim_GsScriptCategoryName= @GsScriptCategoryName -- -- MIL.GsScriptCategoryName

  ,Dim_BoScriptCategoryName= @BoScriptCategoryName -- -- MIL.BoScriptCategoryName

  ,Dim_WebSiteCategoryName= @WebSiteCategoryName -- -- MIL.WebSiteCategoryName

  ,Dim_WebSiteCategorySubName= @WebSiteSubCategoryName -- -- MIL.WebSiteSubCategoryName

  ,Dim_DisplayTypeWebDescCompSecID=@DisplayTypeWebDescCompSecID

  ,Dim_MasterImportLookupsID=@DataID

  ,Dim_DateCreated=@DateNow

  ,DIM_PostedToGs=0

  ,Dim_PostedToGsDate=''

  ,SFA.aw_product_id

  ,SFA.merchant_product_id

  ,SFA.merchant_category

  ,SFA.aw_deep_link

  ,SFA.merchant_image_url

  ,SFA.search_price

  ,[description]=REPLACE(SFA.[description],'Â','')

  ,SFA.product_name

  ,SFA.merchant_deep_link

  ,SFA.aw_image_url

  ,SFA.merchant_name

  ,SFA.merchant_id

  ,SFA.category_name

  ,SFA.category_id

  ,SFA.delivery_cost

  ,SFA.currency

  ,SFA.store_price

  ,SFA.display_price

  ,SFA.data_feed_id

  ,SFA.rrp_price

  ,SFA.specifications

  ,SFA.condition

  ,SFA.promotional_text

  ,SFA.warranty

  ,SFA.merchant_thumb_url

  ,SFA.aw_thumb_url

  ,SFA.brand_name

  ,SFA.brand_id

  ,SFA.delivery_time

  ,SFA.valid_from

  ,SFA.valid_to

  ,SFA.web_offer

  ,SFA.pre_order

  ,SFA.in_stock

  ,SFA.stock_quantity

  ,SFA.is_for_sale

  ,SFA.product_type

  ,SFA.commission_group

  ,SFA.upc

  ,SFA.ean

  ,SFA.mpn

  ,SFA.isbn

  ,SFA.model_number

  ,SFA.parent_product_id

  ,SFA.[language]

  ,SFA.last_updated

  ,SFA.dimensions

  ,SFA.colour

  ,SFA.keywords

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,SFA.custom_4

  ,SFA.custom_5

  ,SFA.saving

  ,SFA.delivery_weight

  ,SFA.delivery_restrictions

  ,SFA.reviews

  ,SFA.average_rating

  ,SFA.number_stars

  ,SFA.number_available

  ,SFA.rating

  ,SFA.alternate_image

  ,SFA.large_image

  ,SFA.basket_link

  ,FilterProductType=CASEWHENDCM.CategoryMainName IS NULLTHENdbo.UfnProductType(REPLACE(SFA.commission_group,' ' ,''))

  ELSEDCM.CategoryMainNameEND

  ,FilterBackSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterCupSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.UfnCupSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterFullSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_1,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_2,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_3,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_4,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_5,' ' ,''),'Mixed Sizes')

  ELSEISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes') END

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  ,FilterBrand=CASEWHENSFA.brand_name IS NOT NULLTHENSFA.brand_nameELSE'No Brand'END

  ,FilterSeller=CASEWHENSFA.merchant_name IS NOT NULLTHENSFA.merchant_nameELSE'No Store Name'END

  ,FilterProductTypeLevel1=''

  ,FilterProductTypeLevel2=''

  ,FilterProductTypeLevel3=''

  ,FilterProductTypeLevel4=''

  ,FilterProductTypeLevel5=''

  ,FilterProductTypeLevel6=''

  ,FilterProductTypeLevel7=''

  ,FilterProductTypeLevel8=''

  ,FilterProductTypeLevel9=''

  ,FilterProductTypeLevel10=''

  ,MerchantActiveInactive=ISNULL(S.MerchantActiveInactive,1)

  ,ClosedDownMerchantURL=''

  ,EtlDateInserted=@DateNow

  ,EtlDateUpdated=@DateNow

  ,EtlDescriptionLastWord=@WhereDescriptionEndsWith

  ,EtlProductNameLastWord=@WhereProdNameEndsWith

  ,EtlProcessedFlag=NULL

  ,EtlMasterLookupFilterID=@InsertSortOrder1

  ,EtlMasterLookupFilterSubID=@InsertSortOrder2

  ,EtlDescriptionFirstWord=@WhereDescriptionStartWith

  ,EtlProductNameFirstWord=@WhereProdNameStartWith

  FROM dbo.Staging_Feed_AffW SFA

  LEFT OUTER JOIN dbo.Dim_CategoryMain DCM

  ON @CategoryMainID = DCM.CategoryMainFeedID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),SFA.merchant_id) = S.SellerID

  WHERE

  SFA.EtlInsertedRows = 'NotSet'

  AND @InsertSortOrder2 = 10

  --AND CONTAINS((SFA.EtlDescriptionFirstWord), @WhereDescriptionStartWith)

  AND CONTAINS((SFA.EtlDescriptionLastWord), @WhereDescriptionEndsWith)

  AND SFA.[description] LIKE '%' + @WhereDescriptionStartLike + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike1 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike2 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike3 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike4 + '%'

  ELSE

  IF EXISTS(

  SELECT @InsertSortOrder2

  WHERE @InsertSortOrder2 = 11

  )

  INSERT INTO dbo.Fact_Feed_Products

  SELECT

  Dim_AffiliateProviderID=1

  ,Dim_AffiliateProgramID=1

  ,Dim_WebSiteID= @WebSiteID

  ,Dim_CategoryMainID= @CategoryMainID -- -- MIL.CategoryMainID

  ,Dim_CategorySub= @CategorySubID -- -- MIL.CategorySubID

  ,Dim_CategorySubSub= @CategorySubSubID -- -- MIL.CategorySubSubID

  ,Dim_GsScriptCategoryName= @GsScriptCategoryName -- -- MIL.GsScriptCategoryName

  ,Dim_BoScriptCategoryName= @BoScriptCategoryName -- -- MIL.BoScriptCategoryName

  ,Dim_WebSiteCategoryName= @WebSiteCategoryName -- -- MIL.WebSiteCategoryName

  ,Dim_WebSiteCategorySubName= @WebSiteSubCategoryName -- -- MIL.WebSiteSubCategoryName

  ,Dim_DisplayTypeWebDescCompSecID=@DisplayTypeWebDescCompSecID

  ,Dim_MasterImportLookupsID=@DataID

  ,Dim_DateCreated=@DateNow

  ,DIM_PostedToGs=0

  ,Dim_PostedToGsDate=''

  ,SFA.aw_product_id

  ,SFA.merchant_product_id

  ,SFA.merchant_category

  ,SFA.aw_deep_link

  ,SFA.merchant_image_url

  ,SFA.search_price

  ,[description]=REPLACE(SFA.[description],'Â','')

  ,SFA.product_name

  ,SFA.merchant_deep_link

  ,SFA.aw_image_url

  ,SFA.merchant_name

  ,SFA.merchant_id

  ,SFA.category_name

  ,SFA.category_id

  ,SFA.delivery_cost

  ,SFA.currency

  ,SFA.store_price

  ,SFA.display_price

  ,SFA.data_feed_id

  ,SFA.rrp_price

  ,SFA.specifications

  ,SFA.condition

  ,SFA.promotional_text

  ,SFA.warranty

  ,SFA.merchant_thumb_url

  ,SFA.aw_thumb_url

  ,SFA.brand_name

  ,SFA.brand_id

  ,SFA.delivery_time

  ,SFA.valid_from

  ,SFA.valid_to

  ,SFA.web_offer

  ,SFA.pre_order

  ,SFA.in_stock

  ,SFA.stock_quantity

  ,SFA.is_for_sale

  ,SFA.product_type

  ,SFA.commission_group

  ,SFA.upc

  ,SFA.ean

  ,SFA.mpn

  ,SFA.isbn

  ,SFA.model_number

  ,SFA.parent_product_id

  ,SFA.[language]

  ,SFA.last_updated

  ,SFA.dimensions

  ,SFA.colour

  ,SFA.keywords

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,SFA.custom_4

  ,SFA.custom_5

  ,SFA.saving

  ,SFA.delivery_weight

  ,SFA.delivery_restrictions

  ,SFA.reviews

  ,SFA.average_rating

  ,SFA.number_stars

  ,SFA.number_available

  ,SFA.rating

  ,SFA.alternate_image

  ,SFA.large_image

  ,SFA.basket_link

  ,FilterProductType=CASEWHENDCM.CategoryMainName IS NULLTHENdbo.UfnProductType(REPLACE(SFA.commission_group,' ' ,''))

  ELSEDCM.CategoryMainNameEND

  ,FilterBackSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterCupSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.UfnCupSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterFullSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_1,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_2,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_3,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_4,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_5,' ' ,''),'Mixed Sizes')

  ELSEISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes') END

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  ,FilterBrand=CASEWHENSFA.brand_name IS NOT NULLTHENSFA.brand_nameELSE'No Brand'END

  ,FilterSeller=CASEWHENSFA.merchant_name IS NOT NULLTHENSFA.merchant_nameELSE'No Store Name'END

  ,FilterProductTypeLevel1=''

  ,FilterProductTypeLevel2=''

  ,FilterProductTypeLevel3=''

  ,FilterProductTypeLevel4=''

  ,FilterProductTypeLevel5=''

  ,FilterProductTypeLevel6=''

  ,FilterProductTypeLevel7=''

  ,FilterProductTypeLevel8=''

  ,FilterProductTypeLevel9=''

  ,FilterProductTypeLevel10=''

  ,MerchantActiveInactive=ISNULL(S.MerchantActiveInactive,1)

  ,ClosedDownMerchantURL=''

  ,EtlDateInserted=@DateNow

  ,EtlDateUpdated=@DateNow

  ,EtlDescriptionLastWord=@WhereDescriptionEndsWith

  ,EtlProductNameLastWord=@WhereProdNameEndsWith

  ,EtlProcessedFlag=NULL

  ,EtlMasterLookupFilterID=@InsertSortOrder1

  ,EtlMasterLookupFilterSubID=@InsertSortOrder2

  ,EtlDescriptionFirstWord=@WhereDescriptionStartWith

  ,EtlProductNameFirstWord=@WhereProdNameStartWith

  FROM dbo.Staging_Feed_AffW SFA

  LEFT OUTER JOIN dbo.Dim_CategoryMain DCM

  ON @CategoryMainID = DCM.CategoryMainFeedID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),SFA.merchant_id) = S.SellerID

  WHERE

  SFA.EtlInsertedRows = 'NotSet'

  AND @InsertSortOrder2 = 11

  --AND CONTAINS((SFA.EtlDescriptionFirstWord), @WhereDescriptionStartWith)

  AND CONTAINS((SFA.EtlDescriptionLastWord), @WhereDescriptionEndsWith)

  AND SFA.[description] LIKE '%' + @WhereDescriptionStartLike + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike1 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike2 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike3 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike4 + '%'

  ELSE

  IF EXISTS(

  SELECT @InsertSortOrder2

  WHERE @InsertSortOrder2 = 12

  )

  INSERT INTO dbo.Fact_Feed_Products

  SELECT

  Dim_AffiliateProviderID=1

  ,Dim_AffiliateProgramID=1

  ,Dim_WebSiteID= @WebSiteID

  ,Dim_CategoryMainID= @CategoryMainID -- -- MIL.CategoryMainID

  ,Dim_CategorySub= @CategorySubID -- -- MIL.CategorySubID

  ,Dim_CategorySubSub= @CategorySubSubID -- -- MIL.CategorySubSubID

  ,Dim_GsScriptCategoryName= @GsScriptCategoryName -- -- MIL.GsScriptCategoryName

  ,Dim_BoScriptCategoryName= @BoScriptCategoryName -- -- MIL.BoScriptCategoryName

  ,Dim_WebSiteCategoryName= @WebSiteCategoryName -- -- MIL.WebSiteCategoryName

  ,Dim_WebSiteCategorySubName= @WebSiteSubCategoryName -- -- MIL.WebSiteSubCategoryName

  ,Dim_DisplayTypeWebDescCompSecID=@DisplayTypeWebDescCompSecID

  ,Dim_MasterImportLookupsID=@DataID

  ,Dim_DateCreated=@DateNow

  ,DIM_PostedToGs=0

  ,Dim_PostedToGsDate=''

  ,SFA.aw_product_id

  ,SFA.merchant_product_id

  ,SFA.merchant_category

  ,SFA.aw_deep_link

  ,SFA.merchant_image_url

  ,SFA.search_price

  ,[description]=REPLACE(SFA.[description],'Â','')

  ,SFA.product_name

  ,SFA.merchant_deep_link

  ,SFA.aw_image_url

  ,SFA.merchant_name

  ,SFA.merchant_id

  ,SFA.category_name

  ,SFA.category_id

  ,SFA.delivery_cost

  ,SFA.currency

  ,SFA.store_price

  ,SFA.display_price

  ,SFA.data_feed_id

  ,SFA.rrp_price

  ,SFA.specifications

  ,SFA.condition

  ,SFA.promotional_text

  ,SFA.warranty

  ,SFA.merchant_thumb_url

  ,SFA.aw_thumb_url

  ,SFA.brand_name

  ,SFA.brand_id

  ,SFA.delivery_time

  ,SFA.valid_from

  ,SFA.valid_to

  ,SFA.web_offer

  ,SFA.pre_order

  ,SFA.in_stock

  ,SFA.stock_quantity

  ,SFA.is_for_sale

  ,SFA.product_type

  ,SFA.commission_group

  ,SFA.upc

  ,SFA.ean

  ,SFA.mpn

  ,SFA.isbn

  ,SFA.model_number

  ,SFA.parent_product_id

  ,SFA.[language]

  ,SFA.last_updated

  ,SFA.dimensions

  ,SFA.colour

  ,SFA.keywords

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,SFA.custom_4

  ,SFA.custom_5

  ,SFA.saving

  ,SFA.delivery_weight

  ,SFA.delivery_restrictions

  ,SFA.reviews

  ,SFA.average_rating

  ,SFA.number_stars

  ,SFA.number_available

  ,SFA.rating

  ,SFA.alternate_image

  ,SFA.large_image

  ,SFA.basket_link

  ,FilterProductType=CASEWHENDCM.CategoryMainName IS NULLTHENdbo.UfnProductType(REPLACE(SFA.commission_group,' ' ,''))

  ELSEDCM.CategoryMainNameEND

  ,FilterBackSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterCupSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.UfnCupSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterFullSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_1,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_2,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_3,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_4,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_5,' ' ,''),'Mixed Sizes')

  ELSEISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes') END

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  ,FilterBrand=CASEWHENSFA.brand_name IS NOT NULLTHENSFA.brand_nameELSE'No Brand'END

  ,FilterSeller=CASEWHENSFA.merchant_name IS NOT NULLTHENSFA.merchant_nameELSE'No Store Name'END

  ,FilterProductTypeLevel1=''

  ,FilterProductTypeLevel2=''

  ,FilterProductTypeLevel3=''

  ,FilterProductTypeLevel4=''

  ,FilterProductTypeLevel5=''

  ,FilterProductTypeLevel6=''

  ,FilterProductTypeLevel7=''

  ,FilterProductTypeLevel8=''

  ,FilterProductTypeLevel9=''

  ,FilterProductTypeLevel10=''

  ,MerchantActiveInactive=ISNULL(S.MerchantActiveInactive,1)

  ,ClosedDownMerchantURL=''

  ,EtlDateInserted=@DateNow

  ,EtlDateUpdated=@DateNow

  ,EtlDescriptionLastWord=@WhereDescriptionEndsWith

  ,EtlProductNameLastWord=@WhereProdNameEndsWith

  ,EtlProcessedFlag=NULL

  ,EtlMasterLookupFilterID=@InsertSortOrder1

  ,EtlMasterLookupFilterSubID=@InsertSortOrder2

  ,EtlDescriptionFirstWord=@WhereDescriptionStartWith

  ,EtlProductNameFirstWord=@WhereProdNameStartWith

  FROM dbo.Staging_Feed_AffW SFA

  LEFT OUTER JOIN dbo.Dim_CategoryMain DCM

  ON @CategoryMainID = DCM.CategoryMainFeedID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),SFA.merchant_id) = S.SellerID

  WHERE

  SFA.EtlInsertedRows = 'NotSet'

  AND @InsertSortOrder2 = 12

  AND CONTAINS((SFA.EtlDescriptionFirstWord), @WhereDescriptionStartWith)

  --AND CONTAINS((SFA.EtlDescriptionLastWord), @WhereDescriptionEndsWith)

  AND SFA.[description] LIKE '%' + @WhereDescriptionEndsLike + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike1 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike2 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike3 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike4 + '%'

  ELSE

  IF EXISTS(

  SELECT @InsertSortOrder2

  WHERE @InsertSortOrder2 = 13

  )

  INSERT INTO dbo.Fact_Feed_Products

  SELECT

  Dim_AffiliateProviderID=1

  ,Dim_AffiliateProgramID=1

  ,Dim_WebSiteID= @WebSiteID

  ,Dim_CategoryMainID= @CategoryMainID -- -- MIL.CategoryMainID

  ,Dim_CategorySub= @CategorySubID -- -- MIL.CategorySubID

  ,Dim_CategorySubSub= @CategorySubSubID -- -- MIL.CategorySubSubID

  ,Dim_GsScriptCategoryName= @GsScriptCategoryName -- -- MIL.GsScriptCategoryName

  ,Dim_BoScriptCategoryName= @BoScriptCategoryName -- -- MIL.BoScriptCategoryName

  ,Dim_WebSiteCategoryName= @WebSiteCategoryName -- -- MIL.WebSiteCategoryName

  ,Dim_WebSiteCategorySubName= @WebSiteSubCategoryName -- -- MIL.WebSiteSubCategoryName

  ,Dim_DisplayTypeWebDescCompSecID=@DisplayTypeWebDescCompSecID

  ,Dim_MasterImportLookupsID=@DataID

  ,Dim_DateCreated=@DateNow

  ,DIM_PostedToGs=0

  ,Dim_PostedToGsDate=''

  ,SFA.aw_product_id

  ,SFA.merchant_product_id

  ,SFA.merchant_category

  ,SFA.aw_deep_link

  ,SFA.merchant_image_url

  ,SFA.search_price

  ,[description]=REPLACE(SFA.[description],'Â','')

  ,SFA.product_name

  ,SFA.merchant_deep_link

  ,SFA.aw_image_url

  ,SFA.merchant_name

  ,SFA.merchant_id

  ,SFA.category_name

  ,SFA.category_id

  ,SFA.delivery_cost

  ,SFA.currency

  ,SFA.store_price

  ,SFA.display_price

  ,SFA.data_feed_id

  ,SFA.rrp_price

  ,SFA.specifications

  ,SFA.condition

  ,SFA.promotional_text

  ,SFA.warranty

  ,SFA.merchant_thumb_url

  ,SFA.aw_thumb_url

  ,SFA.brand_name

  ,SFA.brand_id

  ,SFA.delivery_time

  ,SFA.valid_from

  ,SFA.valid_to

  ,SFA.web_offer

  ,SFA.pre_order

  ,SFA.in_stock

  ,SFA.stock_quantity

  ,SFA.is_for_sale

  ,SFA.product_type

  ,SFA.commission_group

  ,SFA.upc

  ,SFA.ean

  ,SFA.mpn

  ,SFA.isbn

  ,SFA.model_number

  ,SFA.parent_product_id

  ,SFA.[language]

  ,SFA.last_updated

  ,SFA.dimensions

  ,SFA.colour

  ,SFA.keywords

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,SFA.custom_4

  ,SFA.custom_5

  ,SFA.saving

  ,SFA.delivery_weight

  ,SFA.delivery_restrictions

  ,SFA.reviews

  ,SFA.average_rating

  ,SFA.number_stars

  ,SFA.number_available

  ,SFA.rating

  ,SFA.alternate_image

  ,SFA.large_image

  ,SFA.basket_link

  ,FilterProductType=CASEWHENDCM.CategoryMainName IS NULLTHENdbo.UfnProductType(REPLACE(SFA.commission_group,' ' ,''))

  ELSEDCM.CategoryMainNameEND

  ,FilterBackSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterCupSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.UfnCupSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterFullSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_1,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_2,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_3,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_4,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_5,' ' ,''),'Mixed Sizes')

  ELSEISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes') END

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  ,FilterBrand=CASEWHENSFA.brand_name IS NOT NULLTHENSFA.brand_nameELSE'No Brand'END

  ,FilterSeller=CASEWHENSFA.merchant_name IS NOT NULLTHENSFA.merchant_nameELSE'No Store Name'END

  ,FilterProductTypeLevel1=''

  ,FilterProductTypeLevel2=''

  ,FilterProductTypeLevel3=''

  ,FilterProductTypeLevel4=''

  ,FilterProductTypeLevel5=''

  ,FilterProductTypeLevel6=''

  ,FilterProductTypeLevel7=''

  ,FilterProductTypeLevel8=''

  ,FilterProductTypeLevel9=''

  ,FilterProductTypeLevel10=''

  ,MerchantActiveInactive=ISNULL(S.MerchantActiveInactive,1)

  ,ClosedDownMerchantURL=''

  ,EtlDateInserted=@DateNow

  ,EtlDateUpdated=@DateNow

  ,EtlDescriptionLastWord=@WhereDescriptionEndsWith

  ,EtlProductNameLastWord=@WhereProdNameEndsWith

  ,EtlProcessedFlag=NULL

  ,EtlMasterLookupFilterID=@InsertSortOrder1

  ,EtlMasterLookupFilterSubID=@InsertSortOrder2

  ,EtlDescriptionFirstWord=@WhereDescriptionStartWith

  ,EtlProductNameFirstWord=@WhereProdNameStartWith

  FROM dbo.Staging_Feed_AffW SFA

  LEFT OUTER JOIN dbo.Dim_CategoryMain DCM

  ON @CategoryMainID = DCM.CategoryMainFeedID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),SFA.merchant_id) = S.SellerID

  WHERE

  SFA.EtlInsertedRows = 'NotSet'

  AND @InsertSortOrder2 = 13

  AND CONTAINS((SFA.EtlDescriptionFirstWord), @WhereDescriptionStartWith)

  --AND CONTAINS((SFA.EtlDescriptionLastWord), @WhereDescriptionEndsWith)

  AND SFA.[description] LIKE '%' + @WhereDescriptionEndsLike + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike1 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike2 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike3 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike4 + '%'

  ELSE

  IF EXISTS(

  SELECT @InsertSortOrder2

  WHERE @InsertSortOrder2 = 14

  )

  INSERT INTO dbo.Fact_Feed_Products

  SELECT

  Dim_AffiliateProviderID=1

  ,Dim_AffiliateProgramID=1

  ,Dim_WebSiteID= @WebSiteID

  ,Dim_CategoryMainID= @CategoryMainID -- -- MIL.CategoryMainID

  ,Dim_CategorySub= @CategorySubID -- -- MIL.CategorySubID

  ,Dim_CategorySubSub= @CategorySubSubID -- -- MIL.CategorySubSubID

  ,Dim_GsScriptCategoryName= @GsScriptCategoryName -- -- MIL.GsScriptCategoryName

  ,Dim_BoScriptCategoryName= @BoScriptCategoryName -- -- MIL.BoScriptCategoryName

  ,Dim_WebSiteCategoryName= @WebSiteCategoryName -- -- MIL.WebSiteCategoryName

  ,Dim_WebSiteCategorySubName= @WebSiteSubCategoryName -- -- MIL.WebSiteSubCategoryName

  ,Dim_DisplayTypeWebDescCompSecID=@DisplayTypeWebDescCompSecID

  ,Dim_MasterImportLookupsID=@DataID

  ,Dim_DateCreated=@DateNow

  ,DIM_PostedToGs=0

  ,Dim_PostedToGsDate=''

  ,SFA.aw_product_id

  ,SFA.merchant_product_id

  ,SFA.merchant_category

  ,SFA.aw_deep_link

  ,SFA.merchant_image_url

  ,SFA.search_price

  ,[description]=REPLACE(SFA.[description],'Â','')

  ,SFA.product_name

  ,SFA.merchant_deep_link

  ,SFA.aw_image_url

  ,SFA.merchant_name

  ,SFA.merchant_id

  ,SFA.category_name

  ,SFA.category_id

  ,SFA.delivery_cost

  ,SFA.currency

  ,SFA.store_price

  ,SFA.display_price

  ,SFA.data_feed_id

  ,SFA.rrp_price

  ,SFA.specifications

  ,SFA.condition

  ,SFA.promotional_text

  ,SFA.warranty

  ,SFA.merchant_thumb_url

  ,SFA.aw_thumb_url

  ,SFA.brand_name

  ,SFA.brand_id

  ,SFA.delivery_time

  ,SFA.valid_from

  ,SFA.valid_to

  ,SFA.web_offer

  ,SFA.pre_order

  ,SFA.in_stock

  ,SFA.stock_quantity

  ,SFA.is_for_sale

  ,SFA.product_type

  ,SFA.commission_group

  ,SFA.upc

  ,SFA.ean

  ,SFA.mpn

  ,SFA.isbn

  ,SFA.model_number

  ,SFA.parent_product_id

  ,SFA.[language]

  ,SFA.last_updated

  ,SFA.dimensions

  ,SFA.colour

  ,SFA.keywords

  ,SFA.custom_1

  ,SFA.custom_2

  ,SFA.custom_3

  ,SFA.custom_4

  ,SFA.custom_5

  ,SFA.saving

  ,SFA.delivery_weight

  ,SFA.delivery_restrictions

  ,SFA.reviews

  ,SFA.average_rating

  ,SFA.number_stars

  ,SFA.number_available

  ,SFA.rating

  ,SFA.alternate_image

  ,SFA.large_image

  ,SFA.basket_link

  ,FilterProductType=CASEWHENDCM.CategoryMainName IS NULLTHENdbo.UfnProductType(REPLACE(SFA.commission_group,' ' ,''))

  ELSEDCM.CategoryMainNameEND

  ,FilterBackSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.Ufn_BackSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterCupSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.UfnCupSize(SFA.commission_group) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.commission_group)

  WHENSFA.custom_1 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_1) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_1)

  WHENSFA.custom_2 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_2) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_2)

  WHENSFA.custom_3 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_3) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_3)

  WHENSFA.custom_4 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_4) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_4)

  WHENSFA.custom_5 IS NOT NULL AND dbo.UfnCupSize(SFA.custom_5) <> 'Mixed Sizes'THENdbo.UfnCupSize(SFA.custom_5)

  ELSE'Mixed Sizes'END

  ,FilterFullSize=CASEWHENSFA.commission_group IS NOT NULL AND dbo.Ufn_BackSize(SFA.commission_group) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_1 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_1) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_1,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_2 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_2) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_2,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_3 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_3) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_3,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_4 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_4) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_4,' ' ,''),'Mixed Sizes')

  WHENSFA.custom_5 IS NOT NULL AND dbo.Ufn_BackSize(SFA.custom_5) <> 'Mixed Sizes'THENISNULL(REPLACE(SFA.custom_5,' ' ,''),'Mixed Sizes')

  ELSEISNULL(REPLACE(SFA.commission_group,' ' ,''),'Mixed Sizes') END

  ,FilterColour=CASEWHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.colour + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.model_number + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.commission_group + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_1 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_2 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_3 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_4 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.custom_5 + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.product_name + ' ','/',' ')) lc)

  WHEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc) IS NOT NULL

  THEN(SELECT lc.ReturnString FROM dbo.UTfn_LikeColourTableFilter(REPLACE(SFA.[description] + ' ','/',' ')) lc)

  END

  ,FilterBrand=CASEWHENSFA.brand_name IS NOT NULLTHENSFA.brand_nameELSE'No Brand'END

  ,FilterSeller=CASEWHENSFA.merchant_name IS NOT NULLTHENSFA.merchant_nameELSE'No Store Name'END

  ,FilterProductTypeLevel1=''

  ,FilterProductTypeLevel2=''

  ,FilterProductTypeLevel3=''

  ,FilterProductTypeLevel4=''

  ,FilterProductTypeLevel5=''

  ,FilterProductTypeLevel6=''

  ,FilterProductTypeLevel7=''

  ,FilterProductTypeLevel8=''

  ,FilterProductTypeLevel9=''

  ,FilterProductTypeLevel10=''

  ,MerchantActiveInactive=ISNULL(S.MerchantActiveInactive,1)

  ,ClosedDownMerchantURL=''

  ,EtlDateInserted=@DateNow

  ,EtlDateUpdated=@DateNow

  ,EtlDescriptionLastWord=@WhereDescriptionEndsWith

  ,EtlProductNameLastWord=@WhereProdNameEndsWith

  ,EtlProcessedFlag=NULL

  ,EtlMasterLookupFilterID=@InsertSortOrder1

  ,EtlMasterLookupFilterSubID=@InsertSortOrder2

  ,EtlDescriptionFirstWord=@WhereDescriptionStartWith

  ,EtlProductNameFirstWord=@WhereProdNameStartWith

  FROM dbo.Staging_Feed_AffW SFA

  LEFT OUTER JOIN dbo.Dim_CategoryMain DCM

  ON @CategoryMainID = DCM.CategoryMainFeedID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),SFA.merchant_id) = S.SellerID

  WHERE

  SFA.EtlInsertedRows = 'NotSet'

  AND @InsertSortOrder2 = 14

  --AND CONTAINS((SFA.EtlDescriptionFirstWord), @WhereDescriptionStartWith)

  --AND CONTAINS((SFA.EtlDescriptionLastWord), @WhereDescriptionEndsWith)

  AND SFA.[description] LIKE '%' + @WhereDescriptionStartLike + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionEndsLike + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike1 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike2 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike3 + '%'

  AND SFA.[description] LIKE '%' + @WhereDescriptionLike4 + '%'

  ELSE

  SELECT 'Done'

  UPDATE dbo.Dim_MasterImportLookups

  SET

  UsedDate = @DateNow,

  FlagAsUsed = 1

  WHERE DataID= @DataID

  ------DECLARE @DateNow DATE = CONVERT(DATE,GETDATE())

  ----UPDATE dbo.Staging_Feed_AffW -- -- -- Old version based on SFA.merchant_product_id

  ----SET dbo.Staging_Feed_AffW.EtlInsertedRows ='Inserted'

  ----,dbo.Staging_Feed_AffW.EtlDateProcessed=@DateNow

  ----FROM dbo.Fact_Feed_Products NI

  ----LEFT OUTER JOIN dbo.Staging_Feed_AffW SFA

  ---- ON NI.AffiliateProductID = SFA.merchant_product_id

  ----WHERE SFA.merchant_product_id IS NOT NULL

  ----AND SFA.EtlInsertedRows = 'NotSet'

  SELECT

  NI.ProductName

  ,NI.[Description]

  ,ProdNameStartWith=@WhereProdNameStartWith

  ,ProdNameEndsWith=@WhereProdNameEndsWith

  ,ProdNameStartLike=@WhereProdNameStartLike

  ,ProdNameEndsLike=@WhereProdNameEndsLike

  ,ProdNameLike1=@WhereProdNameLike1

  ,ProdNameLike2=@WhereProdNameLike2

  ,ProdNameLike1=@WhereProdNameLike3

  ,ProdNameLike1=@WhereProdNameLike4

  ,DescriptionStartWith=@WhereDescriptionStartWith

  ,DescriptionEndsWith=@WhereDescriptionEndsWith

  ,DescriptionStartLike=@WhereDescriptionStartLike

  ,DescriptionEndsLike=@WhereDescriptionEndsLike

  ,DescriptionLike1=@WhereDescriptionLike1

  ,DescriptionLike2=@WhereDescriptionLike2

  ,DescriptionLike3=@WhereDescriptionLike3

  ,DescriptionLike4=@WhereDescriptionLike4

  FROM dbo.Fact_Feed_Products NI

  LEFT OUTER JOIN dbo.Staging_Feed_AffW SFA

  ON NI.AffiliateProductID = SFA.aw_product_id

  WHERE SFA.aw_product_id IS NOT NULL

  AND SFA.EtlInsertedRows = 'NotSet'

  -- -- -- --DECLARE @DateNow DATE = CONVERT(DATE,GETDATE())

  UPDATE dbo.Staging_Feed_AffW -- -- -- New version based on SFA.aw_product_id

  SET dbo.Staging_Feed_AffW.EtlInsertedRows ='Inserted'

  ,dbo.Staging_Feed_AffW.EtlDateProcessed=@DateNow

  FROM dbo.Fact_Feed_Products NI

  LEFT OUTER JOIN dbo.Staging_Feed_AffW SFA

  ON NI.AffiliateProductID = SFA.aw_product_id

  WHERE SFA.aw_product_id IS NOT NULL

  AND SFA.EtlInsertedRows = 'NotSet'

  -- -- remove closed store products

  UPDATE dbo.Fact_Feed_Products

  SET MerchantActiveInactive = 0

  ,ClosedDownMerchantURL = 'http://www.bra-shop.co.uk/closed-stores/'

  WHERE SkuID IN (

  (SELECT B.SkuID

  FROM dbo.Staging_Feed_AffW A

  LEFT OUTER JOIN dbo.Fact_Feed_Products B

  ON A.aw_product_id = B.AffiliateProductID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),A.merchant_id) = S.SellerID

  WHERE S.MerchantActiveInactive = 0

  AND B.SkuID IS NOT NULL

  AND B.MerchantActiveInactive = 1)

  )

  -- --Re open remove closed store products

  UPDATE dbo.Fact_Feed_Products

  SET MerchantActiveInactive = 1

  ,ClosedDownMerchantURL = ''

  WHERE SkuID IN (

  (SELECT B.SkuID

  FROM dbo.Staging_Feed_AffW A

  LEFT OUTER JOIN dbo.Fact_Feed_Products B

  ON A.aw_product_id = B.AffiliateProductID

  LEFT OUTER JOIN dbo.Dim_Seller S

  ON 'AW' + CONVERT(NVARCHAR(MAX),A.merchant_id) = S.SellerID

  WHERE S.MerchantActiveInactive = 1

  AND B.SkuID IS NOT NULL

  AND B.MerchantActiveInactive = 0)

  )

  SELECT @DataID

  END

  --G

• Northern Monkey

  Ten Centuries

  Points: 1017

  More actions

  August 4, 2016 at 6:57 am

  #1893754

  Hi Chris

  Many thanks this works like a treat

  I never stop learning in this game 🙂

  Also thanks to everyone else

  KR The Monkey

  ChrisM@Work (8/3/2016)

  ---

  Northern Monkey (8/3/2016)

  ---

  Also I would like to add a parameter to be passed from the case statement to the function to limit the type of product I have a column named TypeID INT in the dim_colour table and at the time of processing I would like to pass 1 or 2 or any other number depending on the type of product to give me more functionality 🙂

  Could that be done please? if so could you give examples of how as I have been trying to get this to work for the past few days 🙁

  S

  This whole process looks to me to be massively over-engineered. If you can provide table scripts and scripts to populate those tables with some sample data, we can write code against it. Something like this should do:

  SELECT sfa.*, rc.FilterColour

  FROM Whatever AS sfa

  OUTER APPLY (

  SELECT TOP 1

  FilterColour = cl.DisplayInFilter

  FROM dbo.Dim_Colour cl

  CROSS APPLY (

  SELECT * FROM (VALUES

  (1, sfa.Colour),

  (2, sfa.model_number) -- continue the list here

  ) x ([Priority], Colour)

  ) ls

  WHERE ls.Colour LIKE '%' + cl.LookupValue + '%'

  ORDER BY [Priority], LEN(cl.LookupValue)

  ) rc

  - and doesn't require much in the way of explanation or maintenance.

  Keep it simple 😉 Some bu88er's made this far harder for you than it should be.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 4, 2016 at 7:11 am

  #1893762

  Thanks for the generous feedback KR - do pop by if you need further assistance with this.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  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