"Trev@Work" <no.email@please> wrote in message
news:416e63f4$0$27136$afc38c87@news.easynet.co.uk...
>I have two tables, tblMTO and tblIMPORT_MTO. If I import an entire MTO into
>the import table I want to create a delta from it (i.e. leave only the
>changed items). I have a view (simplified)
>
> SELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,
> dbo.tblIMPORT_MTO.Rev AS New_Rev, dbo.tblMTO.Rev AS Old_Rev
> FROM dbo.tblIMPORT_MTO LEFT OUTER JOIN
> dbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONo
>
> Now to get all rows where old_rev = new_rev I also want all rows where
> both are null, is the best method to put ISNULL() in the view or to select
> from the view using ISNULL in the criteria, e.g.
>
> select * from view1 where ISNULL(Old_Rev,0)=ISNULL(new_rev,0)
>
> or in the view
>
> CREATE VIEW view1 as
> SELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,
> ISNULL(dbo.tblIMPORT_MTO.Rev,0) AS New_Rev, ISNULL(dbo.tblMTO.Rev AS
> Old_Rev,0)
> FROM dbo.tblIMPORT_MTO LEFT OUTER JOIN
> dbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONo;
>
> select * from view1 where Old_Rev=new_rev;

Simon Hayes wrote:
> "Trev@Work" <no.email@please> wrote in message
> news:416e63f4$0$27136$afc38c87@news.easynet.co.uk...
>
>>I have two tables, tblMTO and tblIMPORT_MTO. If I import an entire MTO into
>>the import table I want to create a delta from it (i.e. leave only the
>>changed items). I have a view (simplified)
>>
>>SELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,
>>dbo.tblIMPORT_MTO.Rev AS New_Rev, dbo.tblMTO.Rev AS Old_Rev
>>FROM dbo.tblIMPORT_MTO LEFT OUTER JOIN
>> dbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONo
>>
>>Now to get all rows where old_rev = new_rev I also want all rows where
>>both are null, is the best method to put ISNULL() in the view or to select
>>from the view using ISNULL in the criteria, e.g.
>>
>>select * from view1 where ISNULL(Old_Rev,0)=ISNULL(new_rev,0)
>>
>>or in the view
>>
>>CREATE VIEW view1 as
>>SELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,
>>ISNULL(dbo.tblIMPORT_MTO.Rev,0) AS New_Rev, ISNULL(dbo.tblMTO.Rev AS
>>Old_Rev,0)
>>FROM dbo.tblIMPORT_MTO LEFT OUTER JOIN
>> dbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONo;
>>
>>select * from view1 where Old_Rev=new_rev;
>
>
> You could do either - putting ISNULL() in the view is more convenient if
> you'll use the same query a lot, but you might find it's better to put it in
> the query if that means you can use the same view for other queries more
> easily. Or just create two views, of course.
>
> Having said that, I don't really understand what you're doing - if you want
> to find where old_rev is the same as new_rev, that suggests you're looking
> for those rows which have the same value in both tables (although 'delta'
> seems to suggest the opposite). If so, then why do you need an outer join?
> This affects why dbo.tblMTO.Rev is NULL - it could either be a NULL in the
> data (which you would want to see), or a NULL because there's no matching
> row in dbo.tblMTO (which you wouldn't).
>
> It's very possible that I've misunderstood what you're doing, so if this
> doesn't help, I suggest you post CREATE TABLE and INSERT statements to set
> up some sample data, along with the results you want to see in your view.
> That way others can cut and paste into QA, and we don't have to guess about
> keys, data types, NULLable columns etc.
>
> http://www.aspfaq.com/etiquette.asp?id=5006