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, Aug 09 '16 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, Aug 09 '16 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, Aug 09 '16 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, Aug 09 '16 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](http://stackoverflow.com/questions/38856372/coldfusion-query-of-queries-with-empty-strings#comment65077949_38856372)? — Leigh, Aug 09 '16 at 17:41
I saw the comment about the syntax. the strange column names are not the issue in the where statement, I'm not erroring on that. It must be a corrupt spreadsheet. The sheet has 20 records. But the dumped query says it's over 42,000. When I do QoQ on Contractor <> '', the dump goes down to 30,000 about. I don't know what's in those cells. And I was wanting to avoid looping through each row to check for lengths, because it will take longer. I might have to. Let me know if you would like the spreadsheet — Matt Wilde, Aug 09 '16 at 18:33
Life might be easier if you wrote a new spreadsheet with about 4 rows instead of 40,000 and worked with that. — Dan Bracuk, Aug 09 '16 at 18:39
@DanBracuk I agree wholeheartedly. But I have to make this automated which means I can't be the middleman to edit and resave the spreadsheet. I could use CFSpreadsheet to build a new one with all non-empty values, only that I was wanting to avoid looping through the entire thing and figured QoQ would be much faster. — Matt Wilde, Aug 09 '16 at 18:53
You missed the point. Whatever works with a small spreadsheet should also work with a large one. You use the small one to troubleshoot. — Dan Bracuk, Aug 09 '16 at 18:58
@DanBracuk Makes sense. I did that. I found an extraneous text box towards the bottom of the file. When converted to a query it also has over 1000 columns. I believe it's formatting information that is found in the cells that keeps those rows in the query. I am going to run through the rows and check for empty values as was suggested and allow the extra processing time just because the quality is more important than the speed. — Matt Wilde, Aug 09 '16 at 21:37

score 3 · Accepted Answer · edited May 23 '17 at 12:25

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):

I ended up renaming the columns and the query worked much better and gave 80 rows rather than 42,000. — Matt Wilde, Aug 10 '16 at 21:22
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 ;-) — Leigh, Aug 11 '16 at 00:39

Coldfusion Query of Queries with Empty Strings

1 Answers1