Omitting blank/null records from a query? (Coldfusion)

Question

I am currently trying to find a way to omit any results from a search where the field is blank. The simple method would be to use a <cfif len(example) neq 0>#example#</cfif> but this then means my query is pulling out results unnecessarily.

<cfquery datasource="#datasource#" name="search">
    <cfif URL.for eq "names">
        Select artist_id, artist_name
        From artists
        Where approved = 1
        And active = 1
        <cfif StructKeyExists("URL.search")>
            <cfif URL.find eq "contains">
                And name LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%">
            <cfelseif URL.find eq "matches">
                And name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#URL.search#">
            </cfif>
        </cfif>
        Order By name Asc

    <cfelseif URL.for eq "locations">
        <cfif URL.sort eq "town-az" or URL.sort eq "town-za">
            Select Distinct town
        <cfelseif URL.sort eq "county-az" or URL.sort eq "county-za">
            Select Distinct county
        <cfelseif URL.sort eq "country-az" or URL.sort eq "country-za">
            Select Distinct country
        </cfif>
        From artists
        Where approved = 1
        And active = 1
        <cfif StructKeyExists("URL.search")>
            <cfif URL.find eq "contains">
                <cfif URL.sort eq "town-az">
                    And town LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%">
                    Order By town Asc
                <cfelseif URL.sort eq "town-za">
                    And town LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%">
                    Order By town Desc
                <cfelseif URL.sort eq "county-az">
                    And county IS NOT NULL
                    And county LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%">
                    Order By county Asc
                <cfelseif URL.sort eq "county-za">
                    And county IS NOT NULL
                    And county LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%">
                    Order By county Desc
                <cfelseif URL.sort eq "country-az">
                    And country LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%">
                    Order By country Asc
                <cfelseif URL.sort eq "country-za">
                    And country LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%">
                    Order By country Desc
                </cfif>

            <cfelseif URL.find eq "matches">
                <cfif URL.sort eq "town-az">
                    And town = <cfqueryparam cfsqltype="cf_sql_varchar" value="#URL.search#">
                    Order By town Asc
                <cfelseif URL.sort eq "town-za">
                    And town = <cfqueryparam cfsqltype="cf_sql_varchar" value="#URL.search#">
                    Order By town Desc
                <cfelseif URL.sort eq "county-az">
                    And county IS NOT NULL
                    And county = <cfqueryparam cfsqltype="cf_sql_varchar" value="#URL.search#">
                    Order By county Asc
                <cfelseif URL.sort eq "county-za">
                    And county IS NOT NULL
                    And county = <cfqueryparam cfsqltype="cf_sql_varchar" value="#URL.search#">
                    Order By county Desc
                <cfelseif URL.sort eq "country-az">
                    And country = <cfqueryparam cfsqltype="cf_sql_varchar" value="#URL.search#">
                    Order By country Asc
                <cfelseif URL.sort eq "country-za">
                    And country = <cfqueryparam cfsqltype="cf_sql_varchar" value="#URL.search#">
                    Order By country Desc
                </cfif>
            </cfif>
        </cfif>
    </cfif>
</cfquery>

The town and country fields are mandatory fields upon signing up, so these fields are no issue, however I have not made the county/state field a mandatory field, so values will be NULL if a user does not enter a county or state. As stated above, I could simply use the len() method to only display results that have characters, but this means my query is pulling NULL results which it does not need to pull out. I have already included AND county IS NOT NULL but this seems to make no difference at all. Would anybody be able to point me in the right direction? Thanks.

