ANSI's stance on SmallInt vs. Int

I decided to push this out to it's own thread. Here's the question, if you a
re
deciding to abide purely by ANSI SQL 92 standards and are only thinking in t
erms
of the logical model, how does the modeler decide when to use a SmallInt vs.
an
Integer if the allowed range is considered a physical implementation?
A secondary question relates to boolean values. Is the official ANSI solutio
n to
storing boolean values (0 or 1) to use either a full blown Int (of course, s
ince
we are only talking about ANSI, we have no idea how big that is ;-> ) or a
Char(1)?
ThomasANSI / ISO SQL doesn't define absolute numeric precision so the
decisions on which datatype to use can only be made in the context of
an actual implementation. Those physical details are just outside the
scope of the standard. Does it matter?
SQL99 defines a Boolean datatype but SQL Server doesn't support it.
David Portas
SQL Server MVP
--|||> ANSI / ISO SQL doesn't define absolute numeric precision so the
> decisions on which datatype to use can only be made in the context of
> an actual implementation. Those physical details are just outside the
> scope of the standard. Does it matter?
By specifying the concept of a SmallInt but providing no information that wo
uld
help the modeler choose one over the other, it sounds like the ANSI team mad
e an
outright error in including SmallInt in the first place. Said another way, s
ince
designers are already required to accommodate the given DBMS for things like
the
range of values for a SmallInt, where is the problem in using DBMS specific
features like TinyInt? After all, if portability is the holy grail of standa
rds
use, it is already the case that porting a database from one DBMS to another
might break because one vendor used 2-bytes for their SmallInt while another
used 1-byte for their SmallInt. Thus, in terms of data types certainly, it m
akes
sense to use things like TinyInt to accomplish you goal.
In case you were wondering, I bring this up becuase of a comment Mr. Celko m
ade
about not using SQL's TinyInt datatype.

> SQL99 defines a Boolean datatype but SQL Server doesn't support it.
Is that true of SQL 2005 as well?
Thomas

Ansi_nulls

We had a stored procedure that was created while
ansi_nulls was on. Is there a way to tell if SP has any of
the ansi settings changed from default? Just curious.
SkSELECT OBJECTPROPERTY(OBJECT_ID('<procedure name>'), 'ExecIsANSINullsOn')
1 is on, 0 is off.
Jacco Schalkwijk
SQL Server MVP
"Sandeep" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6401c4d7e2$13737150$a501280a@.phx.gbl...
> We had a stored procedure that was created while
> ansi_nulls was on. Is there a way to tell if SP has any of
> the ansi settings changed from default? Just curious.
> Sk|||Thanks a million Jacco.
SK
>--Original Message--
>SELECT OBJECTPROPERTY(OBJECT_ID('<procedure
name>'), 'ExecIsANSINullsOn')
>1 is on, 0 is off.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Sandeep" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1e6401c4d7e2$13737150$a501280a@.phx.gbl...
of[vbcol=seagreen]
>
>.
>

Ansi_nulls

We had a stored procedure that was created while
ansi_nulls was on. Is there a way to tell if SP has any of
the ansi settings changed from default? Just curious.
Sk
SELECT OBJECTPROPERTY(OBJECT_ID('<procedure name>'), 'ExecIsANSINullsOn')
1 is on, 0 is off.
Jacco Schalkwijk
SQL Server MVP
"Sandeep" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6401c4d7e2$13737150$a501280a@.phx.gbl...
> We had a stored procedure that was created while
> ansi_nulls was on. Is there a way to tell if SP has any of
> the ansi settings changed from default? Just curious.
> Sk
|||Thanks a million Jacco.
SK
>--Original Message--
>SELECT OBJECTPROPERTY(OBJECT_ID('<procedure
name>'), 'ExecIsANSINullsOn')
>1 is on, 0 is off.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Sandeep" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1e6401c4d7e2$13737150$a501280a@.phx.gbl...
of
>
>.
>

ANSI vs SQL 92 ambiguity ?!? yeah right

manstein,
I must admit that I did not thoroughly read your posting.
However, the "supposed" ambiguity of the old outer join syntax (such as
*=) is well known and documented. Here is one posting that illustrates
the ambiguity, and highlights this by sharing with the rest of the world
how different RDBMS vendors (at the time) had implemented this syntax:
differently! The posting almost dates back to the last century.
See
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/518d849c9b1040c0?dmode=source
(url may warp)
Gert-Jan
manstein wrote:
> I have uncovered some interesting techniques that I wanted to share
> with everyone. In particular, they relate to the claimed "ambiguity"
> between using the ANSI outer join operators (LEFT OUTER JOIN, RIGHT
> OUTER JOIN) over *= syntax by Microsoft. A little background would be
> helpful. Microsoft has stated that it will not be supporting the
> older *= syntax into the future. The claim is that it poses ambiguity
> when adding filter logic to the left outer join query. If the filter
> logic is added to the FROM clause, the LEFT OUTER JOIN is preserved.
> In contrast, filter logic added to the WHERE clause actually turns a
> left outer join into an inner join. Example:
> CREATE TABLE #foo (indx INT IDENTITY(1,1), taste VARCHAR(255));
> CREATE TABLE #bar (indx INT IDENTITY(1,1), taste VARCHAR(255));
> INSERT INTO #foo (taste) VALUES ('yummy!');
> INSERT INTO #foo (taste) VALUES ('bummy!');
> INSERT INTO #foo (taste) VALUES ('mummy!');
> INSERT INTO #bar (taste) VALUES ('yummy!');
> INSERT INTO #bar (taste) VALUES ('tummy!');
> INSERT INTO #bar (taste) VALUES ('mummy!');
> --This will return all records from dbo.foo with matching records
> from
> --dbo.bar filtered by b.COL1 = "yummy!" non matching dbo.bar records
> will
> --produce NULLS.
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste AND
> b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> 3 mummy! NULL NULL
> -- This will only return records from dbo.foo that have a matching
> -- dbo.bar record where b.COL1 = "yummy!" In other words, this
> produces
> -- an inner join styled "filter"
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste
> WHERE
> b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> This illustration highlights the fact that filter logic in the FROM
> clause is applied before the join is made while filter logic in the
> WHERE clause is applied after the join. This is a characteristic of
> Microsoft's preferred syntax. With the older SQL 92 syntax, we have a
> different flavor. The logic in the WHERE clause also preserves our
> LEFT outer join with the addition of filters. Example:
> SELECT
> *
> FROM
> #foo f,
> #bar b
> WHERE
> f.taste *= b.taste
> AND b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> 3 mummy! NULL NULL
> In my opinion, there is no ambiguity at all. If you are familiar with
> the peculiarities of these two competing styles, you can get the
> results you want. Certainly the query and result themselves are not
> ambiguous to the system. Now, since it is sometimes desirable in
> practice to be able to apply filter logic to a relation *after* two
> tables have been left outer joined (in particular, in WHERE clause
> subqueries), I was curious to test the limits of MS claim. What I
> found is that you can have WHERE clause filter logic with ANSI outer
> join operators but with a twist. The trick is to add an additional
> filter which includes NULL values from your joined table. Example:
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste
> WHERE
> b.taste = 'yummy!' OR
> b.taste IS NULL
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> Here we get a different result. Tuples matching my filter value
> 'yummy!' are included as well as tuples that have no matching value
> and produce NULL. The twist is rows that do have matching values in
> #bar that are not filtered for (i.e. 'mummy!') are excluded. Also,
> there is an addition drawback. If #.bar COL1 has an index defined on
> it, the system will perform an index scan as opposed to a seek.
> Nevertheless, this approach can be a significant improvement on an
> order of magnitude better if the only alternative is line - by -line
> processing.
> As of this writing, older SQL 92 syntax is unsupported in SQL Server
> 2005 level 9.0. With MS's history of dealing with competing
> technologies, I suspect there is more than meets the eye with
> Microsoft's witch hunt against *= syntax beyond the claim of ambiguity
> but I digress.
My favorite is a table (*) with 5 rows that has NULL for a column. And a query that say col IS NULL
now suddenly returns 8 rows with NULL. Where did the 3 non-existing rows come from? Or a query
saying col IS NOT NULL returning 5 rows with NULL for that column? Or the fact that these queries
doesn't return the same result running on 2000 vs 2005 (with compatibility mode 80).
(*) The table is in fact a view, but a view should behave like a table...
Run below in 2000:
USE pubs
GO
EXEC sp_dbcmptlevel pubs, 80
GO
IF OBJECT_ID('v') IS NOT NULL
DROP VIEW v
GO
CREATE VIEW v AS
SELECT p.pub_name, t.price, t.title
FROM publishers AS p, titles AS t
WHERE p.pub_id *= t.pub_id
AND t.price IS NOT NULL -- *** OBS ***
GO
--begrunda nedanstende resultat:
SELECT *
FROM v
SELECT *
FROM v
WHERE price IS NULL
SELECT *
FROM v
WHERE price IS NOT NULL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:46366ED0.C1FE40F3@.toomuchspamalready.nl...[vbcol=seagreen]
> manstein,
> I must admit that I did not thoroughly read your posting.
> However, the "supposed" ambiguity of the old outer join syntax (such as
> *=) is well known and documented. Here is one posting that illustrates
> the ambiguity, and highlights this by sharing with the rest of the world
> how different RDBMS vendors (at the time) had implemented this syntax:
> differently! The posting almost dates back to the last century.
> See
> http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/518d849c9b1040c0?dmode=source
> (url may warp)
> Gert-Jan
>
>
> manstein wrote:
|||"Gert-Jan Strik" wrote:

> *=) is well known and documented. Here is one posting that illustrates
> the ambiguity, and highlights this by sharing with the rest of the world
> how different RDBMS vendors (at the time) had implemented this syntax:
> differently! The posting almost dates back to the last century.
The argument in this post is specious. It is based entirely on the statement
that some implementations of *= are broken. That argument can be equally
applied against LEFT OUTER JOIN, obviously. The spelling mistakes and
obviously overblown statements make me dismiss it out of hand. According to
the post, *= is "simpoly dead wrong and dangerous". OK c00l d00d.
Maury

ANSI vs SQL 92 ambiguity ?!? yeah right

I have uncovered some interesting techniques that I wanted to share
with everyone. In particular, they relate to the claimed "ambiguity"
between using the ANSI outer join operators (LEFT OUTER JOIN, RIGHT
OUTER JOIN) over *= syntax by Microsoft. A little background would be
helpful. Microsoft has stated that it will not be supporting the
older *= syntax into the future. The claim is that it poses ambiguity
when adding filter logic to the left outer join query. If the filter
logic is added to the FROM clause, the LEFT OUTER JOIN is preserved.
In contrast, filter logic added to the WHERE clause actually turns a
left outer join into an inner join. Example:
CREATE TABLE #foo (indx INT IDENTITY(1,1), taste VARCHAR(255));
CREATE TABLE #bar (indx INT IDENTITY(1,1), taste VARCHAR(255));
INSERT INTO #foo (taste) VALUES ('yummy!');
INSERT INTO #foo (taste) VALUES ('bummy!');
INSERT INTO #foo (taste) VALUES ('mummy!');
INSERT INTO #bar (taste) VALUES ('yummy!');
INSERT INTO #bar (taste) VALUES ('tummy!');
INSERT INTO #bar (taste) VALUES ('mummy!');
--This will return all records from dbo.foo with matching records
from
--dbo.bar filtered by b.COL1 = "yummy!" non matching dbo.bar records
will
--produce NULLS.
SELECT
*
FROM
#foo f
LEFT OUTER JOIN #bar b ON b.taste = f.taste AND
b.taste = 'yummy!'
indx taste indx taste
-- -- -- --
1 yummy! 1 yummy!
2 bummy! NULL NULL
3 mummy! NULL NULL
-- This will only return records from dbo.foo that have a matching
-- dbo.bar record where b.COL1 = "yummy!" In other words, this
produces
-- an inner join styled "filter"
SELECT
*
FROM
#foo f
LEFT OUTER JOIN #bar b ON b.taste = f.taste
WHERE
b.taste = 'yummy!'
indx taste indx taste
-- -- -- --
1 yummy! 1 yummy!
This illustration highlights the fact that filter logic in the FROM
clause is applied before the join is made while filter logic in the
WHERE clause is applied after the join. This is a characteristic of
Microsoft's preferred syntax. With the older SQL 92 syntax, we have a
different flavor. The logic in the WHERE clause also preserves our
LEFT outer join with the addition of filters. Example:
SELECT
*
FROM
#foo f,
#bar b
WHERE
f.taste *= b.taste
AND b.taste = 'yummy!'
indx taste indx taste
-- -- -- --
1 yummy! 1 yummy!
2 bummy! NULL NULL
3 mummy! NULL NULL
In my opinion, there is no ambiguity at all. If you are familiar with
the peculiarities of these two competing styles, you can get the
results you want. Certainly the query and result themselves are not
ambiguous to the system. Now, since it is sometimes desirable in
practice to be able to apply filter logic to a relation *after* two
tables have been left outer joined (in particular, in WHERE clause
subqueries), I was curious to test the limits of MS claim. What I
found is that you can have WHERE clause filter logic with ANSI outer
join operators but with a twist. The trick is to add an additional
filter which includes NULL values from your joined table. Example:
SELECT
*
FROM
#foo f
LEFT OUTER JOIN #bar b ON b.taste = f.taste
WHERE
b.taste = 'yummy!' OR
b.taste IS NULL
indx taste indx taste
-- -- -- --
1 yummy! 1 yummy!
2 bummy! NULL NULL
Here we get a different result. Tuples matching my filter value
'yummy!' are included as well as tuples that have no matching value
and produce NULL. The twist is rows that do have matching values in
#bar that are not filtered for (i.e. 'mummy!') are excluded. Also,
there is an addition drawback. If #.bar COL1 has an index defined on
it, the system will perform an index scan as opposed to a seek.
Nevertheless, this approach can be a significant improvement on an
order of magnitude better if the only alternative is line - by -line
processing.
As of this writing, older SQL 92 syntax is unsupported in SQL Server
2005 level 9.0. With MS's history of dealing with competing
technologies, I suspect there is more than meets the eye with
Microsoft's witch hunt against *= syntax beyond the claim of ambiguity
but I digress.manstein,
I must admit that I did not thoroughly read your posting.
However, the "supposed" ambiguity of the old outer join syntax (such as
*=) is well known and documented. Here is one posting that illustrates
the ambiguity, and highlights this by sharing with the rest of the world
how different RDBMS vendors (at the time) had implemented this syntax:
differently! The posting almost dates back to the last century.
See
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/518d849c9b1040c0?dmode=source
(url may warp)
Gert-Jan
manstein wrote:
> I have uncovered some interesting techniques that I wanted to share
> with everyone. In particular, they relate to the claimed "ambiguity"
> between using the ANSI outer join operators (LEFT OUTER JOIN, RIGHT
> OUTER JOIN) over *= syntax by Microsoft. A little background would be
> helpful. Microsoft has stated that it will not be supporting the
> older *= syntax into the future. The claim is that it poses ambiguity
> when adding filter logic to the left outer join query. If the filter
> logic is added to the FROM clause, the LEFT OUTER JOIN is preserved.
> In contrast, filter logic added to the WHERE clause actually turns a
> left outer join into an inner join. Example:
> CREATE TABLE #foo (indx INT IDENTITY(1,1), taste VARCHAR(255));
> CREATE TABLE #bar (indx INT IDENTITY(1,1), taste VARCHAR(255));
> INSERT INTO #foo (taste) VALUES ('yummy!');
> INSERT INTO #foo (taste) VALUES ('bummy!');
> INSERT INTO #foo (taste) VALUES ('mummy!');
> INSERT INTO #bar (taste) VALUES ('yummy!');
> INSERT INTO #bar (taste) VALUES ('tummy!');
> INSERT INTO #bar (taste) VALUES ('mummy!');
> --This will return all records from dbo.foo with matching records
> from
> --dbo.bar filtered by b.COL1 = "yummy!" non matching dbo.bar records
> will
> --produce NULLS.
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste AND
> b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> 3 mummy! NULL NULL
> -- This will only return records from dbo.foo that have a matching
> -- dbo.bar record where b.COL1 = "yummy!" In other words, this
> produces
> -- an inner join styled "filter"
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste
> WHERE
> b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> This illustration highlights the fact that filter logic in the FROM
> clause is applied before the join is made while filter logic in the
> WHERE clause is applied after the join. This is a characteristic of
> Microsoft's preferred syntax. With the older SQL 92 syntax, we have a
> different flavor. The logic in the WHERE clause also preserves our
> LEFT outer join with the addition of filters. Example:
> SELECT
> *
> FROM
> #foo f,
> #bar b
> WHERE
> f.taste *= b.taste
> AND b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> 3 mummy! NULL NULL
> In my opinion, there is no ambiguity at all. If you are familiar with
> the peculiarities of these two competing styles, you can get the
> results you want. Certainly the query and result themselves are not
> ambiguous to the system. Now, since it is sometimes desirable in
> practice to be able to apply filter logic to a relation *after* two
> tables have been left outer joined (in particular, in WHERE clause
> subqueries), I was curious to test the limits of MS claim. What I
> found is that you can have WHERE clause filter logic with ANSI outer
> join operators but with a twist. The trick is to add an additional
> filter which includes NULL values from your joined table. Example:
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste
> WHERE
> b.taste = 'yummy!' OR
> b.taste IS NULL
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> Here we get a different result. Tuples matching my filter value
> 'yummy!' are included as well as tuples that have no matching value
> and produce NULL. The twist is rows that do have matching values in
> #bar that are not filtered for (i.e. 'mummy!') are excluded. Also,
> there is an addition drawback. If #.bar COL1 has an index defined on
> it, the system will perform an index scan as opposed to a seek.
> Nevertheless, this approach can be a significant improvement on an
> order of magnitude better if the only alternative is line - by -line
> processing.
> As of this writing, older SQL 92 syntax is unsupported in SQL Server
> 2005 level 9.0. With MS's history of dealing with competing
> technologies, I suspect there is more than meets the eye with
> Microsoft's witch hunt against *= syntax beyond the claim of ambiguity
> but I digress.|||My favorite is a table (*) with 5 rows that has NULL for a column. And a query that say col IS NULL
now suddenly returns 8 rows with NULL. Where did the 3 non-existing rows come from? Or a query
saying col IS NOT NULL returning 5 rows with NULL for that column? Or the fact that these queries
doesn't return the same result running on 2000 vs 2005 (with compatibility mode 80).
(*) The table is in fact a view, but a view should behave like a table...
Run below in 2000:
USE pubs
GO
EXEC sp_dbcmptlevel pubs, 80
GO
IF OBJECT_ID('v') IS NOT NULL
DROP VIEW v
GO
CREATE VIEW v AS
SELECT p.pub_name, t.price, t.title
FROM publishers AS p, titles AS t
WHERE p.pub_id *= t.pub_id
AND t.price IS NOT NULL -- *** OBS ***
GO
--begrunda nedanstående resultat:
SELECT *
FROM v
SELECT *
FROM v
WHERE price IS NULL
SELECT *
FROM v
WHERE price IS NOT NULL
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:46366ED0.C1FE40F3@.toomuchspamalready.nl...
> manstein,
> I must admit that I did not thoroughly read your posting.
> However, the "supposed" ambiguity of the old outer join syntax (such as
> *=) is well known and documented. Here is one posting that illustrates
> the ambiguity, and highlights this by sharing with the rest of the world
> how different RDBMS vendors (at the time) had implemented this syntax:
> differently! The posting almost dates back to the last century.
> See
> http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/518d849c9b1040c0?dmode=source
> (url may warp)
> Gert-Jan
>
>
> manstein wrote:
>> I have uncovered some interesting techniques that I wanted to share
>> with everyone. In particular, they relate to the claimed "ambiguity"
>> between using the ANSI outer join operators (LEFT OUTER JOIN, RIGHT
>> OUTER JOIN) over *= syntax by Microsoft. A little background would be
>> helpful. Microsoft has stated that it will not be supporting the
>> older *= syntax into the future. The claim is that it poses ambiguity
>> when adding filter logic to the left outer join query. If the filter
>> logic is added to the FROM clause, the LEFT OUTER JOIN is preserved.
>> In contrast, filter logic added to the WHERE clause actually turns a
>> left outer join into an inner join. Example:
>> CREATE TABLE #foo (indx INT IDENTITY(1,1), taste VARCHAR(255));
>> CREATE TABLE #bar (indx INT IDENTITY(1,1), taste VARCHAR(255));
>> INSERT INTO #foo (taste) VALUES ('yummy!');
>> INSERT INTO #foo (taste) VALUES ('bummy!');
>> INSERT INTO #foo (taste) VALUES ('mummy!');
>> INSERT INTO #bar (taste) VALUES ('yummy!');
>> INSERT INTO #bar (taste) VALUES ('tummy!');
>> INSERT INTO #bar (taste) VALUES ('mummy!');
>> --This will return all records from dbo.foo with matching records
>> from
>> --dbo.bar filtered by b.COL1 = "yummy!" non matching dbo.bar records
>> will
>> --produce NULLS.
>> SELECT
>> *
>> FROM
>> #foo f
>> LEFT OUTER JOIN #bar b ON b.taste = f.taste AND
>> b.taste = 'yummy!'
>> indx taste indx taste
>> -- -- -- --
>> 1 yummy! 1 yummy!
>> 2 bummy! NULL NULL
>> 3 mummy! NULL NULL
>> -- This will only return records from dbo.foo that have a matching
>> -- dbo.bar record where b.COL1 = "yummy!" In other words, this
>> produces
>> -- an inner join styled "filter"
>> SELECT
>> *
>> FROM
>> #foo f
>> LEFT OUTER JOIN #bar b ON b.taste = f.taste
>> WHERE
>> b.taste = 'yummy!'
>> indx taste indx taste
>> -- -- -- --
>> 1 yummy! 1 yummy!
>> This illustration highlights the fact that filter logic in the FROM
>> clause is applied before the join is made while filter logic in the
>> WHERE clause is applied after the join. This is a characteristic of
>> Microsoft's preferred syntax. With the older SQL 92 syntax, we have a
>> different flavor. The logic in the WHERE clause also preserves our
>> LEFT outer join with the addition of filters. Example:
>> SELECT
>> *
>> FROM
>> #foo f,
>> #bar b
>> WHERE
>> f.taste *= b.taste
>> AND b.taste = 'yummy!'
>> indx taste indx taste
>> -- -- -- --
>> 1 yummy! 1 yummy!
>> 2 bummy! NULL NULL
>> 3 mummy! NULL NULL
>> In my opinion, there is no ambiguity at all. If you are familiar with
>> the peculiarities of these two competing styles, you can get the
>> results you want. Certainly the query and result themselves are not
>> ambiguous to the system. Now, since it is sometimes desirable in
>> practice to be able to apply filter logic to a relation *after* two
>> tables have been left outer joined (in particular, in WHERE clause
>> subqueries), I was curious to test the limits of MS claim. What I
>> found is that you can have WHERE clause filter logic with ANSI outer
>> join operators but with a twist. The trick is to add an additional
>> filter which includes NULL values from your joined table. Example:
>> SELECT
>> *
>> FROM
>> #foo f
>> LEFT OUTER JOIN #bar b ON b.taste = f.taste
>> WHERE
>> b.taste = 'yummy!' OR
>> b.taste IS NULL
>> indx taste indx taste
>> -- -- -- --
>> 1 yummy! 1 yummy!
>> 2 bummy! NULL NULL
>> Here we get a different result. Tuples matching my filter value
>> 'yummy!' are included as well as tuples that have no matching value
>> and produce NULL. The twist is rows that do have matching values in
>> #bar that are not filtered for (i.e. 'mummy!') are excluded. Also,
>> there is an addition drawback. If #.bar COL1 has an index defined on
>> it, the system will perform an index scan as opposed to a seek.
>> Nevertheless, this approach can be a significant improvement on an
>> order of magnitude better if the only alternative is line - by -line
>> processing.
>> As of this writing, older SQL 92 syntax is unsupported in SQL Server
>> 2005 level 9.0. With MS's history of dealing with competing
>> technologies, I suspect there is more than meets the eye with
>> Microsoft's witch hunt against *= syntax beyond the claim of ambiguity
>> but I digress.|||There are other problems with the old *= syntax.
It is proprietary to Sybase/MS and therefore incompatible with other
DBMSs
It is limited to equijoins only
It can't do FULL outer joins
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On May 1, 6:21 am, David Portas
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> There are other problems with the old *= syntax.
> It is proprietary to Sybase/MS and therefore incompatible with other
> DBMSs
> It is limited to equijoins only
> It can't do FULL outer joins
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
David and Jert
Your posts mean nothing in regards to mine. I clearly state that
you will get different results between LEFT OUTER JOIN and *= My
point is that if you are familiar with the distinctions then you
should be able to use them as needed.
Tibor gets full credit but he is discussing views and it would seem
that is an implementation bug with SQL Server engine so thumbs down to
MS.|||"Gert-Jan Strik" wrote:
> *=) is well known and documented. Here is one posting that illustrates
> the ambiguity, and highlights this by sharing with the rest of the world
> how different RDBMS vendors (at the time) had implemented this syntax:
> differently! The posting almost dates back to the last century.
The argument in this post is specious. It is based entirely on the statement
that some implementations of *= are broken. That argument can be equally
applied against LEFT OUTER JOIN, obviously. The spelling mistakes and
obviously overblown statements make me dismiss it out of hand. According to
the post, *= is "simpoly dead wrong and dangerous". OK c00l d00d.
Maury

ANSI vs SQL 92 ambiguity ?!? yeah right

I have uncovered some interesting techniques that I wanted to share
with everyone. In particular, they relate to the claimed "ambiguity"
between using the ANSI outer join operators (LEFT OUTER JOIN, RIGHT
OUTER JOIN) over *= syntax by Microsoft. A little background would be
helpful. Microsoft has stated that it will not be supporting the
older *= syntax into the future. The claim is that it poses ambiguity
when adding filter logic to the left outer join query. If the filter
logic is added to the FROM clause, the LEFT OUTER JOIN is preserved.
In contrast, filter logic added to the WHERE clause actually turns a
left outer join into an inner join. Example:
CREATE TABLE #foo (indx INT IDENTITY(1,1), taste VARCHAR(255));
CREATE TABLE #bar (indx INT IDENTITY(1,1), taste VARCHAR(255));
INSERT INTO #foo (taste) VALUES ('yummy!');
INSERT INTO #foo (taste) VALUES ('bummy!');
INSERT INTO #foo (taste) VALUES ('mummy!');
INSERT INTO #bar (taste) VALUES ('yummy!');
INSERT INTO #bar (taste) VALUES ('tummy!');
INSERT INTO #bar (taste) VALUES ('mummy!');
--This will return all records from dbo.foo with matching records
from
--dbo.bar filtered by b.COL1 = "yummy!" non matching dbo.bar records
will
--produce NULLS.
SELECT
*
FROM
#foo f
LEFT OUTER JOIN #bar b ON b.taste = f.taste AND
b.taste = 'yummy!'
indx taste indx taste
-- -- -- --
1 yummy! 1 yummy!
2 bummy! NULL NULL
3 mummy! NULL NULL
-- This will only return records from dbo.foo that have a matching
-- dbo.bar record where b.COL1 = "yummy!" In other words, this
produces
-- an inner join styled "filter"
SELECT
*
FROM
#foo f
LEFT OUTER JOIN #bar b ON b.taste = f.taste
WHERE
b.taste = 'yummy!'
indx taste indx taste
-- -- -- --
1 yummy! 1 yummy!
This illustration highlights the fact that filter logic in the FROM
clause is applied before the join is made while filter logic in the
WHERE clause is applied after the join. This is a characteristic of
Microsoft's preferred syntax. With the older SQL 92 syntax, we have a
different flavor. The logic in the WHERE clause also preserves our
LEFT outer join with the addition of filters. Example:
SELECT
*
FROM
#foo f,
#bar b
WHERE
f.taste *= b.taste
AND b.taste = 'yummy!'
indx taste indx taste
-- -- -- --
1 yummy! 1 yummy!
2 bummy! NULL NULL
3 mummy! NULL NULL
In my opinion, there is no ambiguity at all. If you are familiar with
the peculiarities of these two competing styles, you can get the
results you want. Certainly the query and result themselves are not
ambiguous to the system. Now, since it is sometimes desirable in
practice to be able to apply filter logic to a relation *after* two
tables have been left outer joined (in particular, in WHERE clause
subqueries), I was curious to test the limits of MS claim. What I
found is that you can have WHERE clause filter logic with ANSI outer
join operators but with a twist. The trick is to add an additional
filter which includes NULL values from your joined table. Example:
SELECT
*
FROM
#foo f
LEFT OUTER JOIN #bar b ON b.taste = f.taste
WHERE
b.taste = 'yummy!' OR
b.taste IS NULL
indx taste indx taste
-- -- -- --
1 yummy! 1 yummy!
2 bummy! NULL NULL
Here we get a different result. Tuples matching my filter value
'yummy!' are included as well as tuples that have no matching value
and produce NULL. The twist is rows that do have matching values in
#bar that are not filtered for (i.e. 'mummy!') are excluded. Also,
there is an addition drawback. If #.bar COL1 has an index defined on
it, the system will perform an index scan as opposed to a seek.
Nevertheless, this approach can be a significant improvement on an
order of magnitude better if the only alternative is line - by -line
processing.
As of this writing, older SQL 92 syntax is unsupported in SQL Server
2005 level 9.0. With MS's history of dealing with competing
technologies, I suspect there is more than meets the eye with
Microsoft's witch hunt against *= syntax beyond the claim of ambiguity
but I digress.manstein,
I must admit that I did not thoroughly read your posting.
However, the "supposed" ambiguity of the old outer join syntax (such as
*=) is well known and documented. Here is one posting that illustrates
the ambiguity, and highlights this by sharing with the rest of the world
how different RDBMS vendors (at the time) had implemented this syntax:
differently! The posting almost dates back to the last century.
See
http://groups.google.com/group/micr...c0?dmode=source
(url may warp)
Gert-Jan
manstein wrote:
> I have uncovered some interesting techniques that I wanted to share
> with everyone. In particular, they relate to the claimed "ambiguity"
> between using the ANSI outer join operators (LEFT OUTER JOIN, RIGHT
> OUTER JOIN) over *= syntax by Microsoft. A little background would be
> helpful. Microsoft has stated that it will not be supporting the
> older *= syntax into the future. The claim is that it poses ambiguity
> when adding filter logic to the left outer join query. If the filter
> logic is added to the FROM clause, the LEFT OUTER JOIN is preserved.
> In contrast, filter logic added to the WHERE clause actually turns a
> left outer join into an inner join. Example:
> CREATE TABLE #foo (indx INT IDENTITY(1,1), taste VARCHAR(255));
> CREATE TABLE #bar (indx INT IDENTITY(1,1), taste VARCHAR(255));
> INSERT INTO #foo (taste) VALUES ('yummy!');
> INSERT INTO #foo (taste) VALUES ('bummy!');
> INSERT INTO #foo (taste) VALUES ('mummy!');
> INSERT INTO #bar (taste) VALUES ('yummy!');
> INSERT INTO #bar (taste) VALUES ('tummy!');
> INSERT INTO #bar (taste) VALUES ('mummy!');
> --This will return all records from dbo.foo with matching records
> from
> --dbo.bar filtered by b.COL1 = "yummy!" non matching dbo.bar records
> will
> --produce NULLS.
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste AND
> b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> 3 mummy! NULL NULL
> -- This will only return records from dbo.foo that have a matching
> -- dbo.bar record where b.COL1 = "yummy!" In other words, this
> produces
> -- an inner join styled "filter"
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste
> WHERE
> b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> This illustration highlights the fact that filter logic in the FROM
> clause is applied before the join is made while filter logic in the
> WHERE clause is applied after the join. This is a characteristic of
> Microsoft's preferred syntax. With the older SQL 92 syntax, we have a
> different flavor. The logic in the WHERE clause also preserves our
> LEFT outer join with the addition of filters. Example:
> SELECT
> *
> FROM
> #foo f,
> #bar b
> WHERE
> f.taste *= b.taste
> AND b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> 3 mummy! NULL NULL
> In my opinion, there is no ambiguity at all. If you are familiar with
> the peculiarities of these two competing styles, you can get the
> results you want. Certainly the query and result themselves are not
> ambiguous to the system. Now, since it is sometimes desirable in
> practice to be able to apply filter logic to a relation *after* two
> tables have been left outer joined (in particular, in WHERE clause
> subqueries), I was curious to test the limits of MS claim. What I
> found is that you can have WHERE clause filter logic with ANSI outer
> join operators but with a twist. The trick is to add an additional
> filter which includes NULL values from your joined table. Example:
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste
> WHERE
> b.taste = 'yummy!' OR
> b.taste IS NULL
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> Here we get a different result. Tuples matching my filter value
> 'yummy!' are included as well as tuples that have no matching value
> and produce NULL. The twist is rows that do have matching values in
> #bar that are not filtered for (i.e. 'mummy!') are excluded. Also,
> there is an addition drawback. If #.bar COL1 has an index defined on
> it, the system will perform an index scan as opposed to a seek.
> Nevertheless, this approach can be a significant improvement on an
> order of magnitude better if the only alternative is line - by -line
> processing.
> As of this writing, older SQL 92 syntax is unsupported in SQL Server
> 2005 level 9.0. With MS's history of dealing with competing
> technologies, I suspect there is more than meets the eye with
> Microsoft's witch hunt against *= syntax beyond the claim of ambiguity
> but I digress.|||My favorite is a table (*) with 5 rows that has NULL for a column. And a que
ry that say col IS NULL
now suddenly returns 8 rows with NULL. Where did the 3 non-existing rows com
e from? Or a query
saying col IS NOT NULL returning 5 rows with NULL for that column? Or the fa
ct that these queries
doesn't return the same result running on 2000 vs 2005 (with compatibility m
ode 80).
(*) The table is in fact a view, but a view should behave like a table...
Run below in 2000:
USE pubs
GO
EXEC sp_dbcmptlevel pubs, 80
GO
IF OBJECT_ID('v') IS NOT NULL
DROP VIEW v
GO
CREATE VIEW v AS
SELECT p.pub_name, t.price, t.title
FROM publishers AS p, titles AS t
WHERE p.pub_id *= t.pub_id
AND t.price IS NOT NULL -- *** OBS ***
GO
--begrunda nedanstende resultat:
SELECT *
FROM v
SELECT *
FROM v
WHERE price IS NULL
SELECT *
FROM v
WHERE price IS NOT NULL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:46366ED0.C1FE40F3@.toomuchspamalready.nl...[vbcol=seagreen]
> manstein,
> I must admit that I did not thoroughly read your posting.
> However, the "supposed" ambiguity of the old outer join syntax (such as
> *=) is well known and documented. Here is one posting that illustrates
> the ambiguity, and highlights this by sharing with the rest of the world
> how different RDBMS vendors (at the time) had implemented this syntax:
> differently! The posting almost dates back to the last century.
> See
> http://groups.google.com/group/micr...c0?dmode=source
> (url may warp)
> Gert-Jan
>
>
> manstein wrote:|||"Gert-Jan Strik" wrote:

> *=) is well known and documented. Here is one posting that illustrates
> the ambiguity, and highlights this by sharing with the rest of the world
> how different RDBMS vendors (at the time) had implemented this syntax:
> differently! The posting almost dates back to the last century.
The argument in this post is specious. It is based entirely on the statement
that some implementations of *= are broken. That argument can be equally
applied against LEFT OUTER JOIN, obviously. The spelling mistakes and
obviously overblown statements make me dismiss it out of hand. According to
the post, *= is "simpoly dead wrong and dangerous". OK c00l d00d.
Maury

ANSI to Unicode(MSSQL) convertions

Hi,
How to convert and transfer ANSI string data from FoxPro table to MS SQL
Unicode table. Data in FoxPro are in 1251 codepage (Russian Windows). Now
result is like " 100 ".The ODBC driver does not
performs the conversion from ANSI to Unicode.
Help.Does STRCONV() help?
-Anders
"Aras Kucinskas" <aras@.skuba.lt> wrote in message
news:Ot0I694AFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hi,
> How to convert and transfer ANSI string data from FoxPro table to MS SQL
> Unicode table. Data in FoxPro are in 1251 codepage (Russian Windows). Now
> result is like " 100 ".The ODBC driver does not
> performs the conversion from ANSI to Unicode.
> Help.
>|||MS SQL usually stores data in CP-1251 for ru_RU locale by default. Your
example here reads as "compressor ring 100 milimeters" in CP-1251 in fact.
Nevertheless, Activex script mechanism doesn't allow codepage translations,
as I was explained here some time ago. You probably need to add some VB /
VB.NET code to your DTS package. Of course, if you do need to translate your
strings.
Nick
"Aras Kucinskas" <aras@.skuba.lt> wrote in message
news:Ot0I694AFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hi,
> How to convert and transfer ANSI string data from FoxPro table to MS SQL
> Unicode table. Data in FoxPro are in 1251 codepage (Russian Windows).
Now
> result is like " 100 ".The ODBC driver does not
> performs the conversion from ANSI to Unicode.
> Help.
>|||Thanks Nick,
What functions I can use for string translation?
"Nikolai Lukin" <nvlukin@.gran-service.ru> wrote in message
news:35r898F4pjkqgU1@.individual.net...
> MS SQL usually stores data in CP-1251 for ru_RU locale by default. Your
> example here reads as "compressor ring 100 milimeters" in CP-1251 in fact.
> Nevertheless, Activex script mechanism doesn't allow codepage
translations,
> as I was explained here some time ago. You probably need to add some VB /
> VB.NET code to your DTS package. Of course, if you do need to translate
your
> strings.
> Nick
> "Aras Kucinskas" <aras@.skuba.lt> wrote in message
> news:Ot0I694AFHA.3708@.TK2MSFTNGP14.phx.gbl...
SQL
> Now
not
>|||SQL Server stores based on the codepage of the database!
For example:
If your client is using codepage 850 and the database 1250 you will have
automatic conversion.
If your client is using having a ASCII variable stored in codepage 850 and
your stored this in a Unicode column on the server, by default this will get
converted to the server side codepage first. You can also convert it to
Unicode on the client first and insert it, in which case it will got in as
is.
Please read:
International Features in Microsoft SQL Server 2000
http://msdn.microsoft.com/library/e...000.a
sp
PRB: SQL Server ODBC Driver Converts Language Events to Unicode (234748)
http://support.microsoft.com/defaul...KB;EN-US;234748
INF: SQL Server 7.0 BCP and Code Page Conversion (199819)
http://support.microsoft.com/defaul...KB;EN-US;199819
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Aras Kucinskas" <aras@.skuba.lt> wrote in message
news:O094iWDBFHA.2112@.TK2MSFTNGP09.phx.gbl...
> Thanks Nick,
> What functions I can use for string translation?
> "Nikolai Lukin" <nvlukin@.gran-service.ru> wrote in message
> news:35r898F4pjkqgU1@.individual.net...
> translations,
> your
> SQL
> not
>|||Here follows some portion of a VB.NET code provided "as is" with absolutely
no warranties. Functions translate a string variable from UTF-8 to ASCII
forth and back. Though code says for itself.
--Quote
Imports System.Text
Public utf8 As Encoding = utf8.UTF8
Public ascii As Encoding = ascii.Default
Public Function Utf8ToAscii(ByVal DataString As String) As String
If DataString = "" Then Return "" Else Return
utf8.GetString(ascii.GetBytes(DataString))
End Function
Public Function AsciiToUtf8(ByVal DataString As String) As String
If DataString = "" Then Return "" Else Return
ascii.GetString(utf8.GetBytes(DataString))
End Function
--Unquote
HTH
Nick
"Aras Kucinskas" <aras@.skuba.lt> wrote in message
news:O094iWDBFHA.2112@.TK2MSFTNGP09.phx.gbl...
> Thanks Nick,
> What functions I can use for string translation?
> "Nikolai Lukin" <nvlukin@.gran-service.ru> wrote in message
> news:35r898F4pjkqgU1@.individual.net...
> translations,
> your
> SQL
> not
>|||Sorry, this should read as:

> Imports System.Text
> Public utf8 As Encoding = utf8.UTF8
> Public ascii As Encoding = ascii.Default
> Public Function Utf8ToAscii(ByVal DataString As String) As String
> If DataString = "" Then Return "" Else Return
> utf8.GetString(ascii.GetBytes(DataString))
> End Function
> Public Function AsciiToUtf8(ByVal DataString As String) As String
> If DataString = "" Then Return "" Else Return
> ascii.GetString(utf8.GetBytes(DataString))
> End Function
Nick

ANSI to Unicode(MSSQL) convertions

Hi,
How to convert and transfer ANSI string data from FoxPro table to MS SQL
Unicode table. Data in FoxPro are in 1251 codepage (Russian Windows). Now
result is like " 100 ".The ODBC driver does not
performs the conversion from ANSI to Unicode.
Help.
Does STRCONV() help?
-Anders
"Aras Kucinskas" <aras@.skuba.lt> wrote in message
news:Ot0I694AFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hi,
> How to convert and transfer ANSI string data from FoxPro table to MS SQL
> Unicode table. Data in FoxPro are in 1251 codepage (Russian Windows). Now
> result is like " 100 ".The ODBC driver does not
> performs the conversion from ANSI to Unicode.
> Help.
>
|||MS SQL usually stores data in CP-1251 for ru_RU locale by default. Your
example here reads as "compressor ring 100 milimeters" in CP-1251 in fact.
Nevertheless, Activex script mechanism doesn't allow codepage translations,
as I was explained here some time ago. You probably need to add some VB /
VB.NET code to your DTS package. Of course, if you do need to translate your
strings.
Nick
"Aras Kucinskas" <aras@.skuba.lt> wrote in message
news:Ot0I694AFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hi,
> How to convert and transfer ANSI string data from FoxPro table to MS SQL
> Unicode table. Data in FoxPro are in 1251 codepage (Russian Windows).
Now
> result is like " 100 ".The ODBC driver does not
> performs the conversion from ANSI to Unicode.
> Help.
>
|||Thanks Nick,
What functions I can use for string translation?
"Nikolai Lukin" <nvlukin@.gran-service.ru> wrote in message
news:35r898F4pjkqgU1@.individual.net...
> MS SQL usually stores data in CP-1251 for ru_RU locale by default. Your
> example here reads as "compressor ring 100 milimeters" in CP-1251 in fact.
> Nevertheless, Activex script mechanism doesn't allow codepage
translations,
> as I was explained here some time ago. You probably need to add some VB /
> VB.NET code to your DTS package. Of course, if you do need to translate
your[vbcol=seagreen]
> strings.
> Nick
> "Aras Kucinskas" <aras@.skuba.lt> wrote in message
> news:Ot0I694AFHA.3708@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
> Now
not
>
|||SQL Server stores based on the codepage of the database!
For example:
If your client is using codepage 850 and the database 1250 you will have
automatic conversion.
If your client is using having a ASCII variable stored in codepage 850 and
your stored this in a Unicode column on the server, by default this will get
converted to the server side codepage first. You can also convert it to
Unicode on the client first and insert it, in which case it will got in as
is.
Please read:
International Features in Microsoft SQL Server 2000
http://msdn.microsoft.com/library/en...server2000.asp
PRB: SQL Server ODBC Driver Converts Language Events to Unicode (234748)
http://support.microsoft.com/default...B;EN-US;234748
INF: SQL Server 7.0 BCP and Code Page Conversion (199819)
http://support.microsoft.com/default...B;EN-US;199819
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Aras Kucinskas" <aras@.skuba.lt> wrote in message
news:O094iWDBFHA.2112@.TK2MSFTNGP09.phx.gbl...
> Thanks Nick,
> What functions I can use for string translation?
> "Nikolai Lukin" <nvlukin@.gran-service.ru> wrote in message
> news:35r898F4pjkqgU1@.individual.net...
> translations,
> your
> SQL
> not
>
|||Here follows some portion of a VB.NET code provided "as is" with absolutely
no warranties. Functions translate a string variable from UTF-8 to ASCII
forth and back. Though code says for itself.
--Quote
Imports System.Text
Public utf8 As Encoding = utf8.UTF8
Public ascii As Encoding = ascii.Default
Public Function Utf8ToAscii(ByVal DataString As String) As String
If DataString = "" Then Return "" Else Return
utf8.GetString(ascii.GetBytes(DataString))
End Function
Public Function AsciiToUtf8(ByVal DataString As String) As String
If DataString = "" Then Return "" Else Return
ascii.GetString(utf8.GetBytes(DataString))
End Function
--Unquote
HTH
Nick
"Aras Kucinskas" <aras@.skuba.lt> wrote in message
news:O094iWDBFHA.2112@.TK2MSFTNGP09.phx.gbl...
> Thanks Nick,
> What functions I can use for string translation?
> "Nikolai Lukin" <nvlukin@.gran-service.ru> wrote in message
> news:35r898F4pjkqgU1@.individual.net...
> translations,
> your
> SQL
> not
>
|||Sorry, this should read as:

> Imports System.Text
> Public utf8 As Encoding = utf8.UTF8
> Public ascii As Encoding = ascii.Default
> Public Function Utf8ToAscii(ByVal DataString As String) As String
> If DataString = "" Then Return "" Else Return
> utf8.GetString(ascii.GetBytes(DataString))
> End Function
> Public Function AsciiToUtf8(ByVal DataString As String) As String
> If DataString = "" Then Return "" Else Return
> ascii.GetString(utf8.GetBytes(DataString))
> End Function
Nick

ANSI to Unicode(MSSQL) convertions

Hi,
How to convert and transfer ANSI string data from FoxPro table to MS SQL
Unicode table. Data in FoxPro are in 1251 codepage (Russian Windows). Now
result is like " 100 ".The ODBC driver does not
performs the conversion from ANSI to Unicode.
Help.Does STRCONV() help?
-Anders
"Aras Kucinskas" <aras@.skuba.lt> wrote in message
news:Ot0I694AFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hi,
> How to convert and transfer ANSI string data from FoxPro table to MS SQL
> Unicode table. Data in FoxPro are in 1251 codepage (Russian Windows). Now
> result is like " 100 ".The ODBC driver does not
> performs the conversion from ANSI to Unicode.
> Help.
>|||MS SQL usually stores data in CP-1251 for ru_RU locale by default. Your
example here reads as "compressor ring 100 milimeters" in CP-1251 in fact.
Nevertheless, Activex script mechanism doesn't allow codepage translations,
as I was explained here some time ago. You probably need to add some VB /
VB.NET code to your DTS package. Of course, if you do need to translate your
strings.
Nick
"Aras Kucinskas" <aras@.skuba.lt> wrote in message
news:Ot0I694AFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hi,
> How to convert and transfer ANSI string data from FoxPro table to MS SQL
> Unicode table. Data in FoxPro are in 1251 codepage (Russian Windows).
Now
> result is like " 100 ".The ODBC driver does not
> performs the conversion from ANSI to Unicode.
> Help.
>|||Thanks Nick,
What functions I can use for string translation?
"Nikolai Lukin" <nvlukin@.gran-service.ru> wrote in message
news:35r898F4pjkqgU1@.individual.net...
> MS SQL usually stores data in CP-1251 for ru_RU locale by default. Your
> example here reads as "compressor ring 100 milimeters" in CP-1251 in fact.
> Nevertheless, Activex script mechanism doesn't allow codepage
translations,
> as I was explained here some time ago. You probably need to add some VB /
> VB.NET code to your DTS package. Of course, if you do need to translate
your
> strings.
> Nick
> "Aras Kucinskas" <aras@.skuba.lt> wrote in message
> news:Ot0I694AFHA.3708@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
> Now
not[vbcol=seagreen]
>|||SQL Server stores based on the codepage of the database!
For example:
If your client is using codepage 850 and the database 1250 you will have
automatic conversion.
If your client is using having a ASCII variable stored in codepage 850 and
your stored this in a Unicode column on the server, by default this will get
converted to the server side codepage first. You can also convert it to
Unicode on the client first and insert it, in which case it will got in as
is.
Please read:
International Features in Microsoft SQL Server 2000
http://msdn.microsoft.com/library/e...000.a
sp
PRB: SQL Server ODBC Driver Converts Language Events to Unicode (234748)
http://support.microsoft.com/defaul...KB;EN-US;234748
INF: SQL Server 7.0 BCP and Code Page Conversion (199819)
http://support.microsoft.com/defaul...KB;EN-US;199819
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Aras Kucinskas" <aras@.skuba.lt> wrote in message
news:O094iWDBFHA.2112@.TK2MSFTNGP09.phx.gbl...
> Thanks Nick,
> What functions I can use for string translation?
> "Nikolai Lukin" <nvlukin@.gran-service.ru> wrote in message
> news:35r898F4pjkqgU1@.individual.net...
> translations,
> your
> SQL
> not
>|||Here follows some portion of a VB.NET code provided "as is" with absolutely
no warranties. Functions translate a string variable from UTF-8 to ASCII
forth and back. Though code says for itself.
--Quote
Imports System.Text
Public utf8 As Encoding = utf8.UTF8
Public ascii As Encoding = ascii.Default
Public Function Utf8ToAscii(ByVal DataString As String) As String
If DataString = "" Then Return "" Else Return
utf8.GetString(ascii.GetBytes(DataString))
End Function
Public Function AsciiToUtf8(ByVal DataString As String) As String
If DataString = "" Then Return "" Else Return
ascii.GetString(utf8.GetBytes(DataString))
End Function
--Unquote
HTH
Nick
"Aras Kucinskas" <aras@.skuba.lt> wrote in message
news:O094iWDBFHA.2112@.TK2MSFTNGP09.phx.gbl...
> Thanks Nick,
> What functions I can use for string translation?
> "Nikolai Lukin" <nvlukin@.gran-service.ru> wrote in message
> news:35r898F4pjkqgU1@.individual.net...
> translations,
> your
> SQL
> not
>|||Sorry, this should read as:

> Imports System.Text
> Public utf8 As Encoding = utf8.UTF8
> Public ascii As Encoding = ascii.Default
> Public Function Utf8ToAscii(ByVal DataString As String) As String
> If DataString = "" Then Return "" Else Return
> utf8.GetString(ascii.GetBytes(DataString))
> End Function
> Public Function AsciiToUtf8(ByVal DataString As String) As String
> If DataString = "" Then Return "" Else Return
> ascii.GetString(utf8.GetBytes(DataString))
> End Function
Nick

ANSI to OEM

How or Where can I see if ANSI to OEM is turned off or turned on.
Mike,
In what context?
From BOL:
******
Connection Object - Auto Translate Parameter
Indicates whether OEM/ANSI characters are converted. This property can be
set to True or False. The default value is True. If this property is set to
True, SQLOLEDB performs OEM/ANSI character conversion when multibyte
character strings are retrieved from, or sent to, SQL Server. If this
property is set to False, SQLOLEDB does not perform OEM/ANSI character
conversion on multibyte character string data.
******
and
******
bcp
Using character mode, bcp, by default, always converts characters from the
data file to ANSI characters before bulk copying them into an instance of
SQL Server, and converts characters from SQL Server to OEM characters before
copying them to the data file. Extended character data can be lost during
the OEM to ANSI or ANSI to OEM conversions. To prevent loss of extended
characters, use Unicode character format, or specify a code page for the
bulk copy operation using -C (or the CODEPAGE clause for the BULK INSERT
statement).
******
HTH
Jerry
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:FB56D48A-23C7-48D5-9FFE-55C2C5978EFD@.microsoft.com...
> How or Where can I see if ANSI to OEM is turned off or turned on.
|||"Mike" wrote:

> How or Where can I see if ANSI to OEM is turned off or turned on.
I would like to know where, on SQL, can I see if this option is turned on or
turned off...if you know a sp who could help me...

ANSI to OEM

How or Where can I see if ANSI to OEM is turned off or turned on.Mike,
In what context?
From BOL:
******
Connection Object - Auto Translate Parameter
Indicates whether OEM/ANSI characters are converted. This property can be
set to True or False. The default value is True. If this property is set to
True, SQLOLEDB performs OEM/ANSI character conversion when multibyte
character strings are retrieved from, or sent to, SQL Server. If this
property is set to False, SQLOLEDB does not perform OEM/ANSI character
conversion on multibyte character string data.
******
and
******
bcp
Using character mode, bcp, by default, always converts characters from the
data file to ANSI characters before bulk copying them into an instance of
SQL Server, and converts characters from SQL Server to OEM characters before
copying them to the data file. Extended character data can be lost during
the OEM to ANSI or ANSI to OEM conversions. To prevent loss of extended
characters, use Unicode character format, or specify a code page for the
bulk copy operation using -C (or the CODEPAGE clause for the BULK INSERT
statement).
******
HTH
Jerry
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:FB56D48A-23C7-48D5-9FFE-55C2C5978EFD@.microsoft.com...
> How or Where can I see if ANSI to OEM is turned off or turned on.|||"Mike" wrote:

> How or Where can I see if ANSI to OEM is turned off or turned on.
I would like to know where, on SQL, can I see if this option is turned on or
turned off...if you know a sp who could help me...

ANSI to OEM

How or Where can I see if ANSI to OEM is turned off or turned on.Mike,
In what context?
From BOL:
******
Connection Object - Auto Translate Parameter
Indicates whether OEM/ANSI characters are converted. This property can be
set to True or False. The default value is True. If this property is set to
True, SQLOLEDB performs OEM/ANSI character conversion when multibyte
character strings are retrieved from, or sent to, SQL Server. If this
property is set to False, SQLOLEDB does not perform OEM/ANSI character
conversion on multibyte character string data.
******
and
******
bcp
Using character mode, bcp, by default, always converts characters from the
data file to ANSI characters before bulk copying them into an instance of
SQL Server, and converts characters from SQL Server to OEM characters before
copying them to the data file. Extended character data can be lost during
the OEM to ANSI or ANSI to OEM conversions. To prevent loss of extended
characters, use Unicode character format, or specify a code page for the
bulk copy operation using -C (or the CODEPAGE clause for the BULK INSERT
statement).
******
HTH
Jerry
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:FB56D48A-23C7-48D5-9FFE-55C2C5978EFD@.microsoft.com...
> How or Where can I see if ANSI to OEM is turned off or turned on.|||"Mike" wrote:
> How or Where can I see if ANSI to OEM is turned off or turned on.
I would like to know where, on SQL, can I see if this option is turned on or
turned off...if you know a sp who could help me...

ANSI Standards

We are in the process of migrating our MS SQL Server 2000 databases to MS
SQL Server 2005.
We have using the MS Upgrade Advisor to flag problems in our databases so
they can be fixed. Does there exist a tool from which we can get a report on
which databases contain components (tables, columns, stored procedures) that
do not meet ANSI standards?You can try Best Practices Analyzer tool for SQL Server 2000 by Microsoft ..
here is the link to download page:
http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
"Loren Z" wrote:
> We are in the process of migrating our MS SQL Server 2000 databases to MS
> SQL Server 2005.
> We have using the MS Upgrade Advisor to flag problems in our databases so
> they can be fixed. Does there exist a tool from which we can get a report on
> which databases contain components (tables, columns, stored procedures) that
> do not meet ANSI standards?
>
>

ANSI Standards

We are in the process of migrating our MS SQL Server 2000 databases to MS
SQL Server 2005.
We have using the MS Upgrade Advisor to flag problems in our databases so
they can be fixed. Does there exist a tool from which we can get a report on
which databases contain components (tables, columns, stored procedures) that
do not meet ANSI standards?You can try Best Practices Analyzer tool for SQL Server 2000 by Microsoft ..
here is the link to download page:
http://www.microsoft.com/downloads/...&displaylang=en
"Loren Z" wrote:

> We are in the process of migrating our MS SQL Server 2000 databases to MS
> SQL Server 2005.
> We have using the MS Upgrade Advisor to flag problems in our databases so
> they can be fixed. Does there exist a tool from which we can get a report
on
> which databases contain components (tables, columns, stored procedures) th
at
> do not meet ANSI standards?
>
>

ANSI SQL-99 Compliance Level

When I researched SQL Server 2000, I was told (I believe by Hal Berenson) that it was entry-level compliant with SQL-92, but the next version was going to be compliant with SQL-99. I haven't been able to comfirm this in the docs. Can anyone from MS confirm?

Thank You

Garth

We should still be SQL-99 entry-level compliant since it encompasses SQL-92 standard specifications. We also support newer features like windowing functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE) and query expressions (CTE) that are part of SQL-99 specs. I don't believe that there is a topic in Books Online that covers this. I would encourage you to use the feedback link in Books Online to send this request.|||Thank you for the reply.

I sure would like to get a definitive answer as I'm trying to write about how T-SQL differs from ANSI SQL, and it would be helpful to be able to specify what level of compliance we have in 2005.

It would also be helpful to know if FIPS Flagger is going to be updated to compare against SQL-99 instead of SQL-92. Or if there is another method that can be used to ensure a given T-SQL statement is compliant with SQL-99.

Thanks for your time,

Garth|||Note that T-SQL is the procedural language extensions for SQL. It is not the same as the query language described by ANSI SQL. SQL Server however has extensions to the query language or proprietary syntax for example. AFAIK, FIPS flagger has not been updated to check for SQL-99 features. It will not be changed before RTM also. My suggestion is that you can focus on the SQL-92 syntax for DML statements and talk about the SQL-99 specific features like query expression/window functions. There are some additional improvements to referential constraints - we now support SET NULL and SET DEFAULT in addition to NO ACTION and CASCADE.

Ansi SQL92 question

I have an application that uses the old join syntax instead of the
SQL92 standards join syntax.
I need to justify changing the code to the new standard.
Is there any performance issue related to using the old syntax that
are documented?
Are there any other issues that I use to justify a code upgrade?Versions 2000 SP4 and 2005 SP1|||dunleav1 wrote:

Quote:

Originally Posted by

I have an application that uses the old join syntax instead of the
SQL92 standards join syntax.
I need to justify changing the code to the new standard.
Is there any performance issue related to using the old syntax that
are documented?
Are there any other issues that I use to justify a code upgrade?

Careful.. when you refer to "old join syntax" in this group readers will
assume you mean TSQL proprietary *= syntax for outer joins and not
"implicit join syntax". For implicit inner joins please see my answer in
c.d.ibm-db2.
If you in fact do outer joins using *= or Oracle style (+) syntax things
are different. Portability would be one reason to abandon proprietary
syntax for ANSI syntax.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab|||Yes, implicit join sytax.

I believe it's a portability issue for me accross database vendors.
It's easier to write the same sql accross vendors. But if development
want to mix and match join syntax accross vendors and products it's
fine with me. As long as it doesn't effect performance.

So for Mssql there is no performance impact for using an implict join
as opposed to the SQL92 standard outer join syntax?|||On Feb 1, 11:14 am, Serge Rielau <srie...@.ca.ibm.comwrote:

Quote:

Originally Posted by

dunleav1 wrote:

Quote:

Originally Posted by

I have an application that uses the old join syntax instead of the
SQL92 standards join syntax.
I need to justify changing the code to the new standard.
Is there any performance issue related to using the old syntax that
are documented?
Are there any other issues that I use to justify a code upgrade?

>
Careful.. when you refer to "old join syntax" in this group readers will
assume you mean TSQL proprietary *= syntax for outer joins and not
"implicit join syntax". For implicit inner joins please see my answer in
c.d.ibm-db2.
If you in fact do outer joins using *= or Oracle style (+) syntax things
are different. Portability would be one reason to abandon proprietary
syntax for ANSI syntax.
>
Cheers
Serge
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge,
I believe we are in agreement but let me give you an example about
what I referring to:
sql89: select tab1.col1 from tab1,tab2 where tab1.col1=tab2.col and
tab1.col1 >1;
sql92: select tab1.col1 from tab1 inner join tab2 on
tab1.col1=tab2.col2 where tab1.col1 >1;
Is there a performance impact using one syntax over the other?

I agree it is a good idea to not use proprietary sql extensions such
as (tab1(+) for Oracle or Mssql *=).|||dunleav1 (jmd@.dunleavyenterprises.com) writes:

Quote:

Originally Posted by

I believe we are in agreement but let me give you an example about
what I referring to:
sql89: select tab1.col1 from tab1,tab2 where tab1.col1=tab2.col and
tab1.col1 >1;
sql92: select tab1.col1 from tab1 inner join tab2 on
tab1.col1=tab2.col2 where tab1.col1 >1;
Is there a performance impact using one syntax over the other?

In SQL Server, no.

And I would find it difficult to justify to go through all code and
change it to use the newer syntax. (Note that the SQL-89 syntax is
still very much valid.)

However, I tend to rewrite into the newer syntax when I work with old
code, since I find the newer syntax much easier to read and work with.

Quote:

Originally Posted by

I agree it is a good idea to not use proprietary sql extensions such
as (tab1(+) for Oracle or Mssql *=).

