Issue with Coldfusion QoQ
I have three queries on my page:
1) Query #1: calculates a leave time value (ex 100)
2) Query #2: calculates a used time value: (ex 😎
3) Query #3 is a query of queries that should calculate the difference between the first two values.
First two are working fine, but QoQ is giving me the following error:
The system has attempted to use an undefined value, which usually indicates a programming error, either in your code or some system code.
Null Pointers are another name for undefined values.
What is the issue here?
<!---Create "Accrued Leave Time" (for current year) Dataset --->
<cfquery name="rsAvailableLeaveTime" datasource="care">
SELECT COALESCE(SUM(tblleavetime.ltHours),0) AS Accrued_LeaveTime, ltUser
FROM tblleavetime LEFT OUTER JOIN tblusers ON tblusers.username = tblleavetime.ltUser
WHERE tblleavetime.ltUser = <cfqueryparam value="#SESSION.kt_login_user#" cfsqltype="cf_sql_clob" maxlength="255">
AND YEAR(tblleavetime.ltCreated) = YEAR(CURDATE())
</cfquery>
<!---Create "Used Leave Time" (for current year) Dataset --->
<cfquery name="rsUsedLeaveTime" datasource="care">
SELECT COALESCE(SUM(lqHoursPL),0) AS Used_LeaveTimePL, COALESCE(SUM(lqHoursPB),0) AS Used_LeaveTimePB, COALESCE(SUM(lqHoursUL),0) AS Used_LeaveTimeUL, lqUser,
COALESCE(SUM(lqHoursPL),0) AS Used_LeaveTimeSum
FROM tblleaverequest
WHERE tblleaverequest.lqUser = <cfqueryparam value="#SESSION.kt_login_user#" cfsqltype="cf_sql_clob" maxlength="255">
AND lqApproved LIKE "Y"
AND YEAR(tblleaverequest.lqStartDate) = YEAR(CURDATE())
</cfquery>
<!---Create "Remaining Leave Time" using a QoQ (for current year) Dataset --->
<cfquery name="rsRemainingLeaveTime" dbtype="query">
SELECT *, Accrued_LeaveTime - Used_LeaveTimeSum AS Remaining_LeaveTime
FROM rsAvailableLeaveTime, rsUsedLeaveTime
WHERE ltUser = lqUser
</cfquery>
