SQL works on Oracle, but in Actuate I get a datatype error

[t16turbo]

When I run the SQL below (which is built using the ObtainSelectStatement) I get the following message:

"Basic Error: 1011 Module: C:\Program Files\Actuate6\ErdPro\afc\db.bas Line: 193 Database Error 18: Datatypes of the bound variable and column are not compatible. "

SQL:

SELECT 
x.dsti_proj_ref, a.transtype, a.transdate, sum(NVL(a.quantity,0)) quantity, a.project_code, a.resource_code, b.currency_code, NVL(b.stdcost,0) stdcost,  NVL(b.totalcost,0) totalcost, NVL(b.billrate,0) billrate, NVL(b.amount,0) amount, c.company_name, d.name project_name, e.first_name, e.last_name, NVL(b.amountremaining, 0) amountremaining, SUM(NVL(g.amount,0)) amountbilled 
FROM (select wipunion.TRANSNO, wipunion.TRANSTYPE, wipunion.TRANSDATE, wipunion.QUANTITY, wipunion.PROJECT_CODE, wipunion.RESOURCE_CODE, wipunion.COMPANY_CODE, wipunion.STATUS, wipunion.TASK_ID, f.transno f_transno, f.transtype f_transtype, f.status f_status FROM(SELECT x.TRANSNO, x.TRANSTYPE, x.TRANSDATE, x.QUANTITY, x.PROJECT_CODE, x.RESOURCE_CODE, x.COMPANY_CODE, x.STATUS, x.TASK_ID FROM ppa_wip x UNION ALL SELECT y.TRANSNO, y.TRANSTYPE, y.TRANSDATE, y.QUANTITY, y.PROJECT_CODE, y.RESOURCE_CODE, y.COMPANY_CODE, 0, y.TASK_ID FROM ppa_transcontrol y) wipunion, ppa_billings f WHERE wipunion.transno = f.applyto (+)) a, (SELECT a.currency_code, NVL(a.stdcost,0) stdcost, NVL(a.totalcost,0) totalcost, NVL(a.billrate,0) billrate, NVL(a.amount,0) amount, a.amountremaining, a.currency_type, a.transno FROM ppa_wip_values a UNION ALL SELECT b.currency_code, NVL(b.stdcost,0) stdcost, NVL(b.totalcost,0) totalcost, NVL(b.billrate,0) billrate, NVL(b.amount,0) amount, 0, b.currency_type, b.transno FROM ppa_transcontrol_values b) b, srm_companies c, srm_projects d, srm_resources e, ppa_billing_values g, odf_CA_project x 
 

WHERE x.ID = d.ID AND a.transno = b.transno AND a.company_code = c.company_id (+) AND a.project_code = UPPER( d.unique_name ) AND a.resource_code = e.unique_name AND a.f_transno = g.transno (+) AND b.currency_type = 'BILLING' AND g.currency_type (+) = 'BILLING' AND (a.f_status is null OR a.f_status = 0) AND  a.status <> 2 AND (a.f_transtype IS NULL OR a.f_transtype NOT IN ('BP','BR'))        
    
GROUP BY x.dsti_proj_ref, a.transtype, a.transdate, a.quantity, a.project_code, a.resource_code, b.currency_code, b.stdcost, b.totalcost, b.billrate, b.amount, c.company_name, d.name, e.first_name, e.last_name, b.amountremaining, a.task_id

ObtainSelectStatement:

        SelectStr = "SELECT x.dsti_proj_ref, a.transtype, a.transdate, sum(NVL(a.quantity,0)) quantity, " & 
+				"a.project_code, a.resource_code, b.currency_code, NVL(b.stdcost,0) stdcost,  " &
+				"NVL(b.totalcost,0) totalcost, NVL(b.billrate,0) billrate, NVL(b.amount,0) amount, c.company_name, " &
+				"d.name project_name, e.first_name, e.last_name, NVL(b.amountremaining, 0) amountremaining, " &
+				"SUM(NVL(g.amount,0)) amountbilled " 
        FromStr = "FROM (select wipunion.TRANSNO, wipunion.TRANSTYPE, wipunion.TRANSDATE, wipunion.QUANTITY, " & 
