Need some help with my SQL Statement please 1

[grippy]

INSERT INTO Product_SpecificationAttribute_Mapping
(ProductId, SpecificationAttributeOptionId, AllowFiltering, ShowOnProductPage, DisplayOrder)

SELECT Product.Id AS ProductId, SpecificationAttributeOption.Id AS SpecificationAttributeOptionId, '1' AS AllowFiltering, '0' AS ShowOnProductPage, SpecificationAttributeOption.DisplayOrder AS DisplayOrder
FROM Product
INNER JOIN SpecificationAttributeOption ON ' '+Product.Name+' ' LIKE '% ' + SpecificationAttributeOption.Name +' %' 
WHERE SpecificationAttributeId = 7
ORDER BY SpecificationAttributeOption.Name ASC

Ok this is what the data looks like in SpecificationAttributeOption.Name

2002
2001
2000
1999
1998 and so on.

now my Product.Name data looks like:

blah blah Chevy Cavalier 99-03 4 Cyl

so I need the sql statement to trim SpecificationAttributeOption.Name to the last 2 digits and then find recognize the range of years and insert the Id's for 1999,2000,2001,2002 and 2003 all as separate rows for each product. Right now it works for finding the whole year in the product name. Any and all help is appreciated. Thank you!

 

[grippy]

also too some product names have cars like Saab 9000 so I don't want it to pull the 00 from 9000 thinking its a year 2000

 

[denimined]

Here are some of the pieces.

If SpecificationAttributeOption.Name is numeric, then use

