Correlated Subquery

Hi,
Correlated subquery is written in the following syntax in Oracle.
Select Ename,Sal from Employee X where 5= (Select count(distinct(sal)) from
Emplyee where sal>=X.sal)
How can this be written in SQL Server?
Please throw some light.
Thanks,
Su ManIn SQL server you use HAVING clause. For more information and the syntx
please refer to Books Online available.
thanks and regards
Chandra
"Su Man" wrote:

> Hi,
> Correlated subquery is written in the following syntax in Oracle.
> Select Ename,Sal from Employee X where 5= (Select count(distinct(sal)) fro
m
> Emplyee where sal>=X.sal)
> How can this be written in SQL Server?
> Please throw some light.
> Thanks,
> Su Man
>
>|||Your query is valid in SQL server also
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Su Man" <subu501@.yahoo.com> wrote in message
news:d4sl39$eqi$1@.news.mch.sbs.de...
> Hi,
> Correlated subquery is written in the following syntax in Oracle.
> Select Ename,Sal from Employee X where 5= (Select count(distinct(sal))
> from
> Emplyee where sal>=X.sal)
> How can this be written in SQL Server?
> Please throw some light.
> Thanks,
> Su Man
>|||The query you posted is valid in SQL Server. This is Standard SQL syntax.
Did you try it?
David Portas
SQL Server MVP
--|||It is working. Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gKOdneGg3IlLQOzfRVn-tA@.giganews.com...
> The query you posted is valid in SQL Server. This is Standard SQL syntax.
> Did you try it?
> --
> David Portas
> SQL Server MVP
> --
>

Copying data between databases with different collations

I have written a system which I now want to intergrate to some degree
with with Syspro accounting software, both run off MS SQL databases on
the same server.
I am writing a trigger on the syspro database,
I need to join across the 2 DBs, each have different collations
When I try:
Insert INTO DB1.dbo.Table1 (F1,F2.....)
Select (View1.Field1, View1.Field2.....)
>From DB2.dbo.View1 where [condition]
I get the following error "Cannot resolve collation conflict for
replace operation."
is there some way to convert the values from the view to allow them to
be inserted?
Syspro database is using : SQL_Latin1_General_CP437_BIN
And mine is using : SQL_Latin1_General_CP1_CI_ASHave a look at Collate database_default in SQL Books Online
Jump To URL :- tsqlref.chm::/ts_ca-co_5z55.htm
HTH. Ryan
<cameron.waldron@.gmail.com> wrote in message
news:1139489197.088750.109310@.g44g2000cwa.googlegroups.com...
>I have written a system which I now want to intergrate to some degree
> with with Syspro accounting software, both run off MS SQL databases on
> the same server.
> I am writing a trigger on the syspro database,
> I need to join across the 2 DBs, each have different collations
> When I try:
> Insert INTO DB1.dbo.Table1 (F1,F2.....)
> Select (View1.Field1, View1.Field2.....)
> I get the following error "Cannot resolve collation conflict for
> replace operation."
> is there some way to convert the values from the view to allow them to
> be inserted?
> Syspro database is using : SQL_Latin1_General_CP437_BIN
> And mine is using : SQL_Latin1_General_CP1_CI_AS
>|||Thanks, the problem was that I was in the view I was querying on my
database (it had a replace statement in it which was the problem, hence
the "Cannot resolve collation conflict for replace operation." Error) I
brought the view from my db to the syspro db and collated fields in the
view as I needed with collate database_default, problem fixed :-)