multi lingual where clause

I have a table with nvarchar column. In query analyzer when I run query with foreign language words that are already there in database it dose not return any rows

To test it I returned rows with Select * table. Then from result window I copy chinese characters and put it in SQL where clause in SQL analyzer .When I run query it dose not return results.

What could be the cause..its SQL 2k

found it add N infront of characters|||

As kyus94 indicated, to use UNICODE characters, including Chinese, you must preface the string with the character [ N ].

For example,

SELECT

Column1,

Column2,

etc

FROM MyTable

WHERE Column3 = N'ThisCouldBeChinese'

(Note the character immediately before (no space) the string.)

sql

multi column subquery using IN clause

Is there a way to do a multi column subquery using a IN clause?
Example of what works for Oracle and needs to get it to work for SQL Server
2000:
SELECT *
FROM Test T
WHERE (T.Key1, T.Key2) IN
(SELECT S.Key1, S.Key2 FROM SubSelect S WHERE S.End_Date IS
NULL)
I would like to keep this a prepared statement if possible.
Thanks in advance, KevinUse EXISTS:
SELECT *
FROM Test T
WHERE EXISTS
(SELECT *
FROM SubSelect S
WHERE S.End_Date IS NULL
AND S.Key1 = T.Key1
AND S.Key2 = T.Key2)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Kevin Shephard" <Kevin Shephard@.discussions.microsoft.com> wrote in message
news:DF8C2250-9861-4D50-B6AA-7EC5A806F46C@.microsoft.com...
> Is there a way to do a multi column subquery using a IN clause?
> Example of what works for Oracle and needs to get it to work for SQL
> Server
> 2000:
> SELECT *
> FROM Test T
> WHERE (T.Key1, T.Key2) IN
> (SELECT S.Key1, S.Key2 FROM SubSelect S WHERE S.End_Date IS
> NULL)
> I would like to keep this a prepared statement if possible.
> Thanks in advance, Kevin|||n columns = n conditions
:)
Try using this function (several times in the same query in as many joins as
you need):
http://solidqualitylearning.com/Blo.../10/22/200.aspx
E.g. four columns => four variables => four instances of the function in
four joins
ML|||Why not just do a JOIN?
SELECT T.<col1>, T.<col2>, ...
FROM Test T
INNER JOIN SubSelect S
ON T.Key1 = S.Key1
AND T.Key2 = S.Key2
WHERE S.End_Date IS NULL
Tom's EXISTS works also, but the above truly represents what you're doing...
seems to me the most intuitive approach to the query. Try to avoid SELECT *
in production code, FWIW.
"Kevin Shephard" <Kevin Shephard@.discussions.microsoft.com> wrote in message
news:DF8C2250-9861-4D50-B6AA-7EC5A806F46C@.microsoft.com...
> Is there a way to do a multi column subquery using a IN clause?
> Example of what works for Oracle and needs to get it to work for SQL
> Server
> 2000:
> SELECT *
> FROM Test T
> WHERE (T.Key1, T.Key2) IN
> (SELECT S.Key1, S.Key2 FROM SubSelect S WHERE S.End_Date IS
> NULL)
> I would like to keep this a prepared statement if possible.
> Thanks in advance, Kevin|||On Mon, 31 Oct 2005 12:23:19 -0500, Aaron Bertrand [SQL Server MVP]
wrote:

>Why not just do a JOIN?
>SELECT T.<col1>, T.<col2>, ...
> FROM Test T
> INNER JOIN SubSelect S
> ON T.Key1 = S.Key1
> AND T.Key2 = S.Key2
> WHERE S.End_Date IS NULL
>Tom's EXISTS works also, but the above truly represents what you're doing..
.
>seems to me the most intuitive approach to the query.
Hi Aaron,
If the relationship between Test and Subselect is one to many (with
Subselect being the many), the join will introduce duplicates that are
avoided with Tom's EXISTS.

>Try to avoid SELECT *
>in production code, FWIW.
Amen to that!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> If the relationship between Test and Subselect is one to many (with
> Subselect being the many), the join will introduce duplicates that are
> avoided with Tom's EXISTS.
True. When given crappy specs, we have to make some assumptions. If we ask
more information, we're being pesky. Or so I'm told.|||Your heart is in the right place and that is legal SQL-92 syntax. But
SQL Server is behidn the curve.
Use an EXISTS() predicate and watch out for NULLs.|||Tom,
The EXISTS works and is generic to work with both Oracle and SQL Server.
Thanks for everyone's help.
"Tom Moreau" wrote:

> Use EXISTS:
> SELECT *
> FROM Test T
> WHERE EXISTS
> (SELECT *
> FROM SubSelect S
> WHERE S.End_Date IS NULL
> AND S.Key1 = T.Key1
> AND S.Key2 = T.Key2)
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Kevin Shephard" <Kevin Shephard@.discussions.microsoft.com> wrote in messa
ge
> news:DF8C2250-9861-4D50-B6AA-7EC5A806F46C@.microsoft.com...
>
>|||On Mon, 31 Oct 2005 21:46:07 -0500, Aaron Bertrand [SQL Server MVP]
wrote:

>True. When given crappy specs, we have to make some assumptions. If we as
k
>more information, we're being pesky. Or so I'm told.
>
Aye, it's a thin line we're treading. And we can only lose.
Ask for more information, and you get toasted by the OP for asking
questions instead of serving instant gratification.
Make a wrong assumption, and you get toasted by the OP for giving an
incorrect answer.
Make a correct assumption, and some smartypant other poster informs you
that it might have been wrong.....
My post was, of course, not intended as criticism, but as additional
information and a warning for a common pitfall, both to the OP and to
any others that might be reading this thread.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> My post was, of course, not intended as criticism,
Don't worry, it wasn't taken as such...sql

