Skip to main content
A
Anonymous
July 28, 2008
Answered

Ignoring Rows with Empty Fields?

  • July 28, 2008
  • 6 replies
  • 1188 views
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!
This topic has been closed for replies.
Correct answer 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.

6 replies

M
musicmp3
Participating Frequently
July 29, 2008
Thanks Kronin. Nice explanation.

For music visit http://mp3bravo.com
K
Kronin555
Participating Frequently
July 28, 2008
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.
D
Dan_Bracuk
Inspiring
July 28, 2008
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.
A
Anonymous
July 28, 2008
Thanks Kronin, that has worked perfectly! :-)
K
Kronin555Correct answer
Participating Frequently
July 28, 2008
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.
D
Dan_Bracuk
Inspiring
July 28, 2008
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.
D
Dan_Bracuk
Inspiring
July 28, 2008
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.
A
Anonymous
July 28, 2008
The form isn't part of this process - the table is populated at another stage, and this particular field is an optional one.
P
paross1
Participating Frequently
July 28, 2008
What is your error? Could this be a PreserveSingleQuotes()-type issue with your "list"?

Phil
A
Anonymous
July 28, 2008
Thanks for the reply

The error I'm getting with that query is " Element SOSHORTNAME is undefined in GETSIMILAR"
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices