Issue with Coldfusion QoQ

[jlig]

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 8)

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>

[jlig]

When I do a cfdump on the third query (QoQ) I get:

I've tried everything such as:

- scoping the columns

- using CAST on the columns

- I already have COALESCE on the first two queries columns that are feeding the QoQ

I just cannot get this query to give me any output?

[jlig]

The issue is actually with the second query..

- I removed the date filter: AND YEAR(tblleaverequest.lqStartDate) = YEAR(CURDATE()) and the error goes away?

- It's caused only when the query returns no records.

- How can I make it return a '0' when no records are available?

[jlig · Accepted solution]

I was able to fix my issue by doing the following:

1) I created a sub-query VIEW to do the YEAR filtering

2) then I used that VIEW as the source query for my original second query