If you on the other hand have lots of code with *= int, there is
all reason to rewrite it. *= is deprecated in SQL 2005, and works
only in compatibility mode 80.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>I need to justify changing the code to the new standard. Is there any performance issue related to using the old syntax that are documented? <<

Performance is not the real problem ..

Quote:

Originally Posted by

Quote:

Originally Posted by

>Are there any other issues that I use to justify a code upgrade? <<

The old OUTER JOIN syntaxes are not portable, and are being deprecated
by vendors. It does not work the same way in Sybase, SQL Server,
Oracle, Informix and Centura among other products. It is also very
limited and you will probably find that you can re-write old code to
great advantage. Here is a cut&paste on the details:

Here is how OUTER JOINs work in SQL-92. Assume you are given:

Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z

and the outer join expression:

Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserved
table" in the query. What I am going to give you is a little
different, but equivalent to the ANSI/ISO standards.

1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.

2) If the predicate tests TRUE for that row, then you keep it. You
also remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:

Let @. = passed the first predicate
Let * = passed the second predicate

Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @.
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @.
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @.* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
--------
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
--------
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
--------
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL

the final results:

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL

The basic rule is that every row in the preserved table is represented
in the results in at least one result row.

There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables

Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250

and let's do an extended equality outer join like this:

SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;

If I do the outer first, I get:

Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL

Then I apply the (qty < 200) predicate and get

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100

Doing it in the opposite order

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL

Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;

... or do it in the joining:

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;

Another problem is that you cannot show the same table as preserved
and unpreserved in the extended equality version, but it is easy in
SQL-92. For example to find the students who have taken Math 101 and
might have taken Math 102:

SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;

ANSI SQL Question

I have been googling the last couple of days hoping to find some "end-all" reference for the ANSI SQL-92 standard. I have come up with nothing.
If any of you know a site or a book that has the entire ANSI SQL-92 standard please let me know!
thanks.http://global.ihs.com/standards.cfm?currency_code=USD&customer_id=2125452A5B0A&shopping_cart_id=27242837254950344E5A5020210A&rid=Z56&country_code=US&lang_code=ENGL|||http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt|||Thanks guys, I'm sure this will help!

ANSI SQL Join syntax - multiple tables

I'm used to using the old-style JOIN syntax and performing the joins in the WHERE clause. I'm trying to figure out the ANSI syntax though for doing them in the FROM clause, and am curious if someone can help me out.

Here's the query I want:

SELECT *
FROM tableA A , tableB B , tableC C
WHERE A.col1 = B.col1
AND B.col2 = C.col2

Here is my converted ANSI (SQL-92) syntax:
SELECT *
FROM (tableA A JOIN tableB B ON A.col1 = B.col1 ) JOIN tableC C ON B.col2 = C.col2

Question:
Is my ANSI version correct (equivalent to the first query)?
Is that the only way to do it? I don't really like having to string multiple joins out with parenthesis like that.

Thanks.Sure...I would say INNER JOIN though (no that it matters)

You do know about outer joins though...right?

SELECT *
FROM tableA A
JOIN tableB B
ON A.col1 = B.col1
JOIN tableC C
ON B.col2 = C.col2|||the parentheses are definitely necessary in microsoft access, and nowhere else that i know of

i'm not sure if the parentheses will be ignored in sql server (this is the sql server forum, after all), because i've never coded them in sql server, because i know they aren't necessary

so if in doubt, leave them out, just remember to code them back in if you use microsoft access

rudy
http://r937.com/|||Originally posted by r937
the parentheses are definitely necessary in microsoft access, and nowhere else that i know of

i'm not sure if the parentheses will be ignored in sql server (this is the sql server forum, after all), because i've never coded them in sql server, because i know they aren't necessary

so if in doubt, leave them out, just remember to code them back in if you use microsoft access

rudy
http://r937.com/

Since when?

I always take them out in Access...same rules apply..esp when you duild your own sql in access...never use the qbe grid...(well maybe to select the columns...after that, it's painful...plus you can't union unless you go to the sql window...|||since when? since access 97

i dunno about later versions, perhaps the parentheses on 3+ table joins are no longer required, but they definitely were in access 97, and i ain't upgrading because i see no reason to

rudy|||I'm gonna check it out right now...didn't think so..

We're talking like SELECT a INNER join B on a.id = b.id inner join c on b.id = c.id...

right?|||yes, three tables or more, access 97 requires that you parenthesize them two at a time

select foo, bar
from (
a inner join b on a.id = b.id
)
inner join c on b.id = c.id

rudy|||Yup spent waaaay to much time with 2k...I remeber in 6.5 I think it was that way...

but for joins only...not in the predicates...

AND, Access still throws in waaaaaaaay too many parens...

You can get away with

SELECT *
FROM ((Table1 a
INNER JOIN table2 b
ON a.id = b.id)
INNER JOIN table3 c
ON b.id = c.id)

my bad...

ansi SQL help

Hello,

I have 3 tables.

orders
--
orderID PK
symbol
price
accountNumber
matchStatus
orderDate
volume
side

executions
--
executionID
symbol
price
mcName
orderDate
orderTime
volume
side

accounts
----
accountID
accountNumber
mcName

What I have so far.

select o.orderid, abs(o.volume), sum(e.volume)
from orders o, executions e, accounts a
where a.accountNumber = o.accountNumber and e.mcName = a.mcName
and o.matchStatus IS NULL and e.orderID IS NULL
and left(e.side, 1) = left(o.side, 1)
and o.orderDate = e.executionDate
and o.symbol = e.symbol
group by o.orderid, e.volume, o.volume
having sum(e.volume) = abs(o.volume)

************************************************** 8
I put this in the select statement as a debug statementm abs
(o.volume), sum(e.volume).

What I need to do is this.

I need to get the orderID from the orders table for records where all
the where statement clauses match up and the abs value of o.volume
equals the sum of v.volume for all records that match the the where
clause. It will always be one record in the orders table and will be 1 or
more records in the execution table that will match up.

How do I do this?

Right now I get 0 records because the group by isn't working to well.

here is a sample return set if I don't have the HAVING statement.

ORDERID ABS SUM
2219 434 271
2219 434 500
4125 2000 400
4125 2000 700
4125 2000 900
4129 2000 300
4129 2000 400Take e.volume out of the GROUP BY clause and you should get:

select o.orderid, abs(o.volume), sum(e.volume)
from orders o, executions e, accounts a
where a.accountNumber = o.accountNumber and e.mcName = a.mcName
and o.matchStatus IS NULL and e.orderID IS NULL
and left(e.side, 1) = left(o.side, 1)
and o.orderDate = e.executionDate
and o.symbol = e.symbol
group by o.orderid, abs(o.volume);

ORDERID ABS SUM
2219 434 771
4125 2000 2700

Then put your HAVING back in.|||Thanks worked like a charm.
Do you mind if I ask you another question?

How about if I want to to do an update statement on the returned recordset.

Something like

update executions
set orderID =
(
select o.orderid
from orders o, executions e, accounts a
where a.accountNumber = o.accountNumber and e.mcName = a.mcName
and o.matchStatus IS NULL and e.orderID IS NULL
and left(e.side, 1) = left(o.side, 1)
and o.orderDate = e.executionDate
and o.symbol = e.symbol
group by o.orderid, abs(o.volume)
having sum(e.volume) = abs(o.volume)
)
WHERE orderID IS NULL

Unfortunately my subselect returns multiple records.
What is the best way around this?|||I don't really understand what you want to do.|||I want to update executions.orderid to orders.orderid where the conditions are met.

I found my old code and it went like this.

UPDATE executions
SET orderID =
(
SELECT O.orderID
FROM orders AS O JOIN accounts AS A ON O.accountnumber = A.accountnumber
WHERE O.symbol = executions.symbol AND A.mcname = executions.mcname
AND LEFT(o.side, 1) = LEFT(executions.side, 1) AND O.orderDate = executions.executionDate
AND O.orderID in
(
select o2.orderID
from orders o2, executions ee2, accounts aa
where LEFT(o2.side, 1) = LEFT(ee2.side, 1) and o2.symbol = ee2.symbol and
aa.MCName = ee2.MCName AND aa.accountNumber = o2.accountNumber AND o2.orderDate = ee2.executionDate
AND ee2.orderID IS NULL and o2.matchStatus IS NULL
group by o2.orderID, o2.volume
HAVING SUM(ee2.volume) = ABS(o2.volume)
)
)
WHERE orderID IS NULL

update orders
set matchStatus = 1
where orders.matchStatus IS NULL AND orders.orderID IN
(
select executions.orderID
from executions
where executions.orderID IS NOT NULL
)

Ansi Sql 87

What are the differences between ANSI SQL 87 and 92. I searched through google ,didn't find any documents on this . Could you please point out to any websites .
I need the sample queries covering most of the syntaxes on ANSI SQL 87 .
Thanks
Reddythere aren't any web sites for sql 87

may i ask why you need this? what is it for?