Professional Web Applications Themes

Need to Identify nulls in a CF Query - Coldfusion - Advanced Techniques

Trying to de-dupe a SQL table (that I ed up). So a DISTINCT query, followed by a cfloop containing a cfquery INSERT should get me a new de-duped version. However, the original table contains some nulls. I want to detect these and replace with zeroes in the cfloop before doing the insert. I cannot find a way to detect the null values. ((not var gte 0) AND (not var lt 0)) doesn't do it. Nor anything else I've tried. Does anyone have any advice or pointers please?...

  1. #1

    Default Need to Identify nulls in a CF Query

    Trying to de-dupe a SQL table (that I ed up). So a DISTINCT query,
    followed by a cfloop containing a cfquery INSERT should get me a new de-duped
    version. However, the original table contains some nulls. I want to detect
    these and replace with zeroes in the cfloop before doing the insert. I cannot
    find a way to detect the null values. ((not var gte 0) AND (not var lt 0))
    doesn't do it. Nor anything else I've tried. Does anyone have any advice or
    pointers please?

    garthsenior Guest

  2. #2

    Default Re: Need to Identify nulls in a CF Query

    garthsenior wrote: 

    have you tried <cfif NOT trim(len(columnName))>?
    PaulH Guest

  3. #3

    Default Re: Need to Identify nulls in a CF Query

    If your db has an ifnull, coalesce or similar function, use it. If not, nulls
    appear as empty strings in a cfquery.

    Originally posted by: garthsenior
    Trying to de-dupe a SQL table (that I ed up). So a DISTINCT query,
    followed by a cfloop containing a cfquery INSERT should get me a new de-duped
    version. However, the original table contains some nulls. I want to detect
    these and replace with zeroes in the cfloop before doing the insert. I cannot
    find a way to detect the null values. ((not var gte 0) AND (not var lt 0))
    doesn't do it. Nor anything else I've tried. Does anyone have any advice or
    pointers please?



    Dan Guest

  4. #4

    Default Re: Need to Identify nulls in a CF Query

    You may not even need a cfloop unless there is some extra processing you need
    to do. As Dan mentioned most databases have a function that allows you to
    return a specific value if the column is null, so try using that function in a
    query, and do something like a:

    INSERT INTO newTable (column1, column2, column3, ....)
    SELECT DISTINCT column1, COALESCE(myNumericColumn2, 0), column3, etc...


    Most databases have the generic function is COALESCE, IsNull() for sql server
    and I think NVL() for oracle, etc.


    mxstu Guest

  5. #5

    Default Re: Need to Identify nulls in a CF Query

    Whenever I identify null fields I use the syntax

    WHERE id=''

    Just use two empty quotes.
    Phox68 Guest

  6. #6

    Default Re: Need to Identify nulls in a CF Query

    Phox68,

    That would find empty strings. A null is different than an empty string "".

    mxstu Guest

  7. #7

    Default Re: Need to Identify nulls in a CF Query

    I have the MS SQL data hosted on a remote CF server. To get rid of the
    duplicates I programmed:
    cfquery name='Alldata' select * from tbl
    cfquery name='Data' select distinct * from tbl
    cfquery delete from tbl
    cfloop query='Data' copy every field value into a var, then do an insert
    into tbl (all_fields) values (all_var_values)

    The insert fails (obviously) when any var has been loaded from a query row
    that contains a NULL. I tried everything I could think of to test for NULL and
    replace with zero before doing the insert. Nothing worked! e.g. I used <cfset
    xheight = iheight> (where iheight is a column name in the query). I then tried
    <cfif len(trim(xheight)) = 0 > <cfif ((not xheight gte 0 ) and (not xheight
    lt 0 )) and several other variations. What I need is <cfif xheight is null >
    but, of course, that's not valid.

    Thanks for helping.


    garthsenior Guest

  8. #8

    Default Re: Need to Identify nulls in a CF Query

    garthsenior,

    If you're using MS SQL, why do this in a cfloop? Using #temp tables would
    seem much easier. Select everything into a temp table (using coalesce, etc),
    update the data if necessary, and then truncate the main table and re-insert
    the de-duped data.







    mxstu Guest

Similar Threads

  1. concat with nulls
    By rmorgan in forum Coldfusion Database Access
    Replies: 3
    Last Post: November 13th, 03:12 PM
  2. NULLS first ...
    By Ralph in forum MySQL
    Replies: 4
    Last Post: November 7th, 02:00 AM
  3. Query to identify and change uppercase data
    By MikeyJ in forum Coldfusion Database Access
    Replies: 5
    Last Post: August 18th, 12:20 PM
  4. Casting nulls
    By Carl Furst in forum PHP Development
    Replies: 3
    Last Post: September 24th, 11:39 PM
  5. dealing with nulls....
    By Ken Schaefer in forum ASP
    Replies: 3
    Last Post: July 7th, 05:49 AM

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