Problem with QofQ and Null Values

[Guest]

Just converted from CF5 to CF7. Finally figured out what was causing the error message :
"The value "" cannot be converted to a number" when running a QofQ. It happens whenever there is a NULL (and I think a ZERO) value in the QofQ. I'm using the QofQ in a seperate chart program.

The main query merges data from a store and a region table. I'm doing a LEFT OUTER JOIN to combine the REGION with the STORE, AND so that stores with NO SALES for a given day will show up as ZERO. On the main report, when I don't use a QofQWhen grouping by DAY or MONTH, I can test the value in the CFOUTPUT for NULL or ZERO and display "0" or "N/A" on the report.

But when I try to chart it, using a QofQ to read in the values from the main query, and format the numbers, I get this error message. I've tried using the CAST function, using FLOAT, DOUBLE, BIGINT, etc., but still get same error message.

The only way I could fix this is to add a WHERE statement to the QofQ, that only includes sales values greater than ZERO. But then, I have gaps in my chart because stores with NO SALES for a given day or MONTH don't appear. Because they are removed from the query. The whole point of LEFT JOINS is to include items with no values (or NULL) values.

Does anyone know if it's possible to keep these values in a QofQ? So that items (e.g., store locations in my case) with NULL or ZERO values for some or all days or months can be charted? Even with a ZERO value? I've read everything I can get my hands on regarding QofQ, the CAST function, etc., but nothing seems to work. Thanks for any help. (FYI, didn't have this problem with CF5. It's QofQ could handle NULL or ZERO values without providing an error).

Gary

[Newsgroup_User]

QoQ has problems with nulls. <- "period" ;-)

If poss, alter your original query to change (using nvl() or whatever your
DB uses) potential nulls to zero or something else suitable, which QoQ
won't get confused by.

--
Adam

[insuractive]

If you are using MS SQL Server, you could try using the isNull(field,value) function in your original query to replace NULL values with something else.

i.e. SELECT isNull(sales, 0) 'sales'
FROM myTable

I'm not sure CF supports that SQL function in the QofQ but you should be able to use it in your initial query.

[Guest]

Thank you all for your responses. At least I know I'm not missing something, and that this is a KNOWN issue. I'll try that IsNull() function in the main (initial) query (am using SQL Server). But I don't know if the IsNull() function will work on a LEFT OUTER JOIN. That happens later in the query.

Do you think that using the function in the main query at the beginning, before the JOIN, will have an effect? Because the NULL values don't occur until AFTER the join, which occurs in the FROM and WHERE sections.

I also have other queries where I use the QofQ to JOIN data from 2 different databases. The first query pulls sales data from an IBM-DB2 database. Then 2nd query pulls goal, region and other data from my SQL Server DB. Then the QofQ does the join, by including in it's SELECT statement, variables from BOTH the previous two queries, with a FROM SalesData, RegionData statement. To fix this one, I've had to add to the WHERE statement of the QofQ, this line:

WHERE Query1.SalesAmout > 0

But as mentioned, this removes all stores that didn't have any sales for a given day or month (any cell that has a null value). So they simply don't appear on the report and/or the chart.

I'll try all the options you suggested, and hopefully can get something to work. Thanks very much for your help.

Gary

[Newsgroup_User]

> But I don't know if the IsNull()
> function will work on a LEFT OUTER JOIN. That happens later in the query.

> Do you think that using the function in the main query at the beginning,
> before the JOIN, will have an effect? Because the NULL values don't occur
> until AFTER the join, which occurs in the FROM and WHERE sections.

The easiest way to find out is to try it ;-)

It probably would have taken less time to test it than to type the question
in, I should have thought...

--
Adam

[Guest]

Well, I tried the IsNull function on the 3 SUM() values I was calculating in my SELECT statement (on a LEFT OUTER JOIN QUERY), and I STILL got another "cannot convert "" to a number" but this time the error message was more direct, and pointed me to a DATE field in my query, where I was SORTING AND GROUPING either by DAY or MONTH (depending on what user selected on form).

In my QofQ, I'm not just reformating the integer SUM() values, but also the date values. So, I applied the IsNull() function to the DATE values in my main query. I still kept getting errors. But after experimenting ((SQL Server BOL docs doesn't give ISNULL() examples for DATE FIELDS, only NUMBER fields), I tried putting the dates in SINGLE QUOTES in my IsNull() function, the QUERY RAN. Then I wasn't sure WHAT date to enter, e.g., 1/1/1889 or 1/1/2001, etc.

Then, I discovered, after experimenting, that you cannot just blindly enter ANY date when using IsNull in a date field, especially if you are using CFGRAPH to chart the results by day. You have to select a date within the date range the user selected, so this means using the FORM.DATE (or URL.DATE) value from the form. Here's the working example from my SELECT main query:

SELECT SUM(ISNULL(d.ttldb,0)) AS SumOfDB,
SUM(ISNULL(d.ttldbv,0)) AS SumOfDBV,
SUM(ISNULL(d.ttldbi,0)) AS SumOfDBI,
<CFIF '#url.reptype#' IS "DAILY">
ISNULL(d.depday, '#url.date1#') AS depday
<CFELSE>
month(ISNULL(d.depday, '#url.date2#')) AS TranMonth,
year(ISNULL(d.depday, '#url.date2#')) AS TranYear
</CFIF>

Then I discovered that whatever date was used in the URL.DATE field (in my case it would be one of the two date field from the form, and depended on whether you coded DATE1 or DATE2, the chart would always show ZERO values for that date. So I tried putting the IsNull() date values in the GROUP BY and ORDER BY statements, e.g.,

<CFIF '#url.reptype#' IS "DAILY">
GROUP BY ISNULL(d.depday, '#url.date1#')
ORDER BY ISNULL(d.depday, '#url.date1#') DESC
<CFELSE>
GROUP BY year(ISNULL(d.depday, '#url.date2#')), month(ISNULL(d.depday, '#url.date2#'))
ORDER BY year(ISNULL(d.depday, '#url.date2#')), month(ISNULL(d.depday, '#url.date2#'))
</CFIF>

And this worked. The report ran just fine, and the chart charted every value correctly.
I guess you have to learn all the little "quirks" about CF7 and how it's QofQ and chart programs work. But so far, so good.

Thank you all for your help. Now I've got to update other old CF5 programs that are returning errors when there are null values from my LEFT OUTER JOIN queries. Will take some time, but at least I know how to do it. Thanks to your help.
I really appreciate your time and efforts to help with this issue.

Gary

[palephace]

If you are not alreday usinmg query functions, you might try using CF's query functions to create a temp query for the data that can have a NULL or numeric value. As long as the first item put into that temp query is a string, it should accept an empty string. The problem is that QoQ sees this column's data type as already being numeric(its buggy), because the first piece of data it encountered was an integer.

[Stefan_K_]

I think you ran into the "undefined datatype with QoQ"-problem.
My following article might help you understand/oslve the problem:

http://kuhn.cf-workshop.de/CF_UDFs/QueryNewDatatype.htm