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/