Right(Convert(varchar(4), SpecificationAttributeOption.Name, 2)

If SpecificationAttributeOption.Name is character, then use

Right(SpecificationAttributeOption.Name, 2)

as the string parser.

That gets you the year as a two character string. Then, you would want to do something like [I used the 2nd string parser in this example]:

ON Product.Name LIKE '%-' + Right(SpecificationAttributeOption.Name, 2) + ' %'
OR Product.Name LIKE '% ' + Right(SpecificationAttributeOption.Name, 2) + '-%'

That should search through the <space>YY-YY<space> segments for either the <space>YY- or the -YY<space> pattern.

Hope that helps.

 

[grippy]

It is stored as nvarchar(max). Yes that code looks good but there is one thing missing that I need it to do. The SQL Statement will pick out the 99 and the 03 from 99-03 however I need to have it somehow recognize that it needs to do it for 00 and 01 and 02 as well since it is a range of years. So if someone on the site selects the year 2000 from my dropdown it wont find that product unless they select 99 or 03. Any ideas on that part?

Thanks a bunch for the help! Ive been racking my brain on this one.

 

[denimined]

OK - Here we go.........

The comments should explain what is going on, but basically I the pulled the year range from the product and then reversed the join logic on the specifications so that I could test against the inferred year range.
There is *BIG* assumption that there is only one dash in each description and that it is wrapped by 2 digit years. If in fact there may be more, but the year range is always near the end, then you could change the
[tt]CHARINDEX('-', name)[/tt]
into something like
[tt]CHARINDEX('-', name, len(rtrim(name))-10)[/tt]

I think that I have the century logic right, but I didn't have anything to actually test it on. Note that when the year range doesn't cross the century, both ranges end up the same, so no harm, no foul.

Finally, I joined on PRODUCT.name. However, if PRODUCT has a key field, then include that in the column lists through all the sub-queries and then use the key field on the rejoin to PRODUCT for better performance.

[tt]

-- Get product with evaluated previous century / current century ranges.
join (
      select name, 
             ymin as pcmin,       
             case when ymax < ymin then 99 else ymax end as pcmax,
             case when ymax < ymin then  0 else ymin end as ccmin,
             ymax as ccmax
      from (       
            -- Product name with year entries as min / max.
            select name, 
                   convert(int, SUBSTRING(name, loc-2, 2)) as ymin,
                   convert(int, SUBSTRING(name, loc+1, 2)) as ymax
            from (       
                  -- Pick those entries where '-' is in the form _XX-YY_
                  select name, 
                         loc
                  from (
                        -- Find the location of the all important '-'.
                        select name,
                               CHARINDEX('-', name) as loc
                        from PRODUCT
                        where CHARINDEX('-', name) > 0
                       ) as tbl
                  where SUBSTRING(name, loc-3, 1) = ' ' 
                    and SUBSTRING(name, loc+3, 1) = ' '
                 ) as rng
            where convert(int, SUBSTRING(name, loc-2,2)) > 0
              and convert(int, SUBSTRING(name, loc+1,2)) > 0
           ) as yrs
     ) as pdrng
on pdrng.name = product.name
-- Join the specification where the spec is between the computed product ranges.
join SpecificationAttributeOption as sao       
ON convert(int, Right(sao.Name, 2)) between pdrng.pcmin and pdrng.pcmax
OR convert(int, Right(sao.Name, 2)) between pdrng.ccmin and pdrng.ccmax

[/tt]

Enjoy!

 

[grippy]

Ok this is way over my head lol. To answer your questions there should only be one dash in every product for the year range. The table Product does have an key field which is Product.Id

I copied the sql statement into sql server and before I could execute it there were a few things underlined in red. I'm not very good with complex statements like this so I am not sure what to change to make it work.

The first one was: join ( (incorrect syntax near 'join')
Second was the as in: ) as pdrng (incorrect syntax near 'as')
Third was int and sao: ON convert(int, Right(sao.Name, 2)) (incorrect syntax near 'int'. Expecting '(', or SELECT.)
Forth was int and sao: OR convert(int, Right(sao.Name, 2)) (incorrect syntax near 'int'. Expecting '(', or SELECT.)

Also how do I implement the inserting code from the first post? Performance is not too big of an issue since I will just be manually executing the SQL statement once I add new products. Thank you for all of your help you have gone above and beyond I hope I can get this working.

 

[denimined]

OK, try this as a full statement. (I left the INSERT section out as it will allow for easier testing.)

SELECT PD.Id AS ProductId, 
       sao.Id AS SpecificationAttributeOptionId, 
       '1' AS AllowFiltering, 
       '0' AS ShowOnProductPage, 
       sao.DisplayOrder AS DisplayOrder
FROM Product as PD
join (
      -- Get product with evaluated previous century / current century ranges.
      select id, 
             name, 
             ymin as pcmin,       
             case when ymax < ymin then 99 else ymax end as pcmax,
             case when ymax < ymin then  0 else ymin end as ccmin,
             ymax as ccmax
      from (       
            -- Product name with year entries as min / max.
            select id, 
                   name, 
                   convert(int, SUBSTRING(name, loc-2, 2)) as ymin,
                   convert(int, SUBSTRING(name, loc+1, 2)) as ymax
            from (       
                  -- Pick those entries where '-' is in the form _XX-YY_
                  select id, 
                         name, 
                         loc
                  from (
                        -- Find the location of the all important '-'.
                        select id, 
                               name,
                               CHARINDEX('-', name) as loc
                        from PRODUCT
                        where CHARINDEX('-', name) > 0
                       ) as tbl
                  where SUBSTRING(name, loc-3, 1) = ' ' 
                    and SUBSTRING(name, loc+3, 1) = ' '
                 ) as rng
            where convert(int, SUBSTRING(name, loc-2,2)) > 0
              and convert(int, SUBSTRING(name, loc+1,2)) > 0
           ) as yrs
     ) as PdRng
on PdRng.id = PD.id
-- Join the specification where the spec is between the computed product ranges.
join SpecificationAttributeOption as sao       
ON convert(int, Right(sao.Name, 2)) between PdRng.pcmin and PdRng.pcmax
OR convert(int, Right(sao.Name, 2)) between PdRng.ccmin and PdRng.ccmax

I would suggest that you 'prove' each inner nested section before you try the whole.
e.g.

                        -- Find the location of the all important '-'.
                        select id, 
                               name,
                               CHARINDEX('-', name) as loc
                        from PRODUCT
                        where CHARINDEX('-', name) > 0

 

[grippy]

Alright I removed all the extra dashes from the database so the only occurrence would be in the year form i.e. 99-04. I ran the select statement but I am receiving this error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'SX' to data type int.

I looked at every instance of 'SX' in my DB and didn't see anything that looked wrong. My only guess would be maybe the car models that have SX in them such as Nissan 200SX or 300ZX or 240SX also I found Suzuki SX4 and tried temporarily renaming it to SX 4 since there were only two products and I re ran the sql statement and received the same error message. Let me know what your thoughts are on this.
Thank you,

Russ

 

[denimined]

Run this and then inspect the results, looking for a '>SX<' entry. That will be the problem row(s).
Once we know that, we can strengthen the WHERE filter to fix the problem.

-- Product name with year entries as min / max.
select id, 
       name, 
       '>' + SUBSTRING(name, loc-2, 2)) + '<' as ymin,
       '>' + SUBSTRING(name, loc+1, 2)) + '<' as ymax
