Converting query from Non-Ansi to Ansi

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Converting query from Non-Ansi to Ansi

Author

Topic

gbnaveen
Starting Member

10 Posts

Posted - 2008-09-22 : 02:46:02

Hi,

below sql statement written using non-ansi style.
select isnull(customertemp.custid,customer.custid), isnull(customertemp.parentid,customer.parentid)

from customertemp, customer
where customertemp.custid =* customer.custid
and customer.custid = @currparentid

This is the converted query (using Ansi style)
select isnull(customertemp.custid,customer.custid), isnull(customertemp.parentid,customer.parentid)

from customertemp RIGHT OUTER JOIN customer on
customertemp.custid=customer.custid
where customer.custid = @currparentid

i would like to know how to define whether the "customer.custid = @currparentid" condition should be part of where condition or should be part of join?

Thanks in advance,

Thanks,
Naveen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 03:16:42

Since the comparison is with customer table field which is base table its more or less the same to put condition on where or along with join. But generally its better to put conditions on join clause in case of outer joins. also its beeter to use LEFT JOIN instead of right

select isnull(customertemp.custid,customer.custid), isnull(customertemp.parentid,customer.parentid)
from customer LEFT OUTER JOIN  customertemp  
on customertemp.custid=customer.custid
AND  customer.custid = @currparentid

gbnaveen
Starting Member

10 Posts

Posted - 2008-09-22 : 03:25:20

Hi visakh16, thanks for u r reply,

in which scenario the condition should be part of where clause?

thanks,
naveen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 03:49:54

quote:
Originally posted by gbnaveen

Hi visakh16, thanks for u r reply,

in which scenario the condition should be part of where clause?

thanks,
naveen

for inner join it can be put in where clause or with join. outer joins it should be placed it with join when rightside tables fields are used.if base tables fields are used it can be used anywhere

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-22 : 07:32:46

Also, ISNULL() is not ANSI standard. Use COALESCE() instead.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-22 : 16:34:03

Naveen,

I was not sure of your proficiency level with SQL, but here is a link to an example that I use about putting the condition in the JOIN or the WHERE clause that might help.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98481

It's the post I made on: 03/05/2008 : 15:02:08

Cheers!

gbnaveen
Starting Member

10 Posts

Posted - 2008-09-24 : 01:01:02

hi visakh,
i am getting different results with u r statement, i am putting u r statement here below for reference.
"Since the comparison is with customer table field which is base table its more or less the same to put condition on where or along with join. But generally its better to put conditions on join clause in case of outer joins. also its beeter to use LEFT JOIN instead of right."

-----------------------------------------------------------
Non ansi style query

select isnull(customertemp.custid,customer.custid), isnull(customertemp.parentid,customer.parentid)
from customertemp, customer where customertemp.custid =* customer.custid and customer.custid = @currparentid

converted query(ansi style)

select isnull(customertemp.custid,customer.custid), isnull(customertemp.parentid,customer.parentid)
from customertemp RIGHT OUTER JOIN customer on customertemp.custid=customer.custid where customer.custid = @currparentid
-----------------------------------------------------------

i am getitng exact results what i need if i put customer.custid = @currparentid in where clause.
i get all records from customer table if this condition as part of join..

my question is, what is the thumbrool to decide where to place this condition?

thanks in advance,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 01:34:03

quote:
Originally posted by gbnaveen

hi visakh,
i am getting different results with u r statement, i am putting u r statement here below for reference.
"Since the comparison is with customer table field which is base table its more or less the same to put condition on where or along with join. But generally its better to put conditions on join clause in case of outer joins. also its beeter to use LEFT JOIN instead of right."

-----------------------------------------------------------
Non ansi style query

select isnull(customertemp.custid,customer.custid), isnull(customertemp.parentid,customer.parentid)
from customertemp, customer where customertemp.custid =* customer.custid and customer.custid = @currparentid

converted query(ansi style)

select isnull(customertemp.custid,customer.custid), isnull(customertemp.parentid,customer.parentid)
from customertemp RIGHT OUTER JOIN customer on customertemp.custid=customer.custid where customer.custid = @currparentid
-----------------------------------------------------------

i am getitng exact results what i need if i put customer.custid = @currparentid in where clause.
i get all records from customer table if this condition as part of join..

my question is, what is the thumbrool to decide where to place this condition?

thanks in advance,

can you state your correct requirement.is it
1.Get all records from customer along with matching from customertemp based on condition customer.custid = @currparentid & customertemp.custid = customer.custid
or
2.Get all records from customer having customer.custid = @currparentid & get matching records from customertemp on customertemp.custid = customer.custid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources