sql server programming: Help with subquery syntax -- sql server programming

Re: Help with subquery syntax

Aaron Bertrand - MVP
11/18/2003 9:01:00 AM

sql server programming:

[quoted text, click to view]

There is no BOOLEAN datatype in SQL Server. In other words, you can not say
"WHERE someColumn" and have it implicitly understand that you meant "WHERE
someColumn IS TRUE"...

I think you meant:

WHERE tblFeeTypes.Active = 1)

(You don't need triple parens around a condition...)

Re: Help with subquery syntax

Aaron Bertrand - MVP
11/18/2003 10:00:09 AM

[quoted text, click to view]

I'm not sure why you think that returning a column in a SELECT statement and
checking its value in a WHERE clause should have the same syntax?

SELECT Active FROM table WHERE Active = 1

Why would you say:

SELECT Active = 1 FROM table WHERE Active = 1

You've defeated the purpose of storing the data in the table, if you're
going to override it anyway...

Re: Help with subquery syntax

Aaron Bertrand - MVP
11/18/2003 10:00:46 AM

[quoted text, click to view]

Roji's point is that you're not getting any data out of sub2 anyway, so who
cares whether the rows are there are not.

Re: Help with subquery syntax

Louis Davidson
11/18/2003 12:13:08 PM

Because Active = 1 is a valid expression in many languages. It evaluates to
True. T-SQL doesn't believe in such things, as we old-timers know :)

--
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

[quoted text, click to view]

Re: Help with subquery syntax

Aaron Bertrand - MVP
11/18/2003 1:26:07 PM

So, you're saying the OP wants

SELECT TRUE FROM table WHERE Active = 1

? I'm still not understanding the point...

[quoted text, click to view]

Re: Help with subquery syntax

Jacco Schalkwijk
11/18/2003 2:39:58 PM

You are right Roji, SQL Server doesn't have a Boolean datatype, so you have
to explicitly compare the column with a value.

--
Jacco Schalkwijk
SQL Server MVP

[quoted text, click to view]

Re: Help with subquery syntax

Warnat
11/18/2003 2:56:22 PM

hi,

the solution is simple:
the SQL - Server doesn't understand
(ShowInAllCentres), (tblFeeTypes.Active) and (ShowOnAllRolls)
use
(ShowInAllCentres<>0), (tblFeeTypes.Active<>0) and (ShowOnAllRolls<>0)
instead, and everything is fine.
In the WHERE-clause only boolean - EXPRESSIONS's are allowed (only
JET/Access
assumes, a boolean column could be a boolean expression itself).

r.w.

<paul@palmnospam.com> schrieb im Newsbeitrag
news:OWfpchdrDHA.3320@tk2msftngp13.phx.gbl...
[quoted text, click to view]

Re: Help with subquery syntax

Tibor Karaszi
11/18/2003 2:59:25 PM

One thing I can see immediately is that you need to name the derived table. You have (slightly
reformatted for readability):

[quoted text, click to view]
(
....
[quoted text, click to view]
)

That last part has to be something like:
[quoted text, click to view]
(
....
[quoted text, click to view]
) AS t

You then refer to the result from this query with the table name "t".

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver

[quoted text, click to view]

Re: Help with subquery syntax

Louis Davidson
11/18/2003 4:17:47 PM

Oh, yeah, that is kindof strange. I misunderstood your point. Sorry :)

--
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

[quoted text, click to view]

Re: Help with subquery syntax

Roji. P. Thomas
11/18/2003 7:33:35 PM

WHERE (((tblFeeTypes.Active))))
This might be the line causing the
syntax error. I believe that you have to explicitly
check the value there.

But one doubt.
When all you are doing is selecting Sub1.*
does having a LEFT Join makes sense?

HTH
roji

[quoted text, click to view]

Help with subquery syntax

paul NO[at]SPAM palmnospam.com
11/18/2003 11:32:08 PM

I'm having problems getting a particular query to work. It works ok against
JET / Access, but gives several syntax errors in the Query Analyzer. It's
supposed to return results from two subqueries (Sub1 and Sub2) linked by an
outer join.

SELECT Sub1.* FROM

(SELECT tblFeeTypes.FeeTypeID, tblFeeTypes.ShowOnAllRolls,

tblFeeTypes.ShowInAllCentres, tblFeeTypes.ServiceID,

lnkFeeTypeFeeVariant.RollLetter, tblFeeVariants.FeeVariantID

FROM tblFeeTypes

INNER JOIN (tblFeeVariants INNER JOIN

lnkFeeTypeFeeVariant ON tblFeeVariants.FeeVariantID =
lnkFeeTypeFeeVariant.FeeVariantID)

ON tblFeeTypes.FeeTypeID = lnkFeeTypeFeeVariant.FeeTypeID

WHERE (((tblFeeTypes.Active)))) AS Sub1

LEFT JOIN

(SELECT lnkFeeTypeToRoll.RollType, lnkFeeTypeToRoll.FeeTypeID

FROM lnkFeeTypeToRoll

WHERE (((lnkFeeTypeToRoll.RollType)=8))) AS Sub2

ON Sub1.FeeTypeID = Sub2.FeeTypeID

WHERE ((((ShowOnAllRolls) Or (Sub2.FeeTypeID Is Not Null))) AND
(((ServiceID=5) Or

(ShowInAllCentres))))

ORDER BY Sub1.FeeVariantID, Sub1.FeeTypeName;

Thanks,

Paul

Re: Help with subquery syntax

Aaron Bertrand [MVP]
11/18/2003 11:33:39 PM

[quoted text, click to view]

Uh oh. This might be a Celko trigger!

CREATE TRIGGER WakeCelko
ON microsoft.public.*
FOR INSERT /* post to group */
AS
IF (CHARINDEX('bit', post) > 0 AND CHARINDEX('boolean', post) > 0)
OR (CHARINDEX('row', post) > 0)
OR (CHARINDEX('column', post) > 0)
OR (CHARINDEX('@tablename', post) > 0)
OR (CHARINDEX('rank', post) > 0)
BEGIN
EXEC dbo.WakeCelkoAndPrepareStick()
END

:-))))

Re: Help with subquery syntax

paul NO[at]SPAM palmnospam.com
11/19/2003 12:36:10 AM

Thanks for the replies, they were spot on!

Re: Help with subquery syntax

paul NO[at]SPAM palmnospam.com
11/19/2003 12:44:42 AM

Thanks. I'm definitely floundering the boolean logic in T-SQL. So I can
test for say Active=1 in a WHERE clause, but can't return Active=1 as a
SELECT column, but I can return Active as a SELECT column, but not provide
it as a criteria in a WHERE clause.

[quoted text, click to view]

Re: Help with subquery syntax

paul NO[at]SPAM palmnospam.com
11/19/2003 12:48:49 AM

Hi Roji,

Thanks, that was the problem. I do need the LEFT JOIN though because not
all of the records in Sub1 are in Sub2.

Kind Regards,

Paul

[quoted text, click to view]

RE: Help with subquery syntax

v-yshao NO[at]SPAM online.microsoft.com
11/19/2003 4:02:58 AM

Hi Paul,

Thanks for your post. It seems that you have found the problem in your
query. I'm including some supplemental information for your reference. In
Access, we are able to apply the ANSI-92 SQL query mode to a query via
setting the SQL Server Compatible Syntax option, which is compatible with
Microsoft SQL Server.

To set the SQL Server Compatible Syntax option in Access, please view these
steps.
Tools-->Options-->Tables/Queries tab-->SQL Server Compatible Syntax option.

Please feel free to post in the group if this solves your problem or if you
would like further assistance.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Re: Help with subquery syntax

Tibor Karaszi
11/19/2003 8:47:43 AM

LOL!

But surely you meant to say "record" and "field" instead of "row" and "column"? :-)

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver

[quoted text, click to view]

Re: Help with subquery syntax

Tibor Karaszi
11/19/2003 8:50:28 AM

[quoted text, click to view]