+				"wipunion.PROJECT_CODE, wipunion.RESOURCE_CODE, wipunion.COMPANY_CODE, wipunion.STATUS, " &
+				"wipunion.TASK_ID, f.transno f_transno, f.transtype f_transtype, f.status f_status " &  
+				"FROM(SELECT x.TRANSNO, x.TRANSTYPE, x.TRANSDATE, x.QUANTITY, x.PROJECT_CODE, x.RESOURCE_CODE, " & 
+				"x.COMPANY_CODE, x.STATUS, x.TASK_ID " &
+				"FROM ppa_wip x UNION ALL SELECT y.TRANSNO, y.TRANSTYPE, y.TRANSDATE, y.QUANTITY, y.PROJECT_CODE, " & 
+				"y.RESOURCE_CODE, y.COMPANY_CODE, 0, y.TASK_ID FROM ppa_transcontrol y) wipunion, ppa_billings f " & 
+				"WHERE wipunion.transno = f.applyto (+)) a, (SELECT a.currency_code, NVL(a.stdcost,0) stdcost, " & 
+ 				"NVL(a.totalcost,0) totalcost, NVL(a.billrate,0) billrate, NVL(a.amount,0) amount, " & 
+				"a.amountremaining, a.currency_type, a.transno FROM ppa_wip_values a UNION ALL SELECT " &
+				"b.currency_code, NVL(b.stdcost,0) stdcost, NVL(b.totalcost,0) totalcost, NVL(b.billrate,0) billrate, " & 
+				"NVL(b.amount,0) amount, 0, b.currency_type, b.transno FROM ppa_transcontrol_values b) b, " & 
+				"srm_companies c, srm_projects d, srm_resources e, ppa_billing_values g, odf_CA_project x " 
  If NewReportApp::param_project = 0 Then
            WhereStr = "WHERE x.ID = d.ID AND a.transno = b.transno AND a.company_code = c.company_id (+) AND " &
+               "a.project_code = UPPER( d.unique_name ) AND a.resource_code = e.unique_name AND " &
+               " a.f_transno = g.transno (+) AND " &
+               "b.currency_type = 'BILLING' AND g.currency_type (+) = 'BILLING' AND (a.f_status is null OR a.f_status = 0) AND " &
+				" a.status <> 2 AND " &
+				" (a.f_transtype IS NULL OR a.f_transtype NOT IN ('BP','BR')) "
        Else
            WhereStr = "WHERE x.ID = d.ID AND a.transno = b.transno AND a.company_code = c.company_id (+) AND " &
+               "a.project_code = UPPER( d.unique_name ) AND a.resource_code = e.unique_name AND " &
+               " a.f_transno = g.transno (+) AND " &
+               "b.currency_type = 'BILLING' AND g.currency_type (+) = 'BILLING' AND (a.f_status is null OR a.f_status = 0) AND " &
+				" a.status <> 2 AND " &
+				" (a.f_transtype IS NULL OR a.f_transtype NOT IN ('BP','BR')) "
        End If
    
	    GroupStr = "GROUP BY x.dsti_proj_ref, a.transtype, a.transdate, a.quantity, a.project_code, a.resource_code, " & 
+			"b.currency_code, b.stdcost, b.totalcost, b.billrate, b.amount, c.company_name, d.name, e.first_name, " &
+			"e.last_name, b.amountremaining, a.task_id"

does anyone have any ideas?

 

[t16turbo]

I have identified the problem with this.

It all hinges around the table odf_ca_project
alias of x.

in the where clause I am joining this to anohter table on x.id = d.id

if I remove this join, the table and the field in the SELECT and GROUP BY, I do not get this problem.
however, x.id and d.id ARE compatible, as I use them in almost every other report I run.


why would Actuate see them as not compatible?

 

[gdwood]

check your datarow and make sure they are both defined as the same type or a compatible type.