Coldfusion Query of Queries with Empty Strings

Question

The query I start out with has 40,000 lines of empty rows, which stems from a problem with the original spreadsheet from which it was taken.

Using CF16 server

I would like to do a Query of Queries on a variably named 'key column'.

In my query:

var keyColumn = "Permit No."

var newQuery = "select * from source where (cast('#keyColumn#' as varchar) <> '')";

Note: the casting comes from this suggestion

I still get all those empty fields in there.

But when I use "City" as the keyColumn, it works. How do the values in both those columns differ when they both say [empty string] on the query dump?

Is it a problem with column names? What kind of data are in those cells?

Any difference if you try where col is NOT NULL? What kind of data are in those cells Dump it and find out. Something like writeDump(yourQuery.columnName[rowNum].getClass().name). Might also check the value lengths to see if it really is an empty string. — Leigh
– Leigh, Commented Aug 9, 2016 at 16:58
@Leigh java.lang.String in a cell like 23 with [empty string]. is not null check didn't change anything. Thanks for pointing out the getClass function — Matt Wilde
– Matt Wilde, Commented Aug 9, 2016 at 17:05
(Edit) Aside from verifying the value length is actually 0, are you sure the SQL is valid? Reason for asking is I do not recall if that is the correct way to escape invalid column names in a QoQ, off the top of my head. — Leigh
– Leigh, Commented Aug 9, 2016 at 17:19
@Leigh I tried doing a CHARACTER_LENGTH, CHAR_LENGTH, LENGTH, and LEN check on ('Permit No.') > 1 but those gave me an error on the syntax? And even when I do the selection on 'Contractor' it gives me the empty ones. City is also java.lang.String. Should I rename columns first? Rebuild the query? — Matt Wilde
– Matt Wilde, Commented Aug 9, 2016 at 17:26
Edit: You cannot use a QoQ (very little function support). Use the LEN function on a single value ie LEN( queryName["colName"][rowNum] ). BTW, did you see my question about syntax? — Leigh
– Leigh, Commented Aug 9, 2016 at 17:41

Community · Accepted Answer · 2017-05-23 12:25:27Z

3

where ( cast('Permit No.' as varchar) <> '' )

The problem is the SQL, not the values. By enclosing the column name in quotes, you are actually comparing the literal string "P-e-r-m-i-t N-o-.", not the values inside that column. Since the string "Permit No." can never equal an empty string, the comparison always returns true. That is why the resulting query still includes all rows.

Unless it was fixed in ColdFusion 2016, QoQ's do not support column names containing invalid characters like spaces. One workaround is to use the "columnNames" attribute to specify valid column names when reading the spreadsheet. Failing that, another option is to take advantage of the fact that query columns are arrays and duplicate the data under a valid column name: queryAddColumn(yourQuery, "PermitNo", yourQuery["Permit No."]) (Though the latter option is less ideal because it may require copying the underlying data internally):

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Aug 10, 2016 at 0:42

Leigh

28.9k10 gold badges58 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Wilde Over a year ago

I ended up renaming the columns and the query worked much better and gave 80 rows rather than 42,000.

Leigh Over a year ago

Good to hear. Sounds like restricting the "columns" returned in the query had the added benefit of excluding the extras, which happened to contain all those "empty" cells ;-)

Collectives™ on Stack Overflow

Coldfusion Query of Queries with Empty Strings

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related