ERROR: transaction is read-only jaspersoft aws bi suite

[timy2]

Hello im currently running this query (see below) in devrived tables when creating a domain in jaspersofts bi aws suite. However when i do i get the following error "Cannot execute JDBC Query.

org.postgresql.util.PSQLException: ERROR: transaction is read-only ." Can anyone explain why and point me in the right direction.

 

Cheers

 

SELECT company.companyStatus,

       company.CompanyId,

       company.companyName,

       

count(distinct users.userid) as TotalUsers,

sum(case when UserStatusID = 2 then 1 else 0 end) as ActiveUsers,

sum( case when UserStatusID= 3 then 1 else 0 end) as SuspendedUsers,

 

  (Select COUNT (distinct usersessionid)

  From UserSession 

inner join users  on usersession.UserID=users.UserID

where usersession.UserID=users.UserID  

  and users.companyid= company.CompanyID) as TotalLogin,

 

 

  (Select 

  COUNT( Name)

  From Template 

  inner join merchendisingmodule  on Template.TemplateID= merchendisingmodule.TemplateId

  inner join Project  on Template.ProjectID= Project.ProjectID

  where Template.TemplateID=merchendisingmodule.TemplateId 

  and Project.companyid= company.CompanyID)as CurrentModules,

 

 

  (Select 

  count(merchendisingmodule.CreatedDate)

  from merchendisingmodule 

  inner join Template   on Template.TemplateID= merchendisingmodule.TemplateId

  inner join Project  on Template.ProjectID= Project.ProjectID

  where Template.TemplateID=merchendisingmodule.TemplateId 

  and Project.companyid= company.CompanyID)as ModulesCreated,

 

 

 (Select 

count(merchendisingmodule.UpdatedDate)

  from merchendisingmodule 

  inner join Template  on Template.TemplateID= merchendisingmodule.TemplateId

  inner join Project  on Template.ProjectID= Project.ProjectID

  where Template.TemplateID=merchendisingmodule.TemplateId 

  and Project.companyid= company.CompanyID)as ModulesUpdated,

 

 

  (Select 

  COUNT(merchendisingarea.merchendisingareaid)

  from merchendisingarea 

  inner join Project  on Project.ProjectID= merchendisingarea.ProjectID

  where Project.ProjectID=merchendisingarea.ProjectID 

  and Project.companyid= company.CompanyID) as Currentareas,

 

 

  (Select 

  COUNT (merchendisingarea.name)

  from merchendisingarea 

  inner join Project  on Project.ProjectID= merchendisingarea.ProjectID

  where Project.ProjectID=merchendisingarea.ProjectID 

  and Project.companyid= company.CompanyID) as AreasCreated,

 

 

  (select 

  COUNT (merchendisingarea.UpdatedDate)

  from merchendisingarea 

  inner join Project  on Project.ProjectID = merchendisingarea.ProjectID

  where Project.ProjectID=merchendisingarea.ProjectID 

  and Project.companyid= company.CompanyID) as AreasUpdated,

 

 

  (Select 

  SUM ( case when merchendisingarea.PublishStatus = 1 then 1 else 0 end)

  from merchendisingarea 

  inner join PublishingStatus  on PublishingStatus.PublishStatusId = merchendisingarea.PublishStatus

  inner join Project  on merchendisingarea.ProjectID = Project.ProjectID

  where PublishingStatus.PublishStatusId=merchendisingarea.PublishStatus 

  and Project.companyid= company.CompanyID) as SuccessPublished,

 

 

  (Select

  SUM ( case when merchendisingarea.PublishStatus = 3 then 1 else 0 end) 

  from merchendisingarea 

  inner join PublishingStatus  on PublishingStatus.PublishStatusId= merchendisingarea.PublishStatus

  inner join Project  on merchendisingarea.ProjectID = Project.ProjectID

  where PublishingStatus.PublishStatusId=merchendisingarea.PublishStatus 

  and Project.companyid= company.CompanyID) as FailedPublished

 

  from  Company  

 

inner join users on users.companyid=company.companyid

 

group by company.companyStatus, company.companyid, company.companyname

 

 

[mmflynn]

Timy2 - I'm not a SQL or PostgreSQL expert, but I can offer you an idea that may be able to help you.

Jaspersoft runs database queries, which are naturally read-only transactions. Your complex SQL statement includes sub-queries. A quick web search yielded this, from http://www.postgresql.org/docs/9.1/static/sql-select.html: "Just as in a table, every output column of a SELECT has a name. In a simple SELECT this name is just used to label the column for display, but when the SELECT is a sub-query of a larger query, the name is seen by the larger query as the column name of the virtual table produced by the sub-query". It appears that the SQL is invoking a "write" transaction.

The cleanest way to handle this is to use an ETL process to transform and load the data into a table so it can be easily queried. It's possible you could make a stored procedure that sets the transaction type, but that may not be allowable and I wouldn't recommend it strictly from a security point of view.

I hope this helps.

Mary Flynn

Jaspersoft