man thats some query! whats the execution time on something like that?
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Scott Thornton Sent: Monday, July 18, 2005 9:44 AM To: CFAussie Mailing List Subject: [cfaussie] Re: CFQUERY sql string I guess it is something to think about. Like to offer an option on how to turn this query into a variable easily? <cfquery datasource="#DSN#" name="SEARCH_RESULT"> set dateformat dmy SELECT PATIENT.SB_PATIENT_MRN, PATIENT.SB_HL7_SOURCE, PATIENT.SB_PATIENT_SURNAME, PATIENT.SB_PATIENT_OTHER_NAMES, PATIENT.SB_PATIENT_DOB, CASE WHEN INVOICE.SB_INVOICE_PRINT_ID IS NULL THEN 'NORMAL' ELSE 'GROUP' END AS TYPE, CASE WHEN INVOICE.SB_INVOICE_PRINT_ID IS NULL THEN INVOICE.SB_INVOICE_ID ELSE INVOICE.SB_INVOICE_PRINT_ID END AS SB_INVOICE_ID, MAX(INVOICE.SB_INVOICE_PRINT_ID) as SB_INVOICE_PRINT_ID, INVOICE.SB_INVOICE_DATE, CASE WHEN INVOICE.SB_INVOICE_PRINT_ID IS NULL THEN INVOICE.SB_INVOICE_NUMBER ELSE INVOICE_PRINT.SB_INVOICE_PRINT_INV_NO END AS SB_INVOICE_NUMBER, CASE WHEN MAX( INVOICE.SB_PROVIDER_NUMBER ) = MIN(INVOICE.SB_PROVIDER_NUMBER) THEN MIN(provider.SB_PROVIDER_SURNAME) ELSE '' END AS SB_PROVIDER_SURNAME, CASE WHEN MAX( INVOICE.SB_PROVIDER_NUMBER ) = MIN(INVOICE.SB_PROVIDER_NUMBER) THEN MIN(provider.SB_PROVIDER_NUMBER ) ELSE '' END AS SB_PROVIDER_NUMBER, MIN(SB_BILL_MECH_DESC) AS SB_BILL_MECH_DESC, <cfif session.imaging eq "N"> EP_TYPE.SB_EPISODE_TYPE_DESC, EPISODE.SB_HOSPITAL_CODE, EPISODE.SB_ADMISSION_DATE, EPISODE.SB_DISCHARGE_DATE, </cfif> MIN(INVOICE.SB_REMINDER_LEVEL) AS SB_REMINDER_LEVEL, ISNULL(ISNULL(( SELECT SUM(INVOICE3.SB_INVOICE_TOTAL_AMT) FROM SB_INVOICE INVOICE3 WHERE INVOICE3.SB_INVOICE_PRINT_ID = INVOICE.SB_INVOICE_PRINT_ID AND INVOICE3.SB_PATIENT_MRN = PATIENT.SB_PATIENT_MRN AND INVOICE3.SB_HL7_SOURCE = PATIENT.SB_HL7_SOURCE ), sum(ITEM.SB_INVOICE_COST) ),0) AS INVOICE_AMOUNT, ISNULL(( SELECT SUM(ISNULL(ITEM3.SB_HIC_AMOUNT_PAID,0) + ISNULL(ITEM3.SB_FUND_AMOUNT_PAID,0) ) FROM SB_INVOICE INVOICE3 INNER JOIN SB_INVOICE_ITEM ITEM3 ON INVOICE3.SB_INVOICE_ID = ITEM3.SB_INVOICE_ID WHERE INVOICE3.SB_INVOICE_PRINT_ID = INVOICE.SB_INVOICE_PRINT_ID AND INVOICE3.SB_PATIENT_MRN = PATIENT.SB_PATIENT_MRN AND INVOICE3.SB_HL7_SOURCE = PATIENT.SB_HL7_SOURCE ),0) AS AMOUNT_PAID, ISNULL(( SELECT SUM(ISNULL(ITEM3.SB_WRITEOFF_AMOUNT,0) + ISNULL(ITEM3.SB_ADJUST_AMOUNT,0)) FROM SB_INVOICE INVOICE3 INNER JOIN SB_INVOICE_ITEM ITEM3 ON INVOICE3.SB_INVOICE_ID = ITEM3.SB_INVOICE_ID WHERE INVOICE3.SB_INVOICE_PRINT_ID = INVOICE.SB_INVOICE_PRINT_ID AND INVOICE3.SB_PATIENT_MRN = PATIENT.SB_PATIENT_MRN AND INVOICE3.SB_HL7_SOURCE = PATIENT.SB_HL7_SOURCE ),0) AS AMOUNT_ADJUSTED, ISNULL( ( SELECT SUM(ITEM3.SB_INVOICE_COST)-SUM(ISNULL(ITEM3.SB_HIC_AMOUNT_PAID,0) + ISNULL(ITEM3.SB_FUND_AMOUNT_PAID,0)) - SUM(ISNULL(ITEM3.SB_WRITEOFF_AMOUNT,0) + ISNULL(ITEM3.SB_ADJUST_AMOUNT,0)) FROM SB_INVOICE INVOICE3 INNER JOIN SB_INVOICE_ITEM ITEM3 ON INVOICE3.SB_INVOICE_ID = ITEM3.SB_INVOICE_ID WHERE INVOICE3.SB_INVOICE_PRINT_ID = INVOICE.SB_INVOICE_PRINT_ID AND INVOICE3.SB_PATIENT_MRN = PATIENT.SB_PATIENT_MRN AND INVOICE3.SB_HL7_SOURCE = PATIENT.SB_HL7_SOURCE ),sum(ISNULL(ITEM.SB_INVOICE_COST,0))) AS BALANCE FROM SB_PATIENT_REGISTER PATIENT INNER JOIN SB_PATIENT_EPISODE EPISODE ON PATIENT.SB_PATIENT_MRN = EPISODE.SB_PATIENT_MRN AND PATIENT.SB_HL7_SOURCE = EPISODE.SB_HL7_SOURCE INNER JOIN SB_EPISODE_TYPE EP_TYPE ON EPISODE.SB_EPISODE_TYPE_CODE = EP_TYPE.SB_EPISODE_TYPE_CODE INNER JOIN sb_invoice_balance_view INVOICE ON EPISODE.SB_EPISODE_NUMBER = INVOICE.SB_EPISODE_NUMBER left outer join SB_PATIENT_REGISTER MERGED_PATIENT ON PATIENT.SB_PATIENT_MRN = MERGED_PATIENT.SB_REDIRECTED_TO LEFT OUTER JOIN EPISODE_CURRENT_WARD CURRENT_WARD ON EPISODE.SB_EPISODE_NUMBER = CURRENT_WARD.SB_EPISODE_NUMBER LEFT OUTER JOIN SB_WARD ON SB_WARD.SB_WARD_ID = CURRENT_WARD.SB_WARD_ID LEFT OUTER JOIN SB_DEPARTMENT on SB_WARD.SB_DEPARTMENT_ID = SB_DEPARTMENT.SB_DEPARTMENT_ID LEFT OUTER JOIN SB_HOSPITAL HOSPITAL ON SB_DEPARTMENT.SB_HOSPITAL_CODE = HOSPITAL.SB_HOSPITAL_CODE LEFT OUTER JOIN SB_DEBTOR ON PATIENT.SB_DEBTOR_ID = SB_DEBTOR.SB_DEBTOR_ID LEFT OUTER JOIN SB_BILLING_MECHANISM BILLMECH ON INVOICE.SB_BILL_MECH_CODE = BILLMECH.SB_BILL_MECH_CODE LEFT OUTER JOIN SB_INVOICE_ITEM ITEM ON INVOICE.SB_INVOICE_ID = ITEM.SB_INVOICE_ID LEFT OUTER JOIN SB_INVOICE INVOICE2 ON INVOICE.SB_INVOICE_ID = INVOICE2.SB_INVOICE_ID LEFT OUTER JOIN SB_PROVIDER PROVIDER ON INVOICE.SB_PROVIDER_NUMBER = PROVIDER. SB_PROVIDER_NUMBER LEFT OUTER JOIN SB_INVOICE_PRINT INVOICE_PRINT ON INVOICE.SB_INVOICE_PRINT_ID = INVOICE_PRINT.SB_INVOICE_PRINT_ID LEFT OUTER JOIN SB_ITEM_CLAIM CLAIM ON ITEM.SB_INVOICE_ITEM_ID = CLAIM.SB_INVOICE_ITEM_ID LEFT OUTER JOIN SB_FUND_PAYMENT FP ON CLAIM.SB_FUND_PAYMENT_ID = FP.SB_FUND_PAYMENT_ID LEFT OUTER JOIN SB_RECEIPT_BATCH RECEIPT ON FP.SB_RECEIPT_BATCH_ID = RECEIPT.SB_RECEIPT_BATCH_ID LEFT OUTER JOIN SB_INVOICE_ERROR ERROR ON INVOICE.SB_INVOICE_ID = ERROR.SB_INVOICE_ID <cfif session.SEARCH_INVOICE_FLAG neq ""> AND ERROR.SB_INVOICE_FLAG_CODE = '#session.SEARCH_INVOICE_FLAG#' </cfif> <cfif session.SEARCH_flag_status eq "O"> AND ERROR.SB_ERROR_DATE is not null AND ERROR.SB_FINALISED_DATE IS NULL </cfif> <cfif session.SEARCH_flag_status eq "C"> AND ERROR.SB_FINALISED_DATE IS NOT NULL </cfif> LEFT OUTER JOIN SB_VOUCHER_CLAIM VOUCHER ON ITEM.SB_VOUCHER_ID = VOUCHER.SB_VOUCHER_ID WHERE 1=1 <!--- If called from sbmt118, will just pass invoicerun as URL ---> <cfif IsDefined("URL.invoicerun")> AND INVOICE.SB_INVOICE_PRINT_ID = #URL.invoicerun# <cfelse> <CFIF ISDEFINED("URL.HOSP")> <!--- SHOW PRE INVOICES ONLY WHEN COMING FROM SBMT117 ---> AND INVOICE.SB_INVOICE_DATE IS NULL </CFIF> <CFIF SESSION.SEARCH_MRN NEQ ""> AND (PATIENT.SB_PATIENT_MRN like <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_MRN#%"> OR MERGED_PATIENT.SB_PATIENT_MRN like <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_MRN#%"> OR PATIENT.SB_PATIENT_UPI like <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_MRN#%">) </CFIF> <CFIF SESSION.SEARCH_SURNAME NEQ ""> AND PATIENT.SB_PATIENT_SURNAME LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_SURNAME#%"> </CFIF> <CFIF SESSION.SEARCH_GIVEN NEQ ""> AND PATIENT.SB_PATIENT_OTHER_NAMES LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_GIVEN#%"> </CFIF> <CFIF SESSION.search_pat_suburb NEQ ""> AND PATIENT.sb_pat_suburb LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.search_pat_suburb#%"> </CFIF> <CFIF SESSION.SEARCH_DOB NEQ ""> AND PATIENT.SB_PATIENT_DOB = CONVERT(DATETIME, <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_GIVEN#">) </CFIF> <CFIF SESSION.SEARCH_HEALTH_FUND NEQ ""> AND EPISODE.SB_HEALTH_FUND_CODE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_HEALTH_FUND#"> </CFIF> <CFIF SESSION.search_FUND_NO NEQ ""> AND EPISODE.SB_PATIENT_HEALTH_FUND_NUMBER = '#SESSION.search_FUND_NO#' </CFIF> <CFIF SESSION.SEARCH_MEDICARE NEQ ""> AND PATIENT.SB_PATIENT_MEDICARE_NUMBER like '#SESSION.SEARCH_MEDICARE#%' </CFIF> <CFIF SESSION.SEARCH_DVA NEQ ""> AND PATIENT.SB_DVA_NUMBER like '#SESSION.SEARCH_DVA#%' </CFIF> <CFIF SESSION.SEARCH_HOSPITAL NEQ ""> AND ( INVOICE2.SB_HOSPITAL_OVERRIDE_CODE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_HOSPITAL#"> OR ( INVOICE2.SB_HOSPITAL_OVERRIDE_CODE IS NULL AND HOSPITAL.SB_HOSPITAL_CODE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_HOSPITAL#"> ) ) </CFIF> <CFIF SESSION.SEARCH_WARD NEQ ""> AND ( INVOICE2.SB_WARD_OVERRIDE_ID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_WARD#"> OR ( INVOICE2.SB_WARD_OVERRIDE_ID IS NULL AND CURRENT_WARD.SB_WARD_ID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_WARD#"> ) ) </CFIF> <cfif session.SEARCH_PROV_NUMBERS neq ""> AND INVOICE.SB_PROVIDER_NUMBER IN (#preserveSingleQuotes(session.SEARCH_PROV_NUMBERS)#) </cfif> <CFIF SESSION.SEARCH_HEALTH_FUND NEQ ""> AND EPISODE.SB_HEALTH_FUND_CODE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_HEALTH_FUND#"> </CFIF> <CFIF SESSION.SEARCH_ACCESSION NEQ ""> AND ITEM.SB_EXTERNAL_TRANS_NO = <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_ACCESSION#"> </CFIF> <CFIF SESSION.SEARCH_SB_MODALITY_ID NEQ ""> AND INVOICE.SB_SPECIALTY_CODE IN (#PreserveSingleQuotes(session.SEARCH_SB_MODALITY_ID)#) </CFIF> <CFIF SESSION.SEARCH_WCOMP NEQ ""> AND EPISODE.SB_WCOMP_CLAIM = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.SEARCH_WCOMP#"> </CFIF> <CFIF SESSION.SEARCH_BILL_MECH NEQ ""> AND INVOICE.SB_BILL_MECH_CODE = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.SEARCH_BILL_MECH#"> </CFIF> <CFIF SESSION.SEARCH_REMINDER_DAYS NEQ ""> AND INVOICE.SB_REMINDER_LEVEL = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.SEARCH_REMINDER_DAYS#"> <cfelseif SESSION.SEARCH_REMINDER_DATE_FROM neq "" or SESSION.SEARCH_REMINDER_DATE_TO neq ""> AND INVOICE.SB_REMINDER_LEVEL > 1 </CFIF> <CFIF SESSION.SEARCH_REMINDER_DATE_FROM NEQ ""> AND CONVERT(DATETIME, CONVERT(VARCHAR, INVOICE.SB_REMINDER_DATE, 103)) >= CONVERT(DATETIME, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.SEARCH_REMINDER_DATE_FROM#">) </CFIF> <CFIF SESSION.SEARCH_REMINDER_DATE_TO NEQ ""> AND CONVERT(DATETIME, CONVERT(VARCHAR, INVOICE.SB_REMINDER_DATE, 103)) <= CONVERT(DATETIME, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.SEARCH_REMINDER_DATE_TO#">) </CFIF> <CFIF SESSION.SEARCH_SERVICING_DR NEQ ""> AND INVOICE.SB_PROVIDER_NUMBER = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.SEARCH_SERVICING_DR#"> </CFIF> <CFIF SESSION.SEARCH_REQUESTING_DR NEQ ""> AND ITEM.SB_PROVIDER_NUMBER = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.SEARCH_REQUESTING_DR#"> </CFIF> <CFIF SESSION.SEARCH_SPECIALTY_CODE NEQ ""> AND INVOICE.SB_SPECIALTY_CODE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_SPECIALTY_CODE#"> </CFIF> <CFIF SESSION.SEARCH_SERVICE_DATE NEQ ""> AND CONVERT(DATETIME, CONVERT(VARCHAR, ITEM.SB_SERVICE_DATE, 103)) >= CONVERT(DATETIME, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.SEARCH_SERVICE_DATE#">) </CFIF> <CFIF SESSION.SEARCH_SERVICE_DATE_TO NEQ ""> AND CONVERT(DATETIME, CONVERT(VARCHAR, ITEM.SB_SERVICE_DATE, 103)) <= CONVERT(DATETIME, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.SEARCH_SERVICE_DATE#">) </CFIF> <CFIF SESSION.SEARCH_INVOICE_TYPE_CODE NEQ ""> AND INVOICE.SB_INVOICE_TYPE_CODE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_INVOICE_TYPE_CODE#"> </CFIF> <CFIF SESSION.SEARCH_ITEM_STATUS NEQ ""> AND ITEM.SB_ITEM_STATUS_CODE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_ITEM_STATUS#"> </CFIF> <CFIF SESSION.SEARCH_ITEM NEQ ""> AND ITEM.SB_MBS_ITEM_NUMBER = <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_ITEM#"> </CFIF> <cfif SESSION.search_pre_invoice eq "I"> AND INVOICE.SB_INVOICE_DATE IS NOT NULL <cfelseif SESSION.search_pre_invoice eq "P"> AND INVOICE.SB_INVOICE_DATE IS NULL </cfif> <CFIF SESSION.SEARCH_INVOICE NEQ ""> <cfif Left(SESSION.SEARCH_INVOICE,1) eq Group_Prefix> AND INVOICE_PRINT.SB_INVOICE_PRINT_INV_NO like <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_INVOICE#%"> <cfelse> AND INVOICE.SB_INVOICE_NUMBER like <cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_INVOICE#%"> </cfif> </CFIF> <CFIF SESSION.SEARCH_INVOICE_DATE_FROM NEQ ""> AND INVOICE.SB_INVOICE_DATE >= CONVERT(DATETIME,<cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_INVOICE_DATE_FROM#"> ) </CFIF> <CFIF SESSION.SEARCH_INVOICE_DATE_TO NEQ ""> AND INVOICE.SB_INVOICE_DATE <= CONVERT(DATETIME,<cfqueryparam cfsqltype="cf_sql_varchar" value="#SESSION.SEARCH_INVOICE_DATE_TO# 23:59:59"> ) </CFIF> <CFIF SESSION.SEARCH_CREDIT_CARD NEQ ""> AND FP.SB_CHEQUE_NO like <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.SEARCH_CREDIT_CARD#%"> AND FP.SB_FUND_PAYMENT_METHOD = 'CC' </CFIF> <CFIF SESSION.SEARCH_CHEQUE_NO NEQ ""> AND FP.SB_CHEQUE_NO like <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.SEARCH_CHEQUE_NO#%"> AND FP.SB_FUND_PAYMENT_METHOD <> 'CC' </CFIF> <CFIF SESSION.SB_PAY_TYPE NEQ ""> AND FP.SB_FUND_PAYMENT_METHOD = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.SB_PAY_TYPE#"> </CFIF> <CFIF SESSION.SEARCH_RECEIPT NEQ ""> AND FP.SB_FUND_PAYMENT_ID = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.SEARCH_RECEIPT#"> </CFIF> <CFIF SESSION.BATCH_NUM NEQ ""> AND VOUCHER.SB_CLAIM_NUMBER = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.BATCH_NUM#"> </CFIF> <CFIF SESSION.SEARCH_RECEIPT_DATE_FROM NEQ ""> AND RECEIPT.SB_CREATE_DATE >= CONVERT(DATETIME, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.SEARCH_RECEIPT_DATE_FROM#">) </CFIF> <CFIF SESSION.SEARCH_RECEIPT_DATE_TO NEQ ""> AND RECEIPT.SB_CREATE_DATE <= CONVERT(DATETIME, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.SEARCH_RECEIPT_DATE_TO# 23:59:59">) </CFIF> <cfif session.SEARCH_INVOICE_FLAG neq ""> AND ERROR.SB_INVOICE_FLAG_CODE = '#session.SEARCH_INVOICE_FLAG#' </cfif> <cfif session.SEARCH_flag_status eq "O"> AND ERROR.SB_ERROR_DATE IS NOT NULL AND ERROR.SB_FINALISED_DATE IS NULL </cfif> <cfif session.SEARCH_flag_status eq "C"> AND ERROR.SB_FINALISED_DATE IS NOT NULL </cfif> <cfif session.search_flag_find_from neq ""> <cfif session.search_flag_find eq "O"> AND ERROR.SB_ERROR_DATE >= '#DateFormat(session.search_flag_find_from,"dd-mmm-yyyy")#' <cfelse> AND ERROR.SB_FINALISED_DATE >= '#DateFormat(session.search_flag_find_from,"dd-mmm-yyyy")#' </cfif> </cfif> <cfif session.search_flag_find_to neq ""> <cfif session.search_flag_find eq "O"> AND ERROR.SB_ERROR_DATE <= '#DateFormat(session.search_flag_find_to,"dd-mmm-yyyy")#' <cfelse> AND ERROR.SB_FINALISED_DATE <= '#DateFormat(session.search_flag_find_to,"dd-mmm-yyyy")#' </cfif> </cfif> <cfif SESSION.SEARCH_NEW_DEBTOR_SINCE neq ""> AND SB_DEBTOR.SB_UPDATE_DATE >= '#DateFormat(session.SEARCH_NEW_DEBTOR_SINCE,"dd-mmm-yyyy")#' AND PATIENT.SB_DEBTOR_ID <> INVOICE.SB_DEBTOR_ID AND INVOICE.SB_DEBTOR_ID IS NOT NULL </cfif> <CFIF SESSION.SEARCH_SB_MESSAGE_ID NEQ ""> AND CLAIM.SB_MESSAGE_ID = #SESSION.SEARCH_SB_MESSAGE_ID# </CFIF> <CFIF SESSION.SEARCH_SB_CLAIM_ID NEQ ""> AND CLAIM.SB_CLAIM_ID = #SESSION.SEARCH_SB_CLAIM_ID# </CFIF> </cfif> GROUP BY INVOICE.SB_INVOICE_PRINT_ID , PATIENT.SB_PATIENT_MRN, PATIENT.SB_HL7_SOURCE, PATIENT.SB_PATIENT_SURNAME, PATIENT.SB_PATIENT_OTHER_NAMES, PATIENT.SB_PATIENT_DOB, CASE WHEN INVOICE.SB_INVOICE_PRINT_ID IS NULL THEN 'NORMAL' ELSE 'GROUP' END, CASE WHEN INVOICE.SB_INVOICE_PRINT_ID IS NULL THEN INVOICE.SB_INVOICE_ID ELSE INVOICE.SB_INVOICE_PRINT_ID END, INVOICE.SB_INVOICE_DATE, CASE WHEN INVOICE.SB_INVOICE_PRINT_ID IS NULL THEN INVOICE.SB_INVOICE_NUMBER ELSE INVOICE_PRINT.SB_INVOICE_PRINT_INV_NO END <cfif session.imaging eq "N"> ,EP_TYPE.SB_EPISODE_TYPE_DESC, EPISODE.SB_HOSPITAL_CODE, EPISODE.SB_ADMISSION_DATE, EPISODE.SB_DISCHARGE_DATE </cfif> <CFIF SESSION.SEARCH_AMOUNT_OUTSTANDING NEQ ""> <!--- Group by an Invoice when performing an Outstanding Amomunt Search ---> ,INVOICE_PRINT.SB_INVOICE_PRINT_ID HAVING isnull(( SELECT SUM(ITEM3.SB_INVOICE_COST)-SUM(ISNULL(ITEM3.SB_HIC_AMOUNT_PAID,0) + ISNULL(ITEM3.SB_FUND_AMOUNT_PAID,0)) - SUM(ISNULL(ITEM3.SB_WRITEOFF_AMOUNT,0) + ISNULL(ITEM3.SB_ADJUST_AMOUNT,0)) FROM SB_INVOICE INVOICE3 INNER JOIN SB_INVOICE_ITEM ITEM3 ON INVOICE3.SB_INVOICE_ID = ITEM3.SB_INVOICE_ID WHERE INVOICE3.SB_INVOICE_PRINT_ID = INVOICE_PRINT.SB_INVOICE_PRINT_ID AND INVOICE3.SB_PATIENT_MRN = PATIENT.SB_PATIENT_MRN AND INVOICE3.SB_HL7_SOURCE = PATIENT.SB_HL7_SOURCE ), sum(ITEM.SB_INVOICE_COST)) >0 AND isnull(( SELECT SUM(ITEM3.SB_INVOICE_COST)-SUM(ISNULL(ITEM3.SB_HIC_AMOUNT_PAID,0) + ISNULL(ITEM3.SB_FUND_AMOUNT_PAID,0)) - SUM(ISNULL(ITEM3.SB_WRITEOFF_AMOUNT,0) + ISNULL(ITEM3.SB_ADJUST_AMOUNT,0)) FROM SB_INVOICE INVOICE3 INNER JOIN SB_INVOICE_ITEM ITEM3 ON INVOICE3.SB_INVOICE_ID = ITEM3.SB_INVOICE_ID WHERE INVOICE3.SB_INVOICE_PRINT_ID = INVOICE_PRINT.SB_INVOICE_PRINT_ID AND INVOICE3.SB_PATIENT_MRN = PATIENT.SB_PATIENT_MRN AND INVOICE3.SB_HL7_SOURCE = PATIENT.SB_HL7_SOURCE ), sum(ITEM.SB_INVOICE_COST)) <= #SESSION.SEARCH_AMOUNT_OUTSTANDING# </CFIF> ORDER BY <cfif session.search_order eq "PatientName"> PATIENT.SB_PATIENT_SURNAME, PATIENT.SB_PATIENT_OTHER_NAMES <cfelseif session.search_order eq "InvNo"> CASE WHEN INVOICE.SB_INVOICE_PRINT_ID IS NULL THEN INVOICE.SB_INVOICE_NUMBER ELSE INVOICE_PRINT.SB_INVOICE_PRINT_INV_NO END <cfelseif session.search_order eq "InvNoDesc"> CASE WHEN INVOICE.SB_INVOICE_PRINT_ID IS NULL THEN INVOICE.SB_INVOICE_NUMBER ELSE INVOICE_PRINT.SB_INVOICE_PRINT_INV_NO END desc <cfelseif session.search_order eq "InvDate"> INVOICE.SB_INVOICE_DATE <cfelseif session.search_order eq "InvDateDesc"> INVOICE.SB_INVOICE_DATE DESC <cfelseif session.search_order eq "InvBal"> ISNULL(( SELECT SUM(ITEM3.SB_INVOICE_COST)-SUM(ISNULL(ITEM3.SB_HIC_AMOUNT_PAID,0) + ISNULL(ITEM3.SB_FUND_AMOUNT_PAID,0)) - SUM(ISNULL(ITEM3.SB_WRITEOFF_AMOUNT,0) + ISNULL(ITEM3.SB_ADJUST_AMOUNT,0)) FROM SB_INVOICE INVOICE3 INNER JOIN SB_INVOICE_ITEM ITEM3 ON INVOICE3.SB_INVOICE_ID = ITEM3.SB_INVOICE_ID WHERE INVOICE3.SB_INVOICE_PRINT_ID = INVOICE.SB_INVOICE_PRINT_ID AND INVOICE3.SB_PATIENT_MRN = PATIENT.SB_PATIENT_MRN AND INVOICE3.SB_HL7_SOURCE = PATIENT.SB_HL7_SOURCE ),0) <cfelseif session.search_order eq "InvBalDesc"> ISNULL(( SELECT SUM(ITEM3.SB_INVOICE_COST)-SUM(ISNULL(ITEM3.SB_HIC_AMOUNT_PAID,0) + ISNULL(ITEM3.SB_FUND_AMOUNT_PAID,0)) - SUM(ISNULL(ITEM3.SB_WRITEOFF_AMOUNT,0) + ISNULL(ITEM3.SB_ADJUST_AMOUNT,0)) FROM SB_INVOICE INVOICE3 INNER JOIN SB_INVOICE_ITEM ITEM3 ON INVOICE3.SB_INVOICE_ID = ITEM3.SB_INVOICE_ID WHERE INVOICE3.SB_INVOICE_PRINT_ID = INVOICE.SB_INVOICE_PRINT_ID AND INVOICE3.SB_PATIENT_MRN = PATIENT.SB_PATIENT_MRN AND INVOICE3.SB_HL7_SOURCE = PATIENT.SB_HL7_SOURCE ),0) desc <cfelseif session.search_order eq "MRN"> PATIENT.SB_PATIENT_MRN <cfelse> PATIENT.SB_PATIENT_SURNAME, PATIENT.SB_PATIENT_OTHER_NAMES </cfif> set rowcount 0 </cfquery> >>> [EMAIL PROTECTED] 07/18/05 9:35 am >>> Speaking out of ignorance, but why not just store and pass the SQL as a variable? Many of my queries look like: <cfquery> #MyQueryString# </cfquery> This may not be best practice, though, look forward toi being corrected. Chad who will admit to watching "Annie" with his kid this weekend. On 7/18/05, Scott Thornton <[EMAIL PROTECTED]> wrote: > Hi, > > Is it possible to get hold of the sql string from a cfquery after it has executed? Particualrly, is it possible to get it even if debug is turned off? > > I have a particularly difficult search page, with mutliple table being included / excluded , and different group-bys depending on paramters.... and now the users want to print the page using Crystal Reports. My thoughts are to pass the query string to a stored procedure, and base the crystal report on it. > > using cfmx 6.1 > > cheers, > > > Scott Thornton, Programmer > Application Development > Information Services and Telecommunications > Hunter-New England Area Health Service > Phone RNH +61 2 49236078 > Fax +61 2 49236076 > > [EMAIL PROTECTED] > > > --- > 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/ --- 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/
