SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 WHERE do I put the WHERE?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jesus4u
Posting Yak Master

USA
204 Posts

Posted - 02/28/2003 :  16:11:45  Show Profile  Reply with Quote
I am wanting to bring back only those records where the number of occurences of a period is = 0. But the where clause dones't work anywhere in the statement.


(SELECT T1.IP, datalength(T1.IP) - datalength(replace(T1.IP,'.','')) AS NUM
FROM StagingTable as T1
WHERE NUM = 0
INNER JOIN
(SELECT IP FROM StagingTable) as SUB1
ON T1.[IP]=SUB1.[IP])



robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 02/28/2003 :  16:14:03  Show Profile  Visit robvolk's Homepage  Reply with Quote
INNER JOIN is part of the FROM clause, and WHERE is the first clause that comes after FROM:

(SELECT T1.IP, datalength(T1.IP) - datalength(replace(T1.IP,'.','')) AS NUM
FROM StagingTable as T1
INNER JOIN
(SELECT IP FROM StagingTable) as SUB1
ON T1.[IP]=SUB1.[IP]
WHERE NUM = 0
)


Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/28/2003 :  16:15:27  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
The WHERE has to come after the join, and also you can't reference column ALIAS's, you need to repeat the entire condition.

SELECT T1.IP,
datalength(T1.IP) - datalength(replace(T1.IP,'.','')) AS NUM
FROM StagingTable as T1
INNER JOIN
(SELECT IP FROM StagingTable) as SUB1
ON T1.[IP]=SUB1.[IP]
WHERE datalength(T1.IP) - datalength(replace(T1.IP,'.','')) = 0


Or, you can use a subquery to do this, and in that case you CAN use the alias. Not sure which is more efficient.

SELECT * FROM
(
SELECT T1.IP,
datalength(T1.IP) - datalength(replace(T1.IP,'.','')) AS NUM
FROM StagingTable as T1
INNER JOIN
(SELECT IP FROM StagingTable) as SUB1
ON T1.[IP]=SUB1.[IP]

) A
WHERE NUM = 0

- Jeff
Go to Top of Page

jesus4u
Posting Yak Master

USA
204 Posts

Posted - 02/28/2003 :  16:15:34  Show Profile  Reply with Quote
I have tried that and it says Invalid column name NUM!

Go to Top of Page

jesus4u
Posting Yak Master

USA
204 Posts

Posted - 02/28/2003 :  16:16:49  Show Profile  Reply with Quote
quote:

The WHERE has to come after the join, and also you can't reference column ALIAS's, you need to repeat the entire condition.

SELECT T1.IP,
datalength(T1.IP) - datalength(replace(T1.IP,'.','')) AS NUM
FROM StagingTable as T1
INNER JOIN
(SELECT IP FROM StagingTable) as SUB1
ON T1.[IP]=SUB1.[IP]
WHERE datalength(T1.IP) - datalength(replace(T1.IP,'.','')) = 0


Or, you can use a subquery to do this, and in that case you CAN use the alias. Not sure which is more efficient.

SELECT * FROM
(
SELECT T1.IP,
datalength(T1.IP) - datalength(replace(T1.IP,'.','')) AS NUM
FROM StagingTable as T1
INNER JOIN
(SELECT IP FROM StagingTable) as SUB1
ON T1.[IP]=SUB1.[IP]

) A
WHERE NUM = 0

- Jeff



YES! Thank you for the education

Go to Top of Page

jesus4u
Posting Yak Master

USA
204 Posts

Posted - 02/28/2003 :  16:24:40  Show Profile  Reply with Quote
You guys might think I am stupid but I was hoping to use that result set and do a DELETE based on it. But it deletes ALL records not the records from the subquery. What do I do now?
Thanks

DELETE
FROM StagingTable
WHERE EXISTS
(SELECT T1.IP, DATALENGTH(T1.IP) - DATALENGTH(REPLACE(T1.IP, '.', '')) AS NUM, T1.id
FROM dbo.StagingTable T1 INNER JOIN
(SELECT IP
FROM StagingTable) SUB1 ON T1.IP = SUB1.IP
WHERE (DATALENGTH(T1.IP) - DATALENGTH(REPLACE(T1.IP, '.', '')) = 0))



Go to Top of Page

jesus4u
Posting Yak Master

USA
204 Posts

Posted - 02/28/2003 :  16:35:38  Show Profile  Reply with Quote
hlo?

Go to Top of Page

jesus4u
Posting Yak Master

USA
204 Posts

Posted - 02/28/2003 :  16:37:38  Show Profile  Reply with Quote
Believe it or not this seems to work!

DELETE StagingTable
WHERE DATALENGTH(IP) - DATALENGTH(REPLACE(IP, '.', '')) IN
(SELECT DATALENGTH(T1.IP) - DATALENGTH(REPLACE(T1.IP, '.', '')) AS NUM
FROM dbo.StagingTable T1 WHERE (DATALENGTH(T1.IP) - DATALENGTH(REPLACE(T1.IP, '.', '')) = 0))



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000