thanx for your reply, Scott >> Is there an error message?
yeah a vague "syntax error" - nothing more. take out the cfqueryparams in the SELECT and it works fine >> Also, wouldn't the expected speed gain be lost as the params ARGUMENTS.auth_code and qVendor.VEND_CODE are not parameterised as well? good point. Idealy, all those values within the SELECT should use cfqueryparam as well as the later WHERE clause - that's what I was aiming for. But the problem seems to be that cfqueryparam can't be used *anywhere* in the select part, only the WHERE. I use cfqueryparam not only for speed reasons but to format data acceptable for the drivers to except. eg: if you use cfsqltype="CF_SQL_DATE" and a "yyyy-mm-dd" date format, you don't have to stuff around with createODBCdate() etc. Works brilliantly - except in this case. >> Last question.. is there any reason why you don't use database functions you're assuming a particular db vendor. this is an Informix db but could be changed to mySQL ot MSSQL within the life of the product. While all those db's use the same syntax for month() and year(), Informix doesn't support getDate(). It's a case of sticking with the lowest common denominator... cf date functions thanx barry.b -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Thornton Sent: Friday, 17 September 2004 8:10 AM To: CFAussie Mailing List Subject: [cfaussie] Re: cfqueryparam woes: doesn't work with INSERT INTO...SELECT FROM? Hi, Is there an error message? Also, wouldn't the expected speed gain be lost as the params ARGUMENTS.auth_code and qVendor.VEND_CODE are not parameterised as well? Last question.. is there any reason why you don't use database functions, rather then CF functions? eg Year(getdate()) vs #year(now())# >>> [EMAIL PROTECTED] 16/09/2004 5:36:59 pm >>> I think I've found a limit of usefulness to cfqueryparam: it doesn't seem to work with an INSERT INTO...SELECT FROM as per below (in the "select...from" part eg: the "#ARGUMENTS.auth_code#") can anyone confirm this? can anyone suggest why? thanx barry.b INSERT INTO poaudit( CMPY_CODE, DESC_TEXT, ENTRY_CODE, ENTRY_DATE, EXT_COST_AMT, EXT_TAX_AMT, LINE_NUM, LINE_TOTAL_AMT, NOW_AUTH_FLAG, ORDER_QTY, ORIG_AUTH_FLAG, PERIOD_NUM, POSTED_FLAG, PO_NUM, RECEIVED_QTY, SEQ_NUM, TRAN_CODE, TRAN_DATE, TRAN_NUM, UNIT_COST_AMT, UNIT_TAX_AMT, VEND_CODE, VOUCHER_QTY, YEAR_NUM ) SELECT CMPY_CODE, DESC_TEXT, "#ARGUMENTS.auth_code#", #NOW()#, unit_amt * order_qty, 0, LINE_NUM, unit_amt * order_qty, "N", order_qty, "N", #month(now())#, "N", #qNext_po_num.order_num#, 0 , 1, "AA", #now()#, 1, unit_amt, 0, "#qVendor.VEND_CODE#", 0, #year(now())# FROM reqdetl WHERE req_num = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ARGUMENTS.req_num#"> --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/