from (       
      -- Pick those entries where '-' is in the form _XX-YY_
      select id, 
             name, 
             loc
      from (
            -- Find the location of the all important '-'.
            select id, 
                   name,
                   CHARINDEX('-', name) as loc
            from PRODUCT
            where CHARINDEX('-', name) > 0
           ) as tbl
      where SUBSTRING(name, loc-3, 1) = ' ' 
        and SUBSTRING(name, loc+3, 1) = ' '
     ) as rng

 

[grippy]

That statement has a few errors. Id and Name are underlined from the first Select statement saying invalid column name. Line two name, loc is underlined. Line three just name is underlined. and the very last line with ) as rng the as is underlined.

 

[denimined]

Lets do it by layers then.

This should return the ID and NAME fields from the PRODUCT table along with the location of the '-'.
If ID and NAME are not the correct attribute names from the PRODUCT table, change all occurrences to the correct form.

-- Find the location of the all important '-'.
select id, 
       name,
       CHARINDEX('-', name) as loc
from PRODUCT
where CHARINDEX('-', name) > 0

This simply sub-selects from the previous query, thinning to where the is a space 3 characters before and 3 characters after the location of the dash.

-- Pick those entries where '-' is in the form _XX-YY_
select id, 
       name, 
       loc
from (
      -- Find the location of the all important '-'.
      select id, 
                   name,
                   CHARINDEX('-', name) as loc
      from PRODUCT
      where CHARINDEX('-', name) > 0
     ) as tbl
where SUBSTRING(name, loc-3, 1) = ' ' 
  and SUBSTRING(name, loc+3, 1) = ' '

This is the previous code sent, used to determine what rows will not convert the XX or the YY of the string _XX-YY_ into integers.

-- Product name with year entries as min / max.
select id, 
       name, 
       '>' + SUBSTRING(name, loc-2, 2)) + '<' as ymin,
       '>' + SUBSTRING(name, loc+1, 2)) + '<' as ymax
from (       
      -- Pick those entries where '-' is in the form _XX-YY_
      select id, 
             name, 
             loc
      from (
            -- Find the location of the all important '-'.
            select id, 
                   name,
                   CHARINDEX('-', name) as loc
            from PRODUCT
            where CHARINDEX('-', name) > 0
           ) as tbl
      where SUBSTRING(name, loc-3, 1) = ' ' 
        and SUBSTRING(name, loc+3, 1) = ' '
     ) as rng

 

[grippy]

Ok I figured out the problem. It was the double )) in the third statement which just needed to be one ). It returned results that look like:

id, name, ymin, ymax
53842, blah blah car 85-90 Chevy blah blah, >85<, >90<

So it looks like we are on the right track here! I scrolled through and can not see any issues with it. Im getting excited to see this is working finally. So what is the next step?

 

[denimined]

Add this WHERE statement to the end of the test code.
(After the: as rng clause)

