with a JOIN that should be working but isn't.If I run the same SQL statement in MS Access (same data) I get the join
results I expect. If I run the SQL statement in Enterprise Manager I get a
different result (again same data).
I thought both are supposed to be ANSI compliant?
What I'm trying to accomplish is to force return results even if one table
(FS) doesn't return any records:
SELECT FS.CR, S.SN, S.SID
FROM FS RIGHT OUTER JOIN
S ON FS.SID = S.SID
WHERE (FS.DDate = '4/30/2004')
I'm expecting to see one record with SN and SID values populated and CR
null -- the same query in MS Access produces the expected result, but the
same view in MS SQL 2000 returns NO records.
(true field name and table names have been removed to protect the innocent)
;)
Any suggestions?
Rob.>> I thought both are supposed to be ANSI compliant? <<
ACCESS ain't even close to standards and the underlying model is a file
system. That is why they use the words "record" and "field" instead of
rows and columns.
Since the WHERE clause is applied last, that is probably the culprit.
Did you consider using ISO-8601 dates, as required by Standard SQL? Is
ddate a temporal data type?
WHERE FS.ddate = '2004-04-30';|||> WHERE FS.ddate = '2004-04-30';
Actually Joe, in SQL Server, '20040430' is much safer than the (admittedly,
more sensible and legible) yyyy-mm-dd format.
A|||You've referenced the unpreserved table in the WHERE clause, which
effectively turns your outer join into an inner join. Also, your date
format isn't necessarily reliable in SQL Server. Try:
SELECT FS.cr, S.sn, S.sid
FROM FS
RIGHT OUTER JOIN S
ON FS.sid = S.sid
AND FS.ddate = '20040430'
Personally, I would prefer to turn this around as a LEFT join:
SELECT FS.cr, S.sn, S.sid
FROM S
LEFT OUTER JOIN FS
ON FS.sid = S.sid
AND FS.ddate = '20040430'
I think it's fair to say that LEFT outer joins are used much more often
than RIGHT and perhaps one intuitively expects to see the preserved
table specified first. Maybe that confusion is what caused you to
reference FS in the WHERE clause?
Access's basic syntax differs from ANSI SQL92 in several ways. Don't
expect Access and SQL Server results to correspond every time.
David Portas
SQL Server MVP
--|||> Actually Joe, in SQL Server, '20040430' is much safer than the
(admittedly,
> more sensible and legible) yyyy-mm-dd format.
Indeed. On my (German) server, this is interpreted as yyyy-dd-mm. Therefore,
I prefer to use the also safe (and legible) ODBC version: {d 'yyyy-mm-dd'}
Axel|||> Indeed. On my (German) server, this is interpreted as yyyy-dd-mm.
> Therefore,
> I prefer to use the also safe (and legible) ODBC version: {d 'yyyy-mm-dd'}
But I don't know if you will always be able to rely on SQL Server internally
processing that syntax correctly.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||If I'm not mistaken, in SQL Server (per a Kalen Delaney article from some ti
me
ago), if you use the following format: yyyymmdd SQL will always interpret th
at
correctly regardless of the collation or regional settings. Note that this
format has no dashes.
Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Ozy7TI4PFHA.2788@.TK2MSFTNGP09.phx.gbl...
> But I don't know if you will always be able to rely on SQL Server internal
ly
> processing that syntax correctly.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>|||Yes, that's the format I recommended. What I was commenting on was {d
'yyyy-mm-dd'}
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:OKO2Jw4PFHA.2252@.TK2MSFTNGP15.phx.gbl...
> If I'm not mistaken, in SQL Server (per a Kalen Delaney article from some
> time ago), if you use the following format: yyyymmdd SQL will always
> interpret that correctly regardless of the collation or regional settings.
> Note that this format has no dashes.|||Some additional information, in case anyone is interested...
That format (ODBC escape clause format) is still in BOL for SQL Server 2005.
My guess is that it
will be around for a while. Personally, I'm not too found of it. It was intr
oduced in ODBC to allow
ODBC to catch the string and convert into a format that the DBMS will unders
tand. As of 7.0, it was
added to the DBMS. Try passing it using ISQL against 6.5 and 7.0 to see the
difference. AFAIK, both
ODBC and OLEDB are supposed to convert these escape clauses, I have no idea
whether ADO.NET catches
ODBC-style escape clauses.
I prefer unseparated (as most do). I wish that MS would make the ANSI SQL fo
rmat unambiguous, but I
doubt that will happen in the upcoming 2.4 versions (if at all).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%230pyPH5PFHA.1932@.tk2msftngp13.phx.gbl...
> Yes, that's the format I recommended. What I was commenting on was {d 'yy
yy-mm-dd'}
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:OKO2Jw4PFHA.2252@.TK2MSFTNGP15.phx.gbl...
>|||> I wish that MS would make the ANSI SQL format unambiguous
Me too. To me it's rather irritating that it is so hard to find an
internationally constant DATETIME literal format in SQL Server enabling me
to write software compiling INSERT statements writing a date value into a
DATETIME column.
> I prefer unseparated (as most do).
From my perspective I dislike the unseparated format. It's hard to read and
it's even harder to find typing errors in it giving me likely a hard time to
find errors that don't even exist.
Axel Dahmen
No comments:
Post a Comment