Ignoring Rows with Empty Fields?

[Guest]

I have an existing query, and would like to limit the results according to whether one of the fields (which is an optional field in a form) has a value.

I've been looking through some similar threads here and came up with the following;

<cfquery name="getsimilar" datasource="#datasource#" maxrows="5">
SELECT catshortname, soshortname, retailer_name
FROM merchants
WHERE 1 = 2
<cfif len(trim(getsimilar.soshortname)) GT 0>
OR soshortname IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#getsimilar.soshortname#" list="yes">)
</cfif>
AND catshortname='#getmerchant.catshortname#'
ORDER BY retailer_name ASC
</cfquery>

However, I get an error when executing this.

The field I want to be sure has a value is 'soshortname'

Any help much appreciated!

[paross1]

What is your error? Could this be a PreserveSingleQuotes()-type issue with your "list"?

Phil

[Guest]

Thanks for the reply

The error I'm getting with that query is " Element SOSHORTNAME is undefined in GETSIMILAR"

[Dan_Bracuk]

That's because you are trying to reference your query results while you are running the query. You mentioned a form, but I don't see any form variables being used.

[Guest]

The form isn't part of this process - the table is populated at another stage, and this particular field is an optional one.

[Kronin555 · Accepted solution]

Why not just do:
<cfquery name="getsimilar" datasource="#datasource#" maxrows="5">
SELECT catshortname, soshortname, retailer_name
FROM merchants
WHERE soshortname is not null and soshortname != ''
AND catshortname='#getmerchant.catshortname#'
ORDER BY retailer_name ASC
</cfquery>

The reason there's 2 checks is I'm not sure if you're storing empty values as nulls or empty strings.

[Dan_Bracuk]

quote:

---

Originally posted by: Kronin555
Why not just do:
<cfquery name="getsimilar" datasource="#datasource#" maxrows="5">
SELECT catshortname, soshortname, retailer_name
FROM merchants
WHERE soshortname is not null and soshortname != ''
AND catshortname='#getmerchant.catshortname#'
ORDER BY retailer_name ASC
</cfquery>

The reason there's 2 checks is I'm not sure if you're storing empty values as nulls or empty strings.

---

Just so you know, this:
and soshortname != ''
makes this
WHERE soshortname is not null
unnecessary.

[Guest]

Thanks Kronin, that has worked perfectly! 🙂

[Kronin555]

DOH! Right you are, Dan. I was thinking the opposite when I wrote that, which, if you store empty fields as nulls or empty strings and don't have consistency, in order to get records that have blank fields in that column you'd need to check for:
soshortname is null or soshortname = ''

However, that's rectified if you just consistently store empty strings as nulls.

[Dan_Bracuk]

quote:

---

Originally posted by: Kronin555
DOH! Right you are, Dan. I was thinking the opposite when I wrote that, which, if you store empty fields as nulls or empty strings and don't have consistency, in order to get records that have blank fields in that column you'd need to check for:
soshortname is null or soshortname = ''

However, that's rectified if you just consistently store empty strings as nulls.

---

Sometimes you want to story empty strings as empty strings. If you ever think you want to concatonate that field with something else, empty strings would be better.

[musicmp3]

Thanks Kronin. Nice explanation.

For music visit http://mp3bravo.com