where not ( 
           (SUBSTRING(name, loc-2, 1) >= '0')
       and (SUBSTRING(name, loc-2, 1) <= '9')
          ) 
   or not ( 
           (SUBSTRING(name, loc+2, 1) >= '0')
       and (SUBSTRING(name, loc+2, 1) <= '9')
          )

Any results returned will be problem rows.

 

[grippy]

just ran it with no results looks like we have no problems

 

[denimined]

OK, but didn't this fail? (The red part)

SELECT PD.Id  AS ProductId, 
       sao.Id AS SpecificationAttributeOptionId, 
       '1'    AS AllowFiltering, 
       '0'    AS ShowOnProductPage, 
       sao.Dis[highlight #CC0000][/highlight]playOrder AS DisplayOrder
FROM Product as PD
join (
      -- Get product with evaluated previous century / current century ranges.
      select id, 
             name, 
             ymin as pcmin,       
             case when ymax < ymin then 99 else ymax end as pcmax,
             case when ymax < ymin then  0 else ymin end as ccmin,
             ymax as ccmax
      from (       
            -- Product name with year entries as min / max.
            select id, 
                   name, 
[b][COLOR=#CC0000]                   convert(int, SUBSTRING(name, loc-2, 2)) as ymin,
                   convert(int, SUBSTRING(name, loc+1, 2)) as ymax[/color][/b]
            from (       
                  -- Pick those entries where '-' is in the form _XX-YY_
                  select id, 
                         name, 
                         loc
                  from (
                        -- Find the location of the all important '-'.
                        select id, 
                               name,
                               CHARINDEX('-', name) as loc
                        from PRODUCT
                        where CHARINDEX('-', name) > 0
                       ) as tbl
                  where SUBSTRING(name, loc-3, 1) = ' ' 
                    and SUBSTRING(name, loc+3, 1) = ' '
                 ) as rng
            where convert(int, SUBSTRING(name, loc-2,2)) > 0
              and convert(int, SUBSTRING(name, loc+1,2)) > 0
           ) as yrs
     ) as PdRng
on PdRng.id = PD.id
-- Join the specification where the spec is between the computed product ranges.
join SpecificationAttributeOption as sao       
ON convert(int, Right(sao.Name, 2)) between PdRng.pcmin and PdRng.pcmax
OR convert(int, Right(sao.Name, 2)) between PdRng.ccmin and PdRng.ccmax

 

[denimined]

DOH!

Run this - looking for bad data in the NAME attribute.

select *
from SpecificationAttributeOption 
where not ( 
           (right(name,2) >= '0')
       and (right(name,2) <= '9')
          )

What needs to be done to filter the SpecificationAttributeOption table for the correct rows?

 

[grippy]

When I run that whole script the syntax is good but once executed it fails with this error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'SX' to data type int.

 

[grippy]

Ok I see what its doing. In the Name Attribute it lists everything i.e.

1999
1998
Toyota
Tercel
240SX
Nissan
2000
2001

Since the design of the site just applies the attribute id to the products. I guess we need some sort of WHERE clause that finds only years from 1900-2099. I would just say 4 digit numbers but there are cars such as Saab 9000.

 

[grippy]

With this script it shows all the problem cars as I listed above however it shows 1990, 91,92,93,94,95,96,97,98,99 for some reason? All the years in the attribute name are in four digit format but there are over 250 conflicts with car models that have numbers in them.

select *
from SpecificationAttributeOption 
where not ( 
           (right(name,2) >= '0')
       and (right(name,2) <= '9')
          )

 

[denimined]

OK - change the JOIN to this and you should be golden.

-- Join the specification where the spec is between the computed product ranges.
join (
      -- Only want the entries that are 4 digit years.
      select *
      from SpecificationAttributeOption 
      where isnumeric(Name) > 0
        and len(rtrim(Name)) = 4
     ) as sao       
ON convert(int, Right(sao.Name, 2)) between PdRng.pcmin and PdRng.pcmax
OR convert(int, Right(sao.Name, 2)) between PdRng.ccmin and PdRng.ccmax