Funky ColdFusion Error

A friend and I were working on this page yesterday... We were getting this "not a valid number" error.. The code worked fine when written and ran straight from Toad. We both looked at the code puzzled for a few minutes. My friend commented out a line using the CF "where a.loctype='#thisLoctype#'" statement and below it wrote the line using the literal value of the statement (w/o the cf statement) "where a.loctype = 'RET'". The page worked fine. Then he commented out the top literal line and uncommented out the CF line... The code worked. Then he removed the commented out literal line, the page wouldn't load giving the same error. We added back the commented line and the code works fine. Have you ever seen anything like that? Below is the code... select i.itemid, i.itemname, i.seltype, i.useqty, nvl2(d.itemid,d.value,i.dfltvalue) as value, nvl(d.qty,1) as qty, nvl(d.seq,1) as seq, max(nvl(d.seq,1)) over (partition by i.itemid) as maxseq from items i, item_appliesto a, (   select itemid, seq, value, qty   from item_data   where loctype='#thisLoctype#'   and locid=<cfqueryparam value="#url.locid#" CFSQLType="CF_SQL_VARCHAR"> ) d where a.loctype='#thisLoctype#' --where a.loctype = 'RET'                   <-- if this line is removed, the code stops working, giving invalid number error. and i.itemid = a.itemid and i.enabled='1' and d.itemid (+) = i.itemid order by i.itemid, nvl(d.seq,1) Greg Morphis Rapid Response Team Client Server Dev/Analyst I Armageddon Programmer/Support Desk: 501/905-2881 Cell: 501/529-3691 ****************************************************************************************** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else.

Greg.Morphis@alltel.com wrote: ----- Excess quoted text cut - see Original Post for more ----- No. What is the exact error and the exact code? Jochem

Has noone else seen this before? A friend and I were working on this page yesterday... We were getting this "not a valid number" error.. The code worked fine when written and ran straight from Toad. We both looked at the code puzzled for a few minutes. My friend commented out a line using the CF "where a.loctype='#thisLoctype#'" statement and below it wrote the line using the literal value of the statement (w/o the cf statement) "where a.loctype = 'RET'". The page worked fine. Then he commented out the top literal line and uncommented out the CF line... The code worked. Then he removed the commented out literal line, the page wouldn't load giving the same error. We added back the commented line and the code works fine. Have you ever seen anything like that? Below is the code... select i.itemid, i.itemname, i.seltype, i.useqty, nvl2(d.itemid,d.value,i.dfltvalue) as value, nvl(d.qty,1) as qty, nvl(d.seq,1) as seq, max(nvl(d.seq,1)) over (partition by i.itemid) as maxseq from items i, item_appliesto a, ( select itemid, seq, value, qty from item_data where loctype='#thisLoctype#' and locid=<cfqueryparam value="#url.locid#" CFSQLType="CF_SQL_VARCHAR"> ) d where a.loctype='#thisLoctype#' --where a.loctype = 'RET'                   <-- if this line is removed, the code stops working, giving invalid number error. and i.itemid = a.itemid and i.enabled='1' and d.itemid (+) = i.itemid order by i.itemid, nvl(d.seq,1) Greg Morphis Rapid Response Team Client Server Dev/Analyst I Armageddon Programmer/Support Desk: 501/905-2881 Cell: 501/529-3691 ****************************************************************************************** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else.   _____  

I've not seen anything quite like that before. I have seen some funkiness where it appears that the query is being cached, even when I haven't told it to cache, and you have to change something in the query (more space/less space, 1=1) and if you revert back to the old spacing, it'll revert back to the cached query. So, maybe if you take it out, it's reverting back to a cached template somehow? Have you tried cycling the cf server? > Has noone else seen this before? >

It was the invalid number Cold Fusion error... I cannot duplicate the error today, so maybe it had something to do with caching... I'm not sure, we were pretty baffled about this yesterday Greg.Morphis@alltel.com wrote: ----- Excess quoted text cut - see Original Post for more ----- No. What is the exact error and the exact code? Jochem   _____  

Greg.Morphis@alltel.com wrote: > It was the invalid number Cold Fusion error... Which one exactly? If it was a ColdFusion error, it is more likely caused by the cfqueryparam as by the line you mentioned. You can easily check what threw the error by looking at the last part that is between square brackets. For instance [Microsoft][ODBC Microsoft Access Driver] means the error was in the database, not in CF. That rules out certain types of errors. Jochem