A

Anonymous

Answered

Ignoring Rows with Empty Fields?

Forum|Forum|17 years ago
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!

Database access

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.

M

musicmp3

Participating Frequently

Thanks Kronin. Nice explanation.

For music visit http://mp3bravo.com

K

Kronin555

Participating Frequently

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

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

Thanks Kronin, that has worked perfectly! :-)

K

Kronin555Correct answer

Participating Frequently

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

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

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

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

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

Phil

A

Anonymous

Thanks for the reply

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded