Re: Using expressions in a SELECT Clause

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 02/16/04 

Date: Tue, 17 Feb 2004 00:45:43 +0100

Watch out for this. The handling of datatype conversion is different in 7.0
and 2000. Code can break when moved from 7.0 to 2000 if you rely on this! 

-- 
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"William Morris" <news.remove.this.and.the.dots@seamlyne.com> wrote in
message news:c0qp0m$19s4s2$1@ID-205671.news.uni-berlin.de...
Although, VB will evaluate a 1 the same as a -1 in boolean terms.
  "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:uY43YIK9DHA.1592@TK2MSFTNGP10.phx.gbl...
  You should be aware that Booleans are not treated the same way throughout
industry.  For example, IIRC, VB stores a True as -1 while Perl treats it as
1.  Using literals avoids the problem.  Also, bit datatype are not ANSI
standard, so some code could break.
  -- 
  Tom
  ---------------------------------------------------------------
  Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
  SQL Server MVP
  Columnist, SQL Server Professional
  Toronto, ON Canada
  www.pinnaclepublishing.com/sql
  "Eric Gnacadja" <wegnac@hotmail.com> wrote in message
news:#bzQzCK9DHA.2572@TK2MSFTNGP09.phx.gbl...
  Sorry for my poor english.
  I found your solution really nice and wanted to add just the cast to get a
bit. I think the user who started the thread wanted to get a boolean.
  It's nether the last big discovery of the humankind, nor something really
different from your solution, just something more which could help.
  That's what I tried to express, translating a common french expression.
  Sorry again,
    "Tom Moreau" <tom@dont.spam.me.cips.ca> a écrit dans le message de
news:ecJQ$6J9DHA.488@TK2MSFTNGP12.phx.gbl...
    I guess I don't understand what you mean by "A little bit forward".
    -- 
    Tom
    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql
    "Eric Gnacadja" <wegnac@hotmail.com> wrote in message
news:#K15q3J9DHA.2752@TK2MSFTNGP09.phx.gbl...
    Nice Tom,
    Nice and simple...
    A little bit forward:
    SELECT EmployeeID
    , cast((case when 4 > 3 then 1 else 0 end ) as bit) As Value
    FROM Northwind.dbo.Employees
    Eric
    "Tom Moreau" <tom@dont.spam.me.cips.ca> a écrit dans le message de
    news:O4Kf6jJ9DHA.3200@TK2MSFTNGP09.phx.gbl...
    Try:
    SELECT EmployeeID
    , case when 4 > 3 then 'True' else 'False' end As Value
    FROM Northwind.dbo.Employees
    -- 
    Tom
    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql
    "Shawn Melton" <anonymous@discussions.microsoft.com> wrote in message
    news:7F6A6B4C-BFB1-40AB-9A39-D5FBE0245E22@microsoft.com...
    According to SQL Server documentation:
    I can use any expression in a SELECT clause.
    An expression can consist of two constants separated by a comparison
    operator.
    Yet when I try this:
    SELECT EmployeeID, 4>3 As Value FROM Northwind.dbo.Employees
    I get the error:
    Line 1: Incorrect syntax near '>'.
    Does anyone know why?  What I would expect is a two column resultset
with a
    column named Value that is set to false.  This is a simplification of
what I
    need but should answer the question.  What I really need is to compare a
    field to a string constant to create a new boolean field.

Relevant Pages

  • Re: Using expressions in a SELECT Clause
    ... SQL Server MVP ... Columnist, SQL Server Professional ... I think the user who started the thread wanted to get a boolean. ... field to a string constant to create a new boolean field. ...
    (microsoft.public.sqlserver.programming)
  • Re: Error "SQL Server does not allow remote connections"
    ... The application could not connect to the sql server db. ... network is blocking the connection. ... SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 ... integratedSecurity, SqlConnection owningObject) +737554 ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Sql 2005: SSIS - How to convert "N/Y" CHAR(1) to byte type column
    ... below) to represent boolean types. ... i.e. an expression that takes a charconstruction_weld column value from the flat file and converts it to a bit type that is compatible with the construction_weld column in the wl_well_casing_liner table. ... > Before we go further on the issue, I'd like to know which data type you> want to use in SQL server, do you use any tinyint? ... I'm totally unfamiliar the grammar of SSIS expressions> and I ...
    (microsoft.public.sqlserver.dts)
  • Re: Custom membership provider problem
    ... SQL server 2005 does not install with the TCP/IP protocol turned on. ... >An error has occurred while establishing a connection to the server. ... 25 - Connection string is not valid) ... >Boolean& failoverDemandDone, String host, String failoverPartner, String ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Select statement for boolean and/or DateTime values?
    ... The above works for an SQL Server, means reader contains the row I'm ... SQL Server does not have a boolean datatype, and even if it did, ... Access DOES have a boolean datatype, but, it requires a nonstandard ... Again, if you utilize parameters, values will be passed correctly to the Jet ...
    (microsoft.public.data.ado)