Query of Queries Differences in CF vs Lucee

[Armando Luna]

Hello, I am migrating a site from CF2016 to Lucee 5, and I have a query of queries that works under CF but throws the error 'Not a condition' under Lucee. The query in question:

             <cfquery name="SortedProducts" dbtype="query">

                SELECT sum(units) AS units, description, type 

                FROM MyQuery 

                WHERE (type = 'V' OR type = 'M') 

                GROUP BY description, type, units 

                ORDER BY type DESC, description

            </cfquery>

The query MyQuery has the following structure, and is filled by a series of cfloops and querySetCell():

<cfset myQuery=QueryNew( "patient_id, patient_name, type, dos_start, description, payment, units, unitprice, charge, covered, notcovered, copay, coinsurance, deductible, claim_amt, insurance_paid, patient_resp, id, remit_date, status, billto, transaction_dt", "Integer, VarChar, VarChar, Date, VarChar, Decimal, Decimal, Decimal, Decimal, Decimal, Decimal, Decimal, Decimal, Decimal, Decimal, Decimal, Decimal, Integer, Date, VarChar, VarChar, Date" )>

Apologies if I missed a previous answer in my search.

[Armando Luna]

Update: Changing the query to the following fixed the issue:

            <cfquery name="SortedProducts" dbtype="query">

                SELECT sum(units) AS ttlunits, description, linetype 

                FROM MyQuery 

                WHERE (linetype = 'V' OR linetype = 'M') 

                GROUP BY description, linetype, units 

                ORDER BY linetype DESC, description

            </cfquery> 

The change that fixed it was 'SUM(units) AS ttlunits' instead of 'SUM(units) AS units', althought I still don't know why. Changing 'type' to 'linetype' didn't solve it, but I left it anyway.

[Nando Breiter]

Another difference to be aware of is that Lucee will always cast anything it can convert to a numeric to a numeric, even if the type of the property is varchar. Think dewey decimal number to get a sense of why this might not be an optimal behavior.

Aria Media Sagl

+41 (0)76 303 4477 cell
skype: ariamedia

--
Get 10% off of the regular price for this years CFCamp in Munich, Germany (Oct. 20th & 21st) with the Lucee discount code Lucee@cfcamp. 189€ instead of 210€. Visit https://ti.to/cfcamp/cfcamp-2016/discount/Lucee@cfcamp
---
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/243963fe-e183-48c6-b7a2-f6fc01dc6be4%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Armando Luna]

Thanks for the tip, dos that mean I would have to explicitly cast '01' as VARCHAR to keep it from converting to a numeric?  I also noticed that Lucee converted DATE columns to TIMESTAMP.

On Wednesday, November 9, 2016 at 12:40:08 PM UTC-6, Nando Breiter wrote:

Another difference to be aware of is that Lucee will always cast anything it can convert to a numeric to a numeric, even if the type of the property is varchar. Think dewey decimal number to get a sense of why this might not be an optimal behavior.

Aria Media Sagl

+41 (0)76 303 4477 cell
skype: ariamedia

On Wed, Nov 9, 2016 at 7:10 PM, Armando Luna <cool...@gmail.com> wrote:

Update: Changing the query to the following fixed the issue:

            <cfquery name="SortedProducts" dbtype="query">

                SELECT sum(units) AS ttlunits, description, linetype 

                FROM MyQuery 

                WHERE (linetype = 'V' OR linetype = 'M') 

                GROUP BY description, linetype, units 

                ORDER BY linetype DESC, description

            </cfquery> 

The change that fixed it was 'SUM(units) AS ttlunits' instead of 'SUM(units) AS units', althought I still don't know why. Changing 'type' to 'linetype' didn't solve it, but I left it anyway.

--
Get 10% off of the regular price for this years CFCamp in Munich, Germany (Oct. 20th & 21st) with the Lucee discount code Lucee@cfcamp. 189€ instead of 210€. Visit https://ti.to/cfcamp/cfcamp-2016/discount/Lucee@cfcamp
---
You received this message because you are subscribed to the Google Groups "Lucee" group.

To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.

To post to this group, send email to lu...@googlegroups.com.

[Nando Breiter]

Thanks for the tip, dos that mean I would have to explicitly cast '01' as VARCHAR to keep it from converting to a numeric?  

 That won't work, as far as I know without testing it again now. I tried creating query separately and casting the column to a varchar, but Lucee ignored that and cast my decimal based categorization number to a decimal.

One possible way to work around this is to use arrays of structs instead of q of q's