MS-SQL Server equivalent to Oracle 9i?

All,

Oracle 9i provides a "USING" clause option for inner joins, that
allows me to say:

SELECT * FROM TBL1 JOIN TBL2 USING KeyColumn

assuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirable
for our software make use of, but we also support SQL Server. There
is no USING option available, and

SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn

causes an ambiguous column error on KeyColumn.

Is there any equivalent to this Oracle functionality on SQL Server?

KingGreg>> "SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn"
causes an ambiguous column error on KeyColumn <<

And the USING clause is limited to equi-joins. But the real problem is
that good SQL programmers do not use "SELECT *" in production code. It
changes at run time and is too unclear and dangerous.

NATURAL JOIN and USING were two of the worst ideas we put into SQL-92.
I hope they get deprecated soon.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||On 14 May 2004 13:02:13 -0700, KingGreg wrote:

>All,
>Oracle 9i provides a "USING" clause option for inner joins, that
>allows me to say:
>SELECT * FROM TBL1 JOIN TBL2 USING KeyColumn
>assuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirable
>for our software make use of, but we also support SQL Server. There
>is no USING option available, and
>SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
>causes an ambiguous column error on KeyColumn.

I can't reproduce this error:

create table TBL1 (KeyColumn int not null primary key)
create table TBL2 (KeyColumn int not null primary key)
insert TBL1 (KeyColumn)
values(1)
insert TBL1 (KeyColumn)
values(2)
insert TBL2 (KeyColumn)
values(1)
insert TBL2 (KeyColumn)
values(3)
SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
drop table TBL1
drop table TBL2

KeyColumn KeyColumn
---- ----
1 1

(1 row(s) affected)

Can you post the actual SQL that returns this error, as I assume there is
an error somewhere in the query.

>Is there any equivalent to this Oracle functionality on SQL Server?

No, there isn't.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I understand that I was not clear because you have to be using derived
table. See below:

> create table TBL1 (KeyColumn int not null primary key)
> create table TBL2 (KeyColumn int not null primary key)
> insert TBL1 (KeyColumn)
> values(1)
> insert TBL1 (KeyColumn)
> values(2)
> insert TBL2 (KeyColumn)
> values(1)
> insert TBL2 (KeyColumn)
> values(3)
> SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
> drop table TBL1
> drop table TBL2
>
> KeyColumn KeyColumn
> ---- ----
> 1 1
> (1 row(s) affected)

Try :

1 SELECT KeyColumn
2 FROM (
3 SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
4 ) TBL

Gives error : Column 'KeyColumn' specified multiple times for TBL

As you noted it does not fail when running line 3 by itself.

I guess I must be a bad SQL programmer, but this is nonetheless the
direction I am pursuing because of numerous other limitations in SQL
Server or Oracle that prevents using some other solution.

KingGreg|||Can you be more specific?
I agree with Joe that USING and NATURAL JOIN are undesiravel features,
especially since their only purpose in life seems to be to add convenience.
Obviously you are of a different opinion. As a developer I (and quite
likely MS folks listening in) am curious to learn where you see the
value ad.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab|||KingGreg wrote:
> I understand that I was not clear because you have to be using derived
> table. See below:
>
>>create table TBL1 (KeyColumn int not null primary key)
>>create table TBL2 (KeyColumn int not null primary key)
>>insert TBL1 (KeyColumn)
>>values(1)
>>insert TBL1 (KeyColumn)
>>values(2)
>>insert TBL2 (KeyColumn)
>>values(1)
>>insert TBL2 (KeyColumn)
>>values(3)
>>SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
>>drop table TBL1
>>drop table TBL2
>>
>>
>>KeyColumn KeyColumn
>>---- ----
>>1 1
>>
>>(1 row(s) affected)
>>
>
> Try :
> 1 SELECT KeyColumn
> 2 FROM (
> 3 SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
> 4 ) TBL
> Gives error : Column 'KeyColumn' specified multiple times for TBL
> As you noted it does not fail when running line 3 by itself.
> I guess I must be a bad SQL programmer, but this is nonetheless the
> direction I am pursuing because of numerous other limitations in SQL
> Server or Oracle that prevents using some other solution.
> KingGreg

If in Oracle ... I suspect what you are trying to do is:

SELECT KeyColumn
FROM (
SELECT *
FROM TBL1
WHERE TBL1.KeyColumn = TBL2.KeyColumn);

Using ISO standard syntax. If in 9i or above you could also use
ANSI standard syntax.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||As you have an INNER JOIN it doesn't matter which value of keycolumn you
reference as long as you specify an alias. It's best to avoid using SELECT *
in production code anyway (except in an EXISTS subquery). Try this:

SELECT keycolumn
FROM
(SELECT Tbl1.keycolumn
FROM Tbl1 JOIN Tbl2
ON Tbl1.keycolumn = Tbl2.keycolumn) TBL

--
David Portas
SQL Server MVP
--|||Joe Celko wrote:

>>>"SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn"
> causes an ambiguous column error on KeyColumn <<
> And the USING clause is limited to equi-joins. But the real problem is
> that good SQL programmers do not use "SELECT *" in production code. It
> changes at run time and is too unclear and dangerous.

It's not dangerous if your client code accesses the return fields by
name, and not by number. It is, however, generally returning more
data than you need, so it's a waster of resources, and you still
shouldn't do it.

Bill