select statment confusion - 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

General SQL Server Forums

New to SQL Server Programming

select statment confusion

Author

Topic

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-06-09 : 10:44:02

hello all i have a remote table and a local table i want to select 3 columns from them joining each other on conditions the column named node number must be equal , status not equal and the number of rows for the certain node number is greater than 1..i wrote this query

select project_plan.[node number] as Node, f1.status as noria_status, project_plan.status ,count(project_plan.[node number]) as count_1 from remote_list_view() f1 join project_plan on project_plan.[node number]=f1.[node code] and project_plan.status<> f1.status and count(project_plan.[node number])>2

but it is not working now f1 is the remote table(function calling it) and project_plan is the local table please help

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-09 : 11:34:52

What is the error you are receiving? It would help if you give us sample data and expected output.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 11:59:40

i think you want to group on some field without which you count(project_plan.[node number])>2 doesnt make any sense. probably you can explain what you're trying to achieve by means of some sample data and output

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-06-11 : 02:24:41

i want rows for which the node number apears more than once ...and i got this error
"An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference".

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-11 : 02:36:24

is this u want
select p.[node number] as Node,
f1.status as noria_status, p.status ,count_1
from remote_list_view() f1
join (select [node number],[status],count(p.[node number]) OVER(PARTITION BY p.{node number]) as count_1
from project_plan ) p on p.[node number]=f1.[node code] and p.status<> f1.status AND count_1 > 2

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-06-11 : 02:58:19

well thank u for ur quoick reply i got this error" Incorrect syntax near the keyword 'OVER'." and i dont know what is its cause

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-11 : 03:00:21

select p.[node number] as Node,
f1.status as noria_status, p.status ,count_1
from remote_list_view() f1
join (select [node number],[status],count([node number]) OVER(PARTITION BY [node number]) as count_1
from project_plan ) p on p.[node number]=f1.[node code] and p.status<> f1.status AND count_1 > 2

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-06-11 : 03:14:44

same error[:S]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-11 : 03:22:06

r u using sql 2000 or 2005 version ?

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-06-11 : 03:23:58

i have sql server 2005 but i am connecting to a sql server 2000 remote data base

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-11 : 03:26:30

in 2000 over partition will not work it work in only 2005 and above versions

try this in 2000
select p.[node number] as Node,
f1.status as noria_status, p.status ,count_1
from remote_list_view() f1
join (select [node number],count([node number]) as count_1
from project_plan group by [node number]) p on p.[node number]=f1.[node code] AND count_1 > 2
join
project_plan pp ON pp.[node number]=p.[node number] AND pp.status<> f1.status

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources