Stumped... Crystal returns no results for this command

[fmrock]

I have this large command that pulls from our Oracle Database.

When I run the query in SQL+ against the production and backup database, I get results returned. However, when I try to run the query through crystal, no results are returned.

Any ideas?

SELECT 
	T1.CPCODE "Group_Num"
	,T1.LOCNAME "Site_Name"	
	,FC.CDESC "FC_Desc"
	,add_months(T1.FY_start,0) "dtMonth12"	
	,add_months(T1.FY_start,1) "dtMonth11"	
	,add_months(T1.FY_start,2) "dtMonth10"
	,add_months(T1.FY_start,3) "dtMonth9"
	,add_months(T1.FY_start,4) "dtMonth8"
	,add_months(T1.FY_start,5) "dtMonth7"	
	,add_months(T1.FY_start,6) "dtMonth6"	
	,add_months(T1.FY_start,7) "dtMonth5"	
	,add_months(T1.FY_start,8) "dtMonth4"	
	,add_months(T1.FY_start,9) "dtMonth3"
	,add_months(T1.FY_start,10) "dtMonth2"	
	,add_months(T1.FY_start,11) "dtMonth1"
	,SUM(CASE WHEN T1.POSTDATE=add_months(T1.FY_start,0) THEN T1.Amt ELSE 0 END) "Month12"		
	,SUM(CASE WHEN T1.POSTDATE=add_months(T1.FY_start,1) THEN T1.Amt ELSE 0 END) "Month11"		
	,SUM(CASE WHEN T1.POSTDATE=add_months(T1.FY_start,2) THEN T1.Amt ELSE 0 END) "Month10"		
	,SUM(CASE WHEN T1.POSTDATE=add_months(T1.FY_start,3) THEN T1.Amt ELSE 0 END) "Month9"		
	,SUM(CASE WHEN T1.POSTDATE=add_months(T1.FY_start,4) THEN T1.Amt ELSE 0 END) "Month8"		
	,SUM(CASE WHEN T1.POSTDATE=add_months(T1.FY_start,5) THEN T1.Amt ELSE 0 END) "Month7"		
	,SUM(CASE WHEN T1.POSTDATE=add_months(T1.FY_start,6) THEN T1.Amt ELSE 0 END) "Month6"		
	,SUM(CASE WHEN T1.POSTDATE=add_months(T1.FY_start,7) THEN T1.Amt ELSE 0 END) "Month5"		
	,SUM(CASE WHEN T1.POSTDATE=add_months(T1.FY_start,8) THEN T1.Amt ELSE 0 END) "Month4"		
	,SUM(CASE WHEN T1.POSTDATE=add_months(T1.FY_start,9) THEN T1.Amt ELSE 0 END) "Month3"		
	,SUM(CASE WHEN T1.POSTDATE=add_months(T1.FY_start,10) THEN T1.Amt ELSE 0 END) "Month2"		
	,SUM(CASE WHEN T1.POSTDATE=add_months(T1.FY_start,11) THEN T1.Amt ELSE 0 END) "Month1"		
	,Sum(T1.Amt) "Total_Pmts"
	,T1.FY_start "Start_Date"
	,T1.FY_end "End_Date"
FROM 
	(SELECT 
			C.CPCODE
			, L.LOCNAME
			, C.BILLSTATUS
			, TRUNC(CASE WHEN (C.BATCHNBR='888888') THEN to_date(C.POSTDATE,'j') ELSE NVL(to_date(C.ORIGPOSTDATE,'j'),to_date(C.POSTDATE,'j'))END ,'MONTH') POSTDATE
			, to_date('01-APR-' || to_char(add_months(trunc(sysdate),-4),'YYYY')) FY_start
			, to_date('31-MAR-' || to_char(add_months(trunc(sysdate),+8),'YYYY')) FY_end
			, SUM(C.AMOUNT) Amt
		FROM 
			MEDLOCATIONS L
			JOIN MEDCHARGES C ON ((C.CPCODE = L.LOCCPCODE) AND (L.LOCCODE=C.DOCLOC))
		WHERE
			(L.LOCCPCODE IN ('116001','116002','116003'))
			AND (C.TYPE='P') 
			AND (
					(C.POSTDATE BETWEEN to_char(to_date('01-APR-' || to_char(add_months(trunc(sysdate),-4),'YYYY')),'J') AND to_char(to_date('31-MAR-' || to_char(add_months(trunc(sysdate),+8),'YYYY')),'J'))
					OR (C.ORIGPOSTDATE BETWEEN to_char(to_date('01-APR-' || to_char(add_months(trunc(sysdate),-4),'YYYY')),'J') AND to_char(to_date('31-MAR-' || to_char(add_months(trunc(sysdate),+8),'YYYY')),'J'))
				)
		GROUP BY
			C.CPCODE
			, L.LOCNAME
			, C.BILLSTATUS
			, TRUNC(CASE WHEN (C.BATCHNBR='888888') THEN to_date(C.POSTDATE,'j') ELSE NVL(to_date(C.ORIGPOSTDATE,'j'),to_date(C.POSTDATE,'j'))END ,'MONTH')		
			, to_date('01-APR-' || to_char(add_months(trunc(sysdate),-4),'YYYY'))
			, to_date('31-MAR-' || to_char(add_months(trunc(sysdate),+8),'YYYY'))	
	) T1
	LEFT JOIN FINANCIALCODES FC ON ((FC.FCCODE=T1.CPCODE) AND (FC.CODES=T1.BILLSTATUS))
GROUP BY
	T1.CPCODE
	, T1.LOCNAME
	,FC.CDESC 
	,T1.FY_start 
	,T1.FY_end

 

[kenhamady]

You are probably using a different type of connection from CR than you are from within the database, so you might get a slightly different interpretation.

I would start by seeing if the problem is the SQL or the connection itself (like a permissions issue). First start by making all joins left outer, then start removing WHERE criteria one at a time until you start seeing results. When you see results the last change will point to the issue.

If all joins are left outer and there is no WHERE at all then the problem is probably the connection you are using not letting you see any data.

You could also test running just the sub-select portion to see if it returns any results.

Good Luck

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/