Professional Web Applications Themes

Need help formatting syntax for SELECT query on a Bittype data field - Coldfusion Database Access

:confused; I have been trying to build a SQL statement in my coldfusion page that queries our SQL Server database for a bunch of records. I want to eliminate a subset of those records based on the value of a field (the field type in SQL Server is Bit). I have tried to compare the returned value to 0, '0', '%0%' etc. I cannot seem to find the right syntax to compare it as a true boolean (i.e., d.fieldname is TRUE). This is driving me nuts because I am sure it is something really simple. I cannot find anything online ...

  1. #1

    Default Need help formatting syntax for SELECT query on a Bittype data field

    :confused; I have been trying to build a SQL statement in my coldfusion page
    that queries our SQL Server database for a bunch of records. I want to
    eliminate a subset of those records based on the value of a field (the field
    type in SQL Server is Bit). I have tried to compare the returned value to 0,
    '0', '%0%' etc. I cannot seem to find the right syntax to compare it as a true
    boolean (i.e., d.fieldname is TRUE).

    This is driving me nuts because I am sure it is something really simple. I
    cannot find anything online about how ColdFusion/SQL makes that boolean value
    validation.

    Any help is most appreciated, the code is below d.field3 is the Bit type field.

    SELECT d.field1, d.field2, d.field3, dt.doctype, s.section
    FROM doent d
    INNER JOIN DoentTypes dt ON (d.doctype = dt.doctype)
    INNER JOIN Section s ON (d.section = s.section)
    WHERE d.field2 LIKE '%data1%'
    OR d.field2 LIKE '%data2%'
    OR d.field2 LIKE '%data3%'
    AND d.field3 = 0

    -ca-nimrod

    ca_nimrod Guest

  2. #2

    Default Re: Need help formatting syntax for SELECT query on aBit type data field

    You do have the correct syntax to compare a bit field

    where bit field = 1
    where bit field = 0

    Are you getting an error, if so what is it ?

    If your not getting the results you expect, break the query down. I would
    suggest trying without the LIKE's first
    SELECT d.field1, d.field2, d.field3, dt.doctype, s.section
    FROM doent d
    INNER JOIN DoentTypes dt ON (d.doctype = dt.doctype)
    INNER JOIN Section s ON (d.section = s.section)
    WHERE d.field3 = 0

    See if this returns the correct result set.

    Ken


    The ScareCrow Guest

  3. #3

    Default Re: Need help formatting syntax for SELECT query on aBit type data field

    As stated by Ken, the syntax, as you posted it, is correct.

    However, problems arise in CF when the bit comparison is against a CF variable.
    EG AND d.field3 = #SomeYesNoVar#

    In that case, you would use <CFQUERYPARAM> (which you should be doing anyway).

    Or is the issue that your query works but returns the wrong results?

    It looks like you might have wanted:
    WHERE
    ( d.field2 LIKE '%data1%'
    OR d.field2 LIKE '%data2%'
    OR d.field2 LIKE '%data3%'
    )
    AND d.field3 = 0



    MikerRoo Guest

Similar Threads

  1. Select a list of items into an aliased field when doinga select
    By ehaemmerle in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 18th, 10:49 PM
  2. formatting and syntax
    By Michael S. Robeson II in forum PERL Beginners
    Replies: 7
    Last Post: February 11th, 06:38 PM
  3. Data field formatting
    By RIDVAN AKDEMIR in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: October 3rd, 02:08 PM
  4. SELECT: Syntax error. Please help
    By sam in forum PHP Development
    Replies: 14
    Last Post: September 29th, 10:32 PM
  5. Help with Select Syntax
    By Chaster in forum Microsoft Access
    Replies: 1
    Last Post: July 22nd, 10:24 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139