But what about the 3:rd truth state? "Is 32 = NULL?", which evaluates to UNKNOWN. So you now have a
datatype which should accept TRUE, FALSE, UNKNOWN, and of course all datatypes should accept NULL.
This complexity was what made ANSI not include a Boolean datatype in SQL-92. They did in SQL:1999,
and ignored the difference there between UNKNOWN and NULL. See old posts from Joe Celko on the
topic.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver

[quoted text, click to view]

Re: Help with subquery syntax

paul NO[at]SPAM palmnospam.com
11/19/2003 10:33:23 AM

Yes, well I guess that's the problem. To me that doesn't look strange at
all, in fact I do it quite a lot in Access. It would just make things a
little tidier if there was an assumed equivalency between the bit data type
and booleans.

Kind Regards,

Paul

[quoted text, click to view]

Re: Help with subquery syntax

paul NO[at]SPAM palmnospam.com
11/19/2003 10:48:55 AM

No, but as part of the criteria for rows selected from Sub1, I'm checking
for the presence of a value in Sub2. Without the LEFT JOIN no values in Sub1
that aren't in Sub2 would make it through. Omitting Sub2 altogether would
allow all values through in Sub1, but under certain conditions I don't want
values in Sub1 that aren't in Sub2 to be returned.

WHERE ((((ShowOnAllRolls<>0) Or (Sub2.FeeTypeID Is Not Null))) AND
(((Sub1.ServiceID=5) Or (Sub1.ShowInAllCentres<>0))))

[quoted text, click to view]

Re: Help with subquery syntax

Louis Davidson
11/19/2003 12:07:15 PM

I cannot get those out of my head lately. I am in some training for a piece
of call center software and every time she says field and record I want to
scream "NO, NO, row and column!"

--
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se>
[quoted text, click to view]

Re: Help with subquery syntax

paul NO[at]SPAM palmnospam.com
11/20/2003 8:05:23 AM

Hi Michael,

Thanks for the reply. I have tried ticking this before, but my queries
were still not SQL Server compatible. For instance, with this ticked
(Access 2003), a simple delete query in the designer still comes out as:

DELETE tblDDRun.*
FROM tblDDRun;

And a select query:

SELECT tblContacts.ContactID, Accounts<>0 AS Test
FROM tblContacts;

Also works, both of which aren't ANSI-92 compliant?

Kind Regards,

Paul

[quoted text, click to view]

Re: Help with subquery syntax

v-yshao NO[at]SPAM online.microsoft.com
11/20/2003 9:49:28 AM

Hi Paul,

Thanks for your feedback. I apologize for confusion. I mean that selecting
to enable ANSI-92 SQL query mode so we can run queries using ANSI-92 SQL
syntax. This mode is compatible with Microsoft SQL Server.

Also, do the community members answers address your problem? please feel
free to let me know if you need further assistance.

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Re: Help with subquery syntax

paul NO[at]SPAM palmnospam.com
11/20/2003 10:23:46 PM

Hi Michael,

Ok, got it. That's pretty cool, it will save me some time testing that I
have correctly arrived at a syntax that will work with both Access and SQL
(a very elusive goal). Is there a way to instruct the JETOLEDB driver to
also do this, or is it some preprocessing unique to Access?

Just one small observation; if I make any modifications to my query in
design view, it converts the entire query back to jet format. It would be
great if the designer could also operate in ANSI 92 syntax.

Yes, the community people have been very helpful in answering my question,
they'll do you out of a job!

Kind Regards,

Paul

[quoted text, click to view]

Re: Help with subquery syntax

v-yshao NO[at]SPAM online.microsoft.com
11/21/2003 6:10:55 AM

Hi Paul,

Thanks for your feedback. Based on my experience, it seems that it is
impossible to translate the SQL statements automatically using Jet engine
or Access. We have to do the translation of SQL statements from Access to
SQL Server manually. If you have any problems regarding translation in
future, please feel free to post in the newsgroup and the community members
and I will be glad to work with you.

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! ¨C www.microsoft.com/security
This posting is provided ¡°as is¡± with no warranties and confers no
rights.