CASE and CONVERT in Q of Q / CF7

[Guest]

Am converting CF5 code to CF7, and from documentation, understand you can do more things in Query-of-Queries in CF7 than you could in CF5.

But one QofQ that worked in CF5 won't work in CF7, returns an error. It uses a variable that on occassion causes a division by zero. This was never a problem with CF5, but CF7 won't take it. In a normal query, I would handle by using a CASE statement. So my question is: CAN YOU USE "CASE" statements in CF7 QofQ's? And what about CONVERT or CAST? Can you use those as well, in CF7 QofQ's?

Thanks for any help/advice.
Gary

[Dan_Bracuk]

I've never tried to use case in Q of Q so I don't know. I suggest you try a very simple one and then you will know.

I have successfully used cast. It's quite handy since Cold Fusion queries have such unpredictable datatypes.

Avoiding division by zero errors can be accomplished in the where clause most of the time.

[Newsgroup_User]

> Am converting CF5 code to CF7, and from documentation, understand you can do
> more things in Query-of-Queries in CF7 than you could in CF5.

Not many more things, but it's less flaky. You were brave to attempt to
use it in CF5, I must say!


> But one QofQ that worked in CF5 won't work in CF7, returns an error. It uses
> a variable that on occassion causes a division by zero.

I'm not sure how that would have not given you problems in CF5 as well, to
be honest. Are you sure you're analysing the issue properly?


> using a CASE statement. So my question is: CAN YOU USE "CASE" statements in
> CF7 QofQ's?

No.


> And what about CONVERT

No.


> or CAST?

Kind of.


> Thanks for any help/advice.

The docs are always a good placed to start.

http://livedocs.macromedia.com/coldfusion/7/htmldocs/00001266.htm

--
Adam

[Guest]

Thanks to you both for confirming what my testing and doc-reading seemed to confirm. That you can't use CASE or CONVERT in a CF7 QofQ. I think you're right about CAST but not sure if that will solve my problem. QofQ in CF5 was "flaky" enough to permit a division by zero. But I had to "account" for it in my <CFOUTPUT>, in order to get the report to run and print 0%.

Here's my situation. Current CF5 (and CF7) in main query, I'm calculating the values of a number of variables, let's say one of them is A, the other B. In the old CF5 QofQ, I could do an "A divided by B" as C, where C was my Percent Complete. And if B was ever ZERO, the CF5 QofQ would still run. For some reason, QofQ in CF5 was "flaky" enough to permit this.

But in my <CFOUTPUT>, I would have to check the value of B. If it was equal to zero, then I'd set C = to "0%" or "N/A", and output the results, which printed fine on the report. If B was not = to ZERO, then I'd re-calculate the percentage, and set C = A divided by B, and output "C%" in the report.

But with CF7, the template throws an error in the QofQ, when it encounters an instance where B = 0. The error message is:
"Query Of Queries runtime error. java.lang.Double -> BigDecimal "

I can resolve by adding a HAVING statement to first query, only accepting records where B > 0. Problem with that, is I lose the display of the record that I would like to have shown, but showing 0%, versus not bein there at all. I did read in the docs, before posting message, that CAST can be used in CF7 QofQ, but not sure if that will resolve the problem.

I'll figure something out, but the main reason for posting the initial question was to verify from others, that CASE and CONVERT can NOT be used in CF7's QofQ's. The docs simply didn't mention one way or the other regarding these 2 query functions.

If you or anyone else has any other suggestions, I would be most grateful. Otherwise, as mentioned, will hopefully be able to figure something out. Thanks again,

Gary

[Newsgroup_User]

I'd perhaps consider unioning two queries in the QoQ:

select (A/B) * 100 as perc
from q
where B > 0

UNION

select 0 as perc
from q
where B = 0

You can add an order by into that if it makes sense to, natch.

However. You could have a flaw in your logic somewhere if B ever *is* 0.
You can't divide by zero for a reason, and you should never be in the
position that you need to. Zero should not be a valid value for any row in
the B column, if it's logical to sometimes be dividing something by it. If
that makes sense.

--
Adam

[Dan_Bracuk]

Do a union query. In the top half have where b <> 0 and in the bottom half have where b = 0

[Guest]

Thanks Adam, Dan for the tips. Just back from vacation. I'll give your UNION suggestion a try. FYI, in my QofQ, I've got about 12 variables that I'm using, and several other calculations I'm doing. This particular calculation (the percentage we've been discussing) is only one of them. And this QofQ is actually combining data from 2 prevous CFQUERY statements.

The first CFQUERY reads data from the local SQL Server. The 2nd CFQUERY reads data from another DB/server (an IBM DB2/UDB database). The QofQ helps me combine the data from multiple DBs into a single CFQUERY, used to output the final results.

I may have to do a 2nd QofQ in order to do the UNION for this particular calculation (the one to prevent the division by zero situations).

Many thanks again. We'll see how this works out.
Gary