Cold Fusion/SQL query error being thrown...
I am getting an error when trying to run a SQL query/report from within a cfm file. The error is being throw due to the fact that there's no data to pull from for one of the options in the drop down menu for the last couple of years. No information to pull from. Any ideas on how to fix the query or CF to prevent the error when there's no data to pull from in the db?
Error and queries below:
The error occurred in D:/inetpub/wwwroot/CASAPay
114 :
115 : <!--Summing columns with CFSET declaration... -->
116 : <cfset countttl = #invcounts.15Count# + #invcounts.1530C# + #invcounts.30plusC#>
117 : <cfset dollarttl = #invcounts.less15days# + #invcounts.15to30Days# + #invcounts.30plusDays#>
Error and queries below:
The error occurred in D:/inetpub/wwwroot/CASAPay
114 :
115 : <!--Summing columns with CFSET declaration... -->
116 : <cfset countttl = #invcounts.15Count# + #invcounts.1530C# + #invcounts.30plusC#>
117 : <cfset dollarttl = #invcounts.less15days# + #invcounts.15to30Days# + #invcounts.30plusDays#>
<cfcomponent>
<cffunction name="casapay1" access="public" returntype="query">
<!--Set global datasource variable -->
<cfset #dsn# = "mirrepuser">
<cfparam name="form.agency" default="">
<cfparam name="form.period" default="">
<cfquery name="invCounts" datasource="#dsn#">
SELECT
/*+ INDEX (f FV_INV_SELECTED_DUEDATE_IDX) */
--Within 15 days
SUM(
CASE
WHEN
P.ACCOUNTING_DATE BETWEEN i.terms_date AND i.terms_date + 15
THEN
i.invoice_amount
ELSE
0
END
) "less15days", SUM(
CASE
WHEN
P.ACCOUNTING_DATE BETWEEN i.terms_date AND i.terms_date + 15
THEN
1
ELSE
0
END
) "15COUNT", --Between 15-30 Days
SUM(
CASE
WHEN
P.ACCOUNTING_DATE BETWEEN i.terms_date + 16 AND i.terms_date + 30
THEN
i.invoice_amount
ELSE
0
END
) "15to30days", SUM(
CASE
WHEN
P.ACCOUNTING_DATE BETWEEN i.terms_date + 16 AND i.terms_date + 30
THEN
1
ELSE
0
END
) "1530C", --After 30 Days
SUM(
CASE
WHEN
P.ACCOUNTING_DATE > i.terms_date + 30
THEN
i.invoice_amount
ELSE
0
END
) "30plusdays", SUM(
CASE
WHEN
P.accounting_date > i.terms_date + 30
THEN
1
ELSE
0
END
) "30plusC"
FROM
ap.ap_terms_tl t , ap.ap_invoice_payments_all p , ap.ap_invoices_all i , ap.ap_payment_schedules_all s
WHERE
t.NAME <> 'PROMPT SB NET 30' --take out small business terms
--and t.NAME not like 'STANDARD%' -- take out standard terms
--and nvl(P.DISCOUNT_TAKEN,0) =0 --take out discounts paid
AND I.PAY_GROUP_LOOKUP_CODE IN
(
'VENDOR', 'VENDOR No-Bud', 'NONGOV-PAY-OTHER-BUD', 'NONGOV-PAY-ADV-NO BUD', 'NONGOV-PAY SUS NO BUD'
)
AND T.TERM_ID = I.TERMS_ID
AND p.invoice_id = i.invoice_id
AND p.invoice_id = s.invoice_id
AND p.org_id = '#form.agency#'
AND p.accounting_date BETWEEN (
SELECT
start_date
FROM
gl.gl_periods a, gl.gl_sets_of_books b, fv.fv_operating_units_all c
WHERE
a.period_name = UPPER('#form.period#') AND a.period_set_name = b.period_set_name
AND b.set_of_books_id = c.set_of_books_id
AND c.organization_id = '#form.agency#' )
AND
(
SELECT
end_date
FROM
gl.gl_periods a,
gl.gl_sets_of_books b,
fv.fv_operating_units_all c
WHERE
a.period_name = UPPER('#form.period#')
AND a.period_set_name = b.period_set_name
AND b.set_of_books_id = c.set_of_books_id
AND c.organization_id = '#form.agency#'
)
AND I.invoice_type_lookup_code IN
(
'STANDARD',
'MIXED'
)
AND P.REVERSAL_FLAG IS NULL
AND i.invoice_amount > 0
</cfquery>
<cfreturn invCounts>
</cffunction>
<cffunction name="casapay2" access="public" returntype="query">
<cfset #dsn# = "mirrepuser">
<cfquery name="intpaid" datasource="#dsn#" >
SELECT
COUNT(*) paid,
SUM(ii.invoice_amount) intpaid
FROM
ap.ap_terms_tl t,
ap.ap_invoice_relationships r,
ap.ap_invoices_all i,
ap.ap_invoices_all ii,
ap.ap_invoice_payments_all p,
fv.fv_inv_selected_duedate f
WHERE
t.NAME <> 'PROMPT SB NET 30'
AND T.TERM_ID = I.TERMS_ID
AND I.PAYMENT_STATUS_FLAG = 'Y'
AND I.PAY_GROUP_LOOKUP_CODE IN
(
'VENDOR',
'VENDOR No-Bud',
'NONGOV-PAY-OTHER-BUD',
'NONGOV-PAY-ADV-NO BUD',
'NONGOV-PAY SUS NO BUD'
)
AND i.invoice_id = R.ORIGINAL_INVOICE_ID
AND R.RELATED_INVOICE_ID = ii.invoice_id
AND II.INVOICE_TYPE_LOOKUP_CODE = 'INTEREST'
AND p.org_id = '#form.agency#'
AND p.accounting_date BETWEEN (
SELECT
start_date
FROM
gl.gl_periods a, gl.gl_sets_of_books b, fv.fv_operating_units_all c
WHERE
a.period_name = UPPER('#form.period#') AND a.period_set_name = b.period_set_name
AND b.set_of_books_id = c.set_of_books_id
AND c.organization_id = '#form.agency#' )
AND
(
SELECT
end_date
FROM
gl.gl_periods a,
gl.gl_sets_of_books b,
fv.fv_operating_units_all c
WHERE
a.period_name = UPPER('#form.period#')
AND a.period_set_name = b.period_set_name
AND b.set_of_books_id = c.set_of_books_id
AND c.organization_id = '#form.agency#'
)
AND P.REVERSAL_FLAG IS NULL
AND I.INVOICE_ID = P.INVOICE_ID
AND f.invoice_id = i.invoice_id
</cfquery>
<cfreturn intpaid>
</cffunction>
<cffunction name="casapay3" access="public" returntype="query">
<cfset #dsn# = "mirrepuser">
<cfquery name="paid" datasource="#dsn#">
SELECT
COUNT(*) paid
FROM
ap.ap_terms_tl t,
ap.ap_invoice_relationships r,
ap.ap_invoices_all i,
ap.ap_invoices_all ii,
ap.ap_invoice_payments_all p,
fv.fv_inv_selected_duedate f
WHERE
t.NAME <> 'PROMPT SB NET 30'
AND T.TERM_ID = I.TERMS_ID
AND I.PAYMENT_STATUS_FLAG = 'Y'
AND I.PAY_GROUP_LOOKUP_CODE IN
(
'VENDOR',
'VENDOR No-Bud',
'NONGOV-PAY-OTHER-BUD',
'NONGOV-PAY-ADV-NO BUD',
'NONGOV-PAY SUS NO BUD'
)
AND i.invoice_id = R.ORIGINAL_INVOICE_ID
AND R.RELATED_INVOICE_ID = ii.invoice_id
AND II.INVOICE_TYPE_LOOKUP_CODE = 'INTEREST'
AND p.org_id = '#form.agency#'
AND p.accounting_date BETWEEN (
SELECT
start_date
FROM
gl.gl_periods a, gl.gl_sets_of_books b, fv.fv_operating_units_all c
WHERE
a.period_name = UPPER('#form.period#') AND a.period_set_name = b.period_set_name
AND b.set_of_books_id = c.set_of_books_id
AND c.organization_id = '#form.agency#' )
AND
(
SELECT
end_date
FROM
gl.gl_periods a,
gl.gl_sets_of_books b,
fv.fv_operating_units_all c
WHERE
a.period_name = UPPER('#form.period#')
AND a.period_set_name = b.period_set_name
AND b.set_of_books_id = c.set_of_books_id
AND c.organization_id = '#form.agency#'
)
AND P.REVERSAL_FLAG IS NULL
AND I.INVOICE_ID = P.INVOICE_ID
AND f.invoice_id = i.invoice_id
</cfquery>
<cfreturn paid>
</cffunction>
</cfcomponent>ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oops.... thanks @gdmemaria.. My bad.. missed the val for other two :)
-Sri
-Sri
ASKER
Thanks to both answers. I'll try to work this today and get back to you. It may be Monday though or over the weekend. I just submitted another question that I have to deal w/ first. Its a simple formatting issue but its taken priority for now over this one. If either of you want to take a peak at that it would be great. I'm guessing its a span issue w/ the header image not showing in the cfm file but parsing correctly in the html file as I don't see anything different that would cause it.
thanks again.
thanks again.
ASKER
I'm getting this error after adding the proposed solution:
Element 15COUNT is undefined in INVCOUNTS.
The error occurred in D:/inetpub/wwwroot/CASAPay/casapay.cfc: line 17
15 : <!--Set global datasource variable -->
16 : <cfset dsn = "mirrepuser">
17 : <cfset countttl = val(invcounts.15Count) + val(invcounts.1530C) + val(invcounts.30plusC)>
18 :
19 : <!---cfset countttl = 0 />
has the code changed or is the code from your first post still applicable? Do you ever NOT call the query? If invCounts is the name of the query then that value should exist even if no records are returned.
You can try doing a <cfdump var="#invCounts#"> before line 15 there and see what's in that variable. It should be just your query dumped.
In short, you have to make sure your query is run before these lines and your query is named InvCounts
You can try doing a <cfdump var="#invCounts#"> before line 15 there and see what's in that variable. It should be just your query dumped.
In short, you have to make sure your query is run before these lines and your query is named InvCounts
ASKER
its kicking over to casoutpay.cfm and erroring according to the log file. See below:
"Error","ajp-bio-8012-exec-10","08/01/18","11:00:37",,"Element 15COUNT is undefined in INVCOUNTS. The specific sequence of files included or processed is: D:\inetpub\wwwroot\CASAPay\casapay_out.cfm, line: 17 "
"Error","ajp-bio-8012-exec-10","08/01/18","11:02:40",,"Element RECORDCOUNT is undefined in INVCOUNTS. The specific sequence of files included or processed is: D:\inetpub\wwwroot\CASAPay\casapay_out.cfm, line: 20 "
"Error","ajp-bio-8012-exec-10","08/01/18","11:02:51",,"Element RECORDCOUNT is undefined in INVCOUNTS. The specific sequence of files included or processed is: D:\inetpub\wwwroot\CASAPay\casapay_out.cfm, line: 20 "
"Error","ajp-bio-8012-exec-10","08/01/18","11:03:10",,"Element RECORDCOUNT is undefined in INVCOUNTS. The specific sequence of files included or processed is: D:\inetpub\wwwroot\CASAPay\casapay_out.cfm, line: 20 "
"Error","ajp-bio-8012-exec-10","08/01/18","11:06:08",,"Element 15COUNT is undefined in INVCOUNTS. The specific sequence of files included or processed is: D:\inetpub\wwwroot\CASAPay\casapay_out.cfm, line: 17 "
"Error","ajp-bio-8012-exec-10","08/01/18","11:06:12",,"Element 15COUNT is undefined in INVCOUNTS. The specific sequence of files included or processed is: D:\inetpub\wwwroot\CASAPay\casapay_out.cfm, line: 17 "
"Error","ajp-bio-8012-exec-10","08/01/18","11:06:16",,"Element 15COUNT is undefined in INVCOUNTS. The specific sequence of files included or processed is: D:\inetpub\wwwroot\CASAPay\casapay_out.cfm, line: 17 "
"Error","ajp-bio-8012-exec-10","08/01/18","12:35:29",,"Element 15COUNT is undefined in INVCOUNTS. The specific sequence of files included or processed is: D:\inetpub\wwwroot\CASAPay\casapay_out.cfm, line: 245 "
"Error","ajp-bio-8012-exec-1","08/01/18","12:35:30",,"Element 15COUNT is undefined in INVCOUNTS. The specific sequence of files included or processed is: D:\inetpub\wwwroot\CASAPay\casapay_out.cfm, line: 245 "
"Error","ajp-bio-8012-exec-10","08/01/18","12:37:00",,"Element 15COUNT is undefined in INVCOUNTS. The specific sequence of files included or processed is: D:\inetpub\wwwroot\CASAPay\casapay_out.cfm, line: 245 "
I don't know where your query is located compared to casoutpay.cfm
Based on your errors, it seems that your query is not being called before you start referencing it. That needs to be fixed.
Based on your errors, it seems that your query is not being called before you start referencing it. That needs to be fixed.
ASKER
here's the code for the casoutpay.cfm:
Where should I insert <cfset countttl = val(invcounts.15Count) + val(invcounts.1530C) + val(invcounts.30plusC)> ... in the cfc file? cfm file? both?
I also need to add a message saying "No Results Returned" if there's no data to pull from, but that can wait for now. Thanks a ton for all your help. I'm still new to CF. I don't work it in enough to improve greatly. Mostly in spurts. Thanks again.
<cfif (#form.agency# EQ "101") AND (#Len(form.period)# NEQ 6)>
<body onLoad="alert('Please be sure to enter the correct date format for this Agency...MON-YY');window.close();"></body>
<cfelseif (#form.agency# EQ "2803") AND (#Len(form.period)# NEQ 8)>
<body onLoad="alert('Please be sure to enter the correct date format for this Agency...MON-YYYY');window.close();"></body>
<cfelseif (#form.agency# EQ "4659") AND (#Len(form.period)# NEQ 8)>
<body onLoad="alert('Please be sure to enter the correct date format for this Agency...MON-YYYY');window.close();"></body>
<cfelse>
<cfset #date# = #DateFormat("#Now()#","mmmm dd, yyyy")#>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML LANG="EN-US">
<head>
<title>XXXXXXXXXXXXXXXX</title>
<link href="../css/home.css" rel="stylesheet" type="text/css" />
</head>
<body onLoad="init()">
<div id="loading" style="position:absolute; width:100%; text-align:center; top:300px;">
<img src="../images/spiral.gif" border=0></div>
<script>
var ld=(document.all);
var ns4=document.layers;
var ns6=document.getElementById&&!document.all;
var ie4=document.all;
if (ns4)
ld=document.loading;
else if (ns6)
ld=document.getElementById("loading").style;
else if (ie4)
ld=document.all.loading.style;
function init()
{
if(ns4){ld.visibility="hidden";}
else if (ns6||ie4) ld.display="none";
}
</script>
<table width="100%" height="100%" border="0" cellpadding="0" cellspacing="0" background="../images/grad2b.gif">
<tr>
<td width="10%"> </td>
<td width="70%" valign="top">
<table width="60%" border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" >
<tr>
<td><a href="../index.htm"><img src="../images/IntranetBanner_010909.jpg" alt="XXXXXXXXXXXbanner" width="800" height="82" border="0" ></a></td>
</tr>
<tr>
<td><CF_topmenu_new></td>
</tr>
<tr>
<td valign="top">
<table width="100%" border="0" align="center">
<tr>
<td>
<cfinvoke component="casapay" method="casapay1" argumentcollection="#form#" returnvariable="invcounts" >
</cfinvoke>
<cfinvoke component="casapay" method="casapay2" argumentcollection="#form#" returnvariable="intpaid" >
</cfinvoke>
<cfinvoke component="casapay" method="casapay3" argumentcollection="#form#" returnvariable="paid" >
</cfinvoke>
<table width="100%" border="0">
<tr>
<td><cfoutput>#date#</cfoutput></td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td><div align="center"><b>XXXXXXXXXXXXXXXXX</b></div></td>
</tr>
<tr>
<td>
<div align="center">
<b>For Agency:
<cfif #form.agency# eq 101>
<cfoutput>USCG</cfoutput>
</cfif>
<cfif #form.agency# eq 2803>
<cfoutput>TSA</cfoutput>
</cfif>
<cfif #form.agency# eq 4659>
<cfoutput>DNDO</cfoutput>
</cfif>
</b>
</div>
</td>
</tr>
<tr>
<td><div align="center"><b>For Period: <cfoutput>#form.period#</cfoutput></b></div></td>
</tr>
<tr>
<td><br>
<cfif invcounts.recordcount eq "0">
<span class="FormLabel_Red14">Sorry no data was found for this report: Terms “PROMPT SB NET 30’ was not used during this period.</span>
</cfif>
<!--Summing columns with CFSET declaration... -->
<cfset countttl = #invcounts.15Count# + #invcounts.1530C# + #invcounts.30plusC#>
<cfset dollarttl = #invcounts.less15days# + #invcounts.15to30Days# + #invcounts.30plusDays#>
<table width="745" border="0">
<tr>
<td width="389"> </td>
<td width="53"> </td>
<td width="118"><div align="center"><b>No. of Invoices</b></div></td>
<td width="28"> </td>
<td width="135"><div align="right"><b>Dollar Amount</b></div></td>
</tr>
<cfoutput>
<tr>
<td><a href="getFifteens_nested.cfm?period=#form.period#&agency=#form.agency#" target="_blank">Invoices Paid Within 15 Days</a></td>
<td> </td>
<td><div align="center"><cfoutput>#invcounts.15Count#</cfoutput></div></td>
<td> </td>
<td><div align="right"><cfoutput>#dollarformat(invcounts.less15days)#</cfoutput></div></td>
</tr>
<tr>
<td><a href="getFifteen2thirties.cfm?period=#form.period#&agency=#form.agency#" target="_blank">Invoices Paid Between 15-30 Days</a></td>
<td> </td>
<td><div align="center"><cfoutput>#invcounts.1530C#</cfoutput></div></td>
<td> </td>
<td><div align="right"><cfoutput>#dollarformat(invcounts.15to30Days)#</cfoutput></div></td>
</tr>
<tr>
<td><a href="getOverthirties.cfm?period=#form.period#&agency=#form.agency#" target="_blank">Invoices Paid After 30 Days</a></td>
<td> </td>
<td><div align="center"><cfoutput>#invcounts.30plusC#</cfoutput></div></td>
<td> </td>
<td><div align="right"><cfoutput>#dollarformat(invcounts.30plusDays)#</cfoutput></div></td>
</tr>
</cfoutput>
<tr>
<td> </td>
<td> </td>
<td><div align="center"><cfoutput>______________</cfoutput></div></td>
<td> </td>
<td><div align="right"><cfoutput>______________</cfoutput></div></td>
</tr>
<tr>
<td><b>Total Invoices Paid</b></td>
<td> </td>
<td><div align="center"><cfoutput>#countttl#</cfoutput></div></td>
<td> </td>
<td><div align="right"><cfoutput>#dollarformat(dollarttl)#</cfoutput></div></td>
</tr>
<tr>
<td> </td>
<td> </td>
<td><div align="center"></div></td>
<td> </td>
<td><div align="right"></div></td>
</tr>
<tr>
<td>Amount of Interest Paid</td>
<td> </td>
<td><div align="center"><cfoutput query="paid">#paid.paid#</cfoutput></div></td>
<td> </td>
<td><div align="right"><cfoutput>#dollarformat(intpaid.intpaid)#</cfoutput></div></td>
</tr>
</table></td>
</tr>
<td> </td>
</tr>
<tr>
<td> </td>
</tr>
</table> </td>
</tr>
<tr>
<td> </td>
</tr>
</table></td>
</tr>
<tr>
<td><span class="FormLabel11BluBld">Balances do not include Prompt SB NET 30 Terms
<!-- or invoices where a discount was taken.
--> </span>
</td>
</tr>
<tr>
<td><CF_CGfooter_new></td>
</tr>
</table></td>
<td width="10%"> </td>
</tr>
<tr>
<td width="10%"> </td>
<td width="80%"> </td>
<td width="10%"> </td>
</tr>
<tr>
<td width="10%"> </td>
<td width="80%"> </td>
</tr>
</table>
</body>
</html>
</cfif>Where should I insert <cfset countttl = val(invcounts.15Count) + val(invcounts.1530C) + val(invcounts.30plusC)> ... in the cfc file? cfm file? both?
I also need to add a message saying "No Results Returned" if there's no data to pull from, but that can wait for now. Thanks a ton for all your help. I'm still new to CF. I don't work it in enough to improve greatly. Mostly in spurts. Thanks again.
ASKER
Any thoughts?
Curious. It looks like your code is returning the query into the variable invCounts, but let's do some tests to see what is happening.
At this spot of code, let's check to see what we are getting back from the CFINVOKE that returns invCounts...
At this spot of code, let's check to see what we are getting back from the CFINVOKE that returns invCounts...
<tr>
<td><CF_topmenu_new></td>
</tr>
<tr>
<td valign="top">
<table width="100%" border="0" align="center">
<tr>
<td>
<!--------------- this is where your query is being returned from ---------------------------->
<cfinvoke component="casapay" method="casapay1" argumentcollection="#form#" returnvariable="invcounts" >
</cfinvoke>
<cfinvoke component="casapay" method="casapay2" argumentcollection="#form#" returnvariable="intpaid" >
</cfinvoke>
<cfinvoke component="casapay" method="casapay3" argumentcollection="#form#" returnvariable="paid" >
</cfinvoke>
<!-------------- let's test to see what you're getting back ------------->
<cfif isQuery(invcounts)>
Yes. It is a query
<cfelse>
NO. Error - a query was not returned
</cfif>
<cfdump var="#invCounts#">
<table width="100%" border="0">
<tr>
<td><cfoutput>#date#</cfoutput></td>ASKER
Getting this error from app log:
"Error","ajp-bio-8012-exec-10","08/01/18","16:04:42",,"Element 15COUNT is undefined in INVCOUNTS. The specific sequence of files included or processed is: D:\inetpub\wwwroot\CASAPay\casapay_out.cfm, line: 130 "ASKER
that was after adding the <cfdump var="#invCounts#"> to the location above
what does the code that you added show? yes or No is it a query and what does the dump look like?
If the page is an error and you cannot see the output, put a <CFEXIT> right after the CFDUMP so we can stop processing and see it...
If the page is an error and you cannot see the output, put a <CFEXIT> right after the CFDUMP so we can stop processing and see it...
ASKER
I'll check this again later today...early morning. Have to head to a meeting. Thanks for your assistance. Greatly appreciated.
ASKER
Thanks for all your assistance. Much appreciated.
You might want to add default value and check if record exists and do the addition part like below
<cfset countttl = 0 />
<cfif invcounts.recordcount>
<cfset countttl = invcounts.15Count + invcounts.1530C+ invcounts.30plusC />
</cfif>
OR user val like below
<cfset countttl = val(invcounts.15Count) + (invcounts.1530C)+ (invcounts.30plusC) />
-Sri