Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Posts
    18

    Unanswered: SQL Server 2012 - Column value -1 or 0

    Hi Guys,

    If you look at the Having statement in the query below it has:
    HAVING (dbo.TITLES.Check1 = - 1) <- notice the space between - sign and 1.
    I have tried removing that space in SQL Server view designer but it keeps putting it back. The values in the DB column Check1 are either -1 or 0.

    If I try:
    dbo.TITLES.Check1 = 0)
    This pull up records with 0

    But when I try:
    dbo.TITLES.Check1 = - 1)
    This pull up nothing even though there are 2 records in there with a value of -1 in that column.

    I think it is the space between the minus sign and the number 1. But, every time I remove the space the designer puts it right back.

    In access this is basically a true or false column which is represented by a -1 for true and a 0 for false.

    Is there another way I should be checking this in SQL Server?

    I did try:
    dbo.TITLES.Check1 = True)
    But of course that tosses an error even though it works when run in access.

    SQL SERVER VERSION:

    SELECT MAX(dbo.TITLOC.TitleLocID) AS MaxOfTitleLocID, dbo.TITLES.TitleID, dbo.TITLES.CustLName, dbo.TITLES.CustFName, dbo.TITLES.RecDT,
    dbo.TITLES.TitleID AS Expr1, dbo.TITLES.Check1
    FROM dbo.TITLES INNER JOIN
    dbo.TITLOC ON dbo.TITLES.TitleID = dbo.TITLOC.TitleID
    GROUP BY dbo.TITLES.CustLName, dbo.TITLES.CustFName, dbo.TITLES.RecDT, dbo.TITLES.TitleID, dbo.TITLES.Check1, dbo.TITLES.TitleID
    HAVING (dbo.TITLES.Check1 = - 1)



    ACCESS VERSION:
    SELECT Max(TITLOC.TitleLocID) AS MaxOfTitleLocID, TITLES.TitleID, TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID, TITLES.Check1
    FROM TITLES INNER JOIN TITLOC ON TITLES.TitleID = TITLOC.TitleID
    GROUP BY TITLES.TitleID, TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID, TITLES.Check1
    HAVING (((TITLES.Check1)=True));

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I just ran a quick test, and it looks like you can have a considerable amount of whitespace between the negative sign, and the number.
    Code:
    create table #temp (col1 int)
    
    insert into #temp values (1), (0), (-1)
    
    select col1
    from #temp
    group by col1
    having col1 = -			1
    I even tossed in a few tabs to be sure, and it looks like it still works. If you remove the having clause, do you still have any negative values in the TITLES.Check1 column? They could be getting filtered out by the join.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sadly Access is a bit funny with boolean values...

    I know that it seems illogical but have you tried comparing to 1 (i.e. positive)?

    Also, have you tried to convert the value to the bit data type:
    Code:
    WHERE  Convert(bit, col1) = 1
    lastly, don't use the view designer: test your query first outside of this.
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    ---Do not use BIT flags in SQL; that was assembler.

    In access this is basically a true or false column which is represented by a -1 for true and a 0 for false.
    https://www.simple-talk.com/sql/t-sq...-of-a-problem/

    Once upon a time, long, long time ago, programming languages were tied to the hardware. If you were ones-complement or twos-complement, you would -1 or +1 for TRUE in your 1950's computers. In C#, Boolean values are 0/1 for FALSE/TRUE, whereas VB.NET has Boolean values of 0/1 for FALSE/TRUE and they are proprietary languages from the same vendor!

    The hardware affected the design of the languages. My favorite was the three-way IF in FORTRAN that looked like this:
    IF (<numeric expression>) <label 1>, <label 2>, <label 3>
    Depending on the signum (-1, 0, +1) of the numeric expression, control jumped to one of the three labels.

    The article goes into details, but the basic thing is that SQL is a predicate language that reports the current state of a schema. It is not a [I]flag/semaphore/I] model that passes messages from disjoint events.

    For example, doing a survey with "yes/no" questions starts off fine, then chokes when you need to indicate "No Answer given", "Not Applicable", "Contradicts a previous answer" and other values. Even the ISO sex codes have four values (0 = unknown, 1 = male, 2 = female, 9 = lawful person. such as a corporation).

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Celko View Post
    long, long time ago
    Eh, not so much... The practice is still QUITE alive and well, thank you!

    As long as people find new ways to approach problems (F# or R would be great examples), they will find "interesting" ways to recreate some old problems like "How do you represent True and False in this particular language?" Since there is little or no chance that people will standardize, and I'm very strongly in favor of innovation, I'll gladly live with these minor inconsistencies.

    One of the really great things about standards is that if you don't like any of the existing standards, create a new standard! This seems to be the expectation among innovators, so it is tolerated by those of us that need those innovative tools to get work done.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    appreciating that this question is in the SQL server thread in the Access and VB world you should be using true or false
    VBA defines constants vbtrue and vbfalse precisley so you as the developer don't have to worry about the actual implementation on the specific SQL paltform your code is meant to run on.
    Access further muddies the waters by referrign to yes/no columns.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    Even the ISO sex codes have four values (0 = unknown, 1 = male, 2 = female, 9 = lawful person. such as a corporation).
    I've come to the conclusion that these days gender should be stored as floating point.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •