Where to put ISNULL

[Trev]

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]

"Trev@Work" <no.email@please> wrote in message
news:41***********************@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

Simon

[Trev]

Simon Hayes wrote:

"Trev@Work" <no.email@please> wrote in message
news:41***********************@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

When I said "select" that was just an example, what I'm doing (or
wanting to do) is to delete rows that haven't changed since the last
import so I will be left with the delta. The left join is there because
the imported row may be an addition so won't exist in the main table
yet. I used Rev as an example column, in reality this should not be null
(not if the CAD people know how to use CAD <g>).

So I assume for efficiency's sake, it wouldn't matter where to put the
ISNULL(), although the view I'm creating is just for this one purpose I
shall probably keep the nulls in there JIC of any future use to make it
more flexible.