can you show a sample result set? Are the fields entered as NULL or blank values into the database? If they're NULL then `IS NOT NULL` is the correct syntax. — Matt Busche, May 07 '13 at 12:58
Are you certain that they're null and not just defaulted to blank? — Joe C, May 07 '13 at 12:59
Entered as NULL, I have it so if the field is left blank when they sign up it does not enter any data whatsoever, and have also set it so when they update it, if the field is still blank it simply updates the value to be NULL. — Banny, May 07 '13 at 13:00
if "and not county is null" is not working for you, your probably storing the value and an empty string. you would need to examine how your data is being added and make sure you are using NULL instead of a blank string. Next, you should examine cfswitch instead of doing the cfif/cfelseif.... last instead of having 1 query that could possibly pull 3 or 4 different record sets... maybe split each recordset into its own query. — steve, May 07 '13 at 13:00
This won't make any difference to your problem but it's better to use `` instead of `` — duncan, May 07 '13 at 13:03
I can assure you that those fields are NULL and not blank, i made sure of this across all my tables. And yeah i will be changing to either a cfswitch or use an array to control it instead as the array can be used elsewhere. — Banny, May 07 '13 at 13:03
May i ask the difference between "IsDefined" and "StructKeyExists"? — Banny, May 07 '13 at 13:04
`isDefined` looks through all scopes (variables, url, form, etc) before it can say it doesn't exist. `structKeyExists` looks in one scope only. `IsDefined("URL.search")` first looks for `variables.URL.search` and then looks for `URL.search`. — Matt Busche, May 07 '13 at 13:05
Because of the `Select Distinct` it only pulls one up, but it appears at the top of my results because I have ordered them alphabetically. And to give you an idea i would get for example '', 'Sussex', 'Yorkshire' and so on so forth.. — Banny, May 07 '13 at 13:06
if the fields are NULL then AND county is NOT NULL would remove those fields. Can you post the actual query that is being ran and a result set? — Matt Busche, May 07 '13 at 13:09
@MattBusche thanks for clearing that up, I will start using that in future. Is it best practice to do that in all instances where i use `IsDefined`? — Banny, May 07 '13 at 13:10
@LeeB Yes, there's almost no reason to use `isDefined()` (i'd make exceptions when looking for something like `xml.key.key2.key3.key4` or something deeply nested, but other than that almost no reason to use it. — Matt Busche, May 07 '13 at 13:11
@MattBusche Right i'll keep that in mind :) thanks for making me aware of that. In regards to my post, i've just checked over the database again for the `county` field and all results are either a value or NULL, no fields are blank strings. I have also checked my query over aswell as the query it loops to find bands in each area to see if using `county IS NOT NULL` made a difference but it is still displaying the blank result. — Banny, May 07 '13 at 13:13
Wait, i've just clicked on. The query is built so that if no search query is provided, it pulls all results. I have (naively) included the `AND county IS NOT NULL` within the area that checks if the search term exists. Problem solved, I need to have one that checks outside instead of inside. — Banny, May 07 '13 at 13:17

score 1 · Answer 1 · edited May 09 '13 at 01:49

It was in the end a trivial mistake, however it is worth pointing out to anyone who does find a similar issue, if you add extra statements based upon variables existing, check they are not required at all levels of the query.

SELECT DISTINCT county
FROM   artists
WHERE  active = 1
AND    approved = 1
<cfif URL.sort eq "county-az" or URL.sort eq "county-za">
    AND county IS NOT NULL
</cfif>

The above is what I need to change within the core of the query, notice I have included the section to exclude all NULL results BEFORE checking if a search term is present.

score 0 · Answer 2 · edited May 09 '13 at 01:32

0

Try using a dynamic approach using @variables in sql server:

declare @county varchar(50), @countylike varchar(50)

Add some logic to set the variables if they exist in ColdFusion here:

<cfif ....>

</cfif>

Then add something like this to the where clause:

and (@county is null or county = @county)
and (@countylike is null or county like '%'+ @countylike +'%')

edited May 09 '13 at 01:32

Leigh

28,765
10
55
103

answered May 08 '13 at 23:44

lpeckazca

1
1

Technically [sql server does not guarantee the order of evaluation](http://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated). So using `cfif` is safer. – Leigh May 09 '13 at 01:47
using the code I pasted does not rely on order, if the county variable is null then that portion always evaluates true, likewise for the countylike variable. You still have to set these up, and I would do that in cold fusion, making sure only one or the other was set. Make sure to use to try and prevent injection issues. – lpeckazca May 10 '13 at 03:55

Omitting blank/null records from a query? (Coldfusion)

2 Answers2