This content has been marked as final. Show 7 replies
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.
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'
I'm not sure CF supports that SQL function in the QofQ but you should be able to use it in your initial query.
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.
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.
> 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...
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
month(ISNULL(d.depday, '#url.date2#')) AS TranMonth,
year(ISNULL(d.depday, '#url.date2#')) AS TranYear
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
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#'))
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.