| Author |
 Topic  |
|
|
jesus4u
Posting Yak Master
USA
204 Posts |
 Posted - 02/28/2003 : 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
USA
15557 Posts |
Posted - 02/28/2003 : 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
USA
7423 Posts |
Posted - 02/28/2003 : 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
USA
204 Posts |
Posted - 02/28/2003 : 16:15:34
|
I have tried that and it says Invalid column name NUM!
|
|
|
|
jesus4u
Posting Yak Master
USA
204 Posts |
Posted - 02/28/2003 : 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
USA
204 Posts |
Posted - 02/28/2003 : 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
USA
204 Posts |
Posted - 02/28/2003 : 16:35:38
|
hlo?
|
|
|
|
jesus4u
Posting Yak Master
USA
204 Posts |
Posted - 02/28/2003 : 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))
|
|
|
| |
Topic |
|
WHERE do I put the WHERE?
