Htykuut

I have this sql statement for making a view

create view RecordYearsTwo 

as

    select 

        Record.RecordID, RecordValue.Value 

    from 

        Record 

    join 

        RecordValue on Record.RecordID = RecordValue.RecordID

    where 

        len(RecordValue.Value) = 4 

        and RecordValue.Value like '[16-20][0-9][0-9][0-9]%'

        and RecordValue.Value like '%[16-20][0-9][0-9][0-9]'

        and RecordValue.Value != '26 Mar 1850';

When I then run

select * 

from Record

join RecordYearsTwo on Record.RecordID = RecordYearsTwo.RecordID 

where cast(RecordYearsTwo.Value as int) >= 1800

I get this error

Conversion failed when converting the nvarchar value '26 Mar 1850' to data type int.

My understanding is that '26 Mar 1850' shouldn't even exist in my view because the length of everything in my view should be 4 and I specifically said should not equal '26 Mar 1850'

Any ideas?

The criteria for that datestamp isn't needed.

Because even the first criteria wouldn't accept it (to long).

And those LIKE criteria don't need the % if only 4 characters are expected.

create view RecordYearsTwo as

select rec.RecordID, val.Value 

from Record rec

join RecordValue val on val.RecordID = rec.RecordID

where len(val.Value) = 4 

  and (val.Value like '1[6-9][0-9][0-9]' or val.Value like '20[0-9][0-9]')

And to avoid the error you could use TRY_CAST instead.

select * 

from Record

join RecordYearsTwo on Record.RecordID = RecordYearsTwo.RecordID 

where try_cast(RecordYearsTwo.Value as int) >= 1800

With multiple expressions in the where clause, you can't guarantee what order they get executed in. Try:

create view RecordYearsTwo as

select r.RecordID, v.Value 

from Record r

join (

    select *

    from RecordValue

    where len(Value) = 4 

) v on r.RecordID = v.RecordID

where v.Value like '[16-20][0-9][0-9][0-9]%'

    and v.Value like '%[16-20][0-9][0-9][0-9]'

First, your view conditions do not make sense. I think you want:

create view RecordYearsTwo as

    select r.RecordID, rv.Value 

    from Record r join

         RecordValue rv

         on r.RecordID = rv.RecordID

    where len(rv.Value) = 4 

          try_convert(int, rv.Value) >= 1600 and

          try_convert(int, rv.Value) < 2100;

Your logic doesn't make sense. I find this amusing: 'v.Value like '[16-20][0-9][0-9][0-9]%'. That like pattern says to get any '1', any character between '6' and '2' (which is none), and any '0'. I understand what you mean, but SQL Server does not.

Then, the view does not get executed first. You have no idea what the order of execution is, so for your query, you want try_convert() again:

select * 

from Record join r

     RecordYearsTwo ry2

     on r.RecordID = ry2.RecordID 

where try_convert(int, ry2.Value) >= 1800