Generating SQL

[PeteWalburn]

Hi,

I need to create a report that takes a parameter specifying which type of report it is.

It is a TRANSACTION report, but there are different types of these reports (Issue, Receipt, Returns, etc). Each Transaction type will have a different SQL statement albeit returning columns of the same names.

Is it possible to build up the SQL statments in code depending on the passed in parameter?

I want something like:

Select Case Type
Case "ISS"
SelectClause = "LINECOST AS TRANSVAL,REFWO AS TRANSWO"
FromClause = Schema & ".MATUSETRANS"
WhereClause = "ORGID='" & mroOrg & "' AND "
WhereClause = WhereClause & SITEID='" & mroSite & "'"
OrderByClause = "TRANSWO"
Case "REC"
SelectClause = "ACTUALCOST AS TRANSVAL,'' AS TRANSWO"
FromClause = Schema & ".MATRECTRANS"
WhereClause = "ORGID='" & mroOrg & "' AND "
WhereClause = WhereClause & "SITEID='" & mroSite & "'"
OrderClause = "TRANSVAL"
End Select

There is a lot more to the SQL than this, but this is the general idea.

Is it possible? How?

Pete

 

[gdwood]

Yes, you can build SQL and run a conditional around the parameter. I find Case Statements with this a bit "touchy" so prefer the IF.... Would be this idea:

IF <parameter condition> Then
SQL build
ELSEIF <parameter condition> THEN
SQL build
....
END IF

You can try the CAse if you like as well

Not sure if you needed help with the SQL as well, but you can build that many ways, we do it in a complex way so that uses a string to create a big SQL and then does a function to pass and open it.

Hope this helps

 

[PeteWalburn]

Thanks for the reply.

I do have code to build the SQL statements. I use a Case statement to do it. The real problem I have is knowing which function to override and put the code in.

I have been doing it in the ObtainSelectStatement of my SQLquerySource. I have set the SelectClause, FromClause, WhereClause and OrderByClause.

When I run the report, I get the error message:

Basic Error: 1011
Module: C:\Program Files\Actuate7\ErdPro\afc\db.bas
Line: 182

Database Error
16: The parameter identified is not known to this statement.

So I guess that I am doing something wrong.


Which event/function should I be building the SQL statements in?

Thanks,
Pete

 

[gdwood]

I'm sure that it can be done in many places in Actuate, but we have a very rigid set of standards that are followed because we have so many reports and it makes it easier knowing that the SQL code is in the same place in every report.

We put SQL in the START method of the DataStream Object for every report. The start includes a bit of library logic and then it build the sql for the reports. We've found this as the best way for us.

Hope that helps
Grant

 

[PeteWalburn]

Grant,

Thanks for your help. I've managed to get it working now. I overrid the START and BINDATAROW methods of the DataStream.

Pete.

 

[gdwood]

great, good to hear. That's how we do ours and it works well

 

[shahdhruv]

Hi guys, sorry to but in, but why do we need to use binddatarow.

Thanks,
Dhruv

 

[gdwood]

You need to binddatarow when using a cursor in order to bind the rows coming in from the tables to the cursor in order to use them. Without binding the datarows your cursor will not be able to function properly