WHERE do I put the WHERE? - SQL Server Forums

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)

WHERE do I put the WHERE?

Author

Topic

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-02-28 : 16:11:45

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

15732 Posts

Posted - 2003-02-28 : 16:14:03

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
)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-28 : 16:15:27

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

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-02-28 : 16:15:34

I have tried that and it says Invalid column name NUM!

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-02-28 : 16:16:49

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

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-02-28 : 16:24:40

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))

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-02-28 : 16:35:38

hlo?

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-02-28 : 16:37:38

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))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources