Alright, what's going on here....? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

Alright, what's going on here....?

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2003-07-07 : 11:26:31

I have the following code


SELECT sc.scid, sc.scname, st.StateLong, count(us.[user_id]) as [total_users] FROM 
	smc_new_products.dbo.SupportCenters sc
left join 
	smc_new_products.dbo.supportcenterstates scs 
		on sc.scid=scs.scid
left join 
	smc_new_products.dbo.States st
		on scs.stateid=st.stateid
left join 
	smc_new_products.dbo.usr_smc us
		on 
			substring(convert(varchar(4), sc.phone), 2, 3) = left(3, convert(varchar(4), us.phone))
			and 
				us.country='United States' 
			and 
				len(us.phone)>8 
WHERE Branch='1' 
group by sc.scid, sc.scname, statelong
ORDER BY SCName

..................................................

Fairly straight-forward stuff here, right?

So, the error I'm getting is this:


Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '714-' to a column of data type int.

.................................................

The fields that I believe are involved are these:

usr_smc.phone (nvarchar(64), Null)

and

SupportCenters.phone (varchar(50), Null)

I'm not trying to convert anything into an "int" anywhere in this query so I have less than no idea why it's throwing this error.....

Any ideas please?

macka
Posting Yak Master

162 Posts

Posted - 2003-07-07 : 11:28:15

Looks like youve got the arguments for LEFT the wrong way round:

left(3, convert(varchar(4), us.phone))

should be:

left(convert(varchar(4), us.phone),3)

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 11:42:29

Absolutley

left(3, convert(varchar(4), us.phone))

SQL is trying to convert us.phone into an integer which is the required arg for LEFT...

I'm more concerned withis:


and 
				us.country='United States' 
			and 
				len(us.phone)>8

In the Join clause...

I know there are issues as to whether this is in the predicate or the join because I know they can cause different results..

Just don't remeber why though..

Brett

8-)

label
Posting Yak Master

197 Posts

Posted - 2003-07-07 : 11:45:34

quote:

Absolutley

left(3, convert(varchar(4), us.phone))

SQL is trying to convert us.phone into an integer which is the required arg for LEFT...

*smacks forhead in disgust.

hehe. I knew I was overlooking something obvious....thanks!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-07 : 11:47:56

Brett -- in a LEFT OUTER JOIN you want all criteria on the outer table to be part of the join clause (as in his example), otherwise it turns the OUTER JOIN into an INNER JOIN.

i.e.,

select a.*
from
a
left outer join b
on
a.id = b.id
where
b.name = 'Jeff'

That completely negates the OUTER JOIN because b.name is not allowed to be Null.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 12:09:40

Isn't that the other way around?


USE NorthWind
GO

INSERT INTO Orders (CustomerId) SELECT 'VINET'
GO
 
SELECT * FROM Orders l
LEFT JOIN [Order Details] r
ON l.OrderId = r.OrderId 
WHERE r.OrderId Is Null AND l.CustomerId = 'VINET'

SELECT * FROM Orders l
LEFT JOIN [Order Details] r
ON l.OrderId = r.OrderId AND l.CustomerId = 'VINET' AND r.OrderId Is Null
GO

DELETE FROM Orders WHERE EmployeeId Is Null
GO

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-07 : 12:56:26

Your example has nothing to do with mine. you are using the OUTER JOIN to determine which orders have no details for a particular customer. That's fine.

My example was joing from table A to table B, but only certain rows in table B (where B.name = 'Jeff'). That says: return ALL rows from table A, and matching rows where b.name ='Jeff' from table B.

if you put the criteria in the WHERE clause, it becomes: return only rows from table A that have a matching row in table B where the b.name = 'Jeff'.

I hope this makes some sense.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 13:50:27

Got it...

Why would you want to use a outter join in this manner?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-07 : 14:00:56

YOu have a table of customers and a table of CustomerAddresses:

Customers
--------
CustNo
CustName
... etc...

CustAddresses
---------
CustNo
AddrType
Address1
Address2
City
State
ZIP
..etc...

you can have multiple addresses per customer, 1 for each address type. AddressTypes might be:

Billing
Mailing
Contact1
..etc...

Now, write me this query:

Return *all* customers and their Billing Address, if any (not all customer's might have a billing address).

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-07 : 16:25:07

Brett -- did my example not help answer your question?

In case you were confused, here's the answer:



select 
  c.CustNo, c.CustName, a.address1, a.address2, ...etc..
from
  customers c
left outer join
  custAddresses a
on
  c.custNo = a.CustNo  and
  a.AddrType = 'Billing'

if you take the bold part OUT of the join clause in put it in a WHERE
clause, the query does not return a row for ALL customers, assuming some
don't have billing addresses as stated.

the alternate method:



select 
  c.CustNo, c.CustName, a.address1, a.address2, ...etc..
from
  customers c
left outer join
  (select * from custAddresses where AddrType = 'Billing') a
on
  c.custNo = a.CustNo

That'll work as well, but requires a subquery.

- Jeff

Edited by - jsmith8858 on 07/07/2003 17:26:43

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 16:30:12

No I got...absolutley...

Just hasn't been a good day, (as you can attest to my post latley).

I think I'll have to rebuild a 7.0 box that just got drop in my lap...they built it with collation 53...almost out of the box...I believe Tara mentioned 52 is...which it looks like..

Thanks for the example....and as the French would say...

But, Of course...

You could even get home and billing and use a case statement...

Brett

8-)

Edited by - x002548 on 07/07/2003 16:34:21

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources