Home » Archived » BIRT » Appending to SQL Where statement
| Appending to SQL Where statement [message #182098] |
Wed, 02 August 2006 17:42  |
Eclipse User |
|
|
|
Originally posted by: svenn.cayenta.com
Is it possible to add to the where clause based on a condition without
creating the whole SQL in a JavaScript method?
That is I have a query in my Data Set object
Select column1, column2....column25 from table where column1 = 'A'
I'm passing a parameter to the report called FLAG.
What I want to do is create a conditional statement such as
If FLAG = "S" Then
SQL = SQL + " column16 = 'SUBMIT'"
Else If FLAG = "P" Then
SQL = SQL + " column16 = 'PENDING'"
Else If FLAG = "A" Then
SQL = SQL + " column25 = 'ACCEPT'"
Else If FLAG = "R" Then
SQL = SQL + " column25 = 'REJECT'"
|
|
| |
| Re: Appending to SQL Where statement [message #183500 is a reply to message #182098] |
Tue, 08 August 2006 15:20  |
| Eclipse User |
|
|
|
There is a very easy way to do this. You just build your WHERE clause in
Java and then append it entirely as a single parameter. This works great
and I will append a complete example that does it:
---------------------------------------------------------
XXX.rptdesign file (excerpt):
Three things to notice in here
1.) Notice how the WHERE_CLAUSE is passed in as a parameter. This
will need to be set in your Java class before you execute the report file.
2.) Notice before the SQL statement I have a line:
<method name="beforeOpen"><![CDATA[this.queryText = this.queryText
+ " " + params["WHERE_CLAUSE"];]]></method>
This line takes the existing query (via its property named
'queryText') and appends the where clause to it.
3.) Notice that I have built my query to accept that the where
clause
will be appended.
<?xml version="1.0" encoding="UTF-8"?>
<!-- Written by Eclipse BIRT 2.0 -->
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3" id="1">
<property name="createdBy">Eclipse BIRT Designer Version 1.0.1 Build
<20051130-1157></property>
<property name="units">in</property>
<text-property name="displayName">Diagnosis Frequency</text-property>
<parameters>
<scalar-parameter name="WHERE_CLAUSE" id="7">
<property name="valueType">static</property>
<property name="dataType">string</property>
<property name="allowNull">true</property>
<property name="defaultValue">1 = 0</property>
<property name="controlType">text-box</property>
<property name="format">Unformatted</property>
</scalar-parameter>
</parameters>
<data-sources>
<oda-data-source
extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source"
id="44">
<property
name="odaDriverClass">com.microsoft.sqlserver.jdbc.SQLServerDriver </property>
<property
name="odaURL">jdbc:sqlserver://localhost:1433;databaseName=FREYA_DB</property>
<property name="odaUser">FREYA</property>
<encrypted-property
name="odaPassword">bWlkZ2FyZDE=</encrypted-property>
</oda-data-source>
</data-sources>
<data-sets>
<oda-data-set
extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet "
name="Diagnosis Code Data Set" id="1200">
<property name="dataSource">Data Source</property>
<method name="beforeOpen"><![CDATA[this.queryText =
this.queryText + " " + params["WHERE_CLAUSE"];]]></method>
<property name="queryText">
SELECT * FROM CLAIM WHERE
</property>
</oda-data-set>
</data-sets>
[rest of the file omitted because it has nothing that pertains to this
point.]
---------------------------------------------------------
Java class that executes the report
protected void renderComponent(IMarkupWriter pWriter, IRequestCycle
pCycle)
{
pWriter.begin("p");
getHTMLReport(pWriter, pCycle.getListenerParameters());
pWriter.end("p");
}
private void getHTMLReport(IMarkupWriter pWriter, Object[] pParameters)
{
ReportEngine engine = ReportEngineFactory.getEngine();
// Run reports, etc.
try
{
HashMap<String, Object> parameters = new HashMap<String, Object>();
setOutgoingParameters( parameters, pParameters );
IReportRunnable design = getReportSpecification( engine,
(String)pParameters[ 0 ] );
// Create task to run the report and render the report
IRunAndRenderTask task = engine.createRunAndRenderTask(design);
// Set Render context to handle url and image locataions
HTMLRenderContext renderContext = new HTMLRenderContext();
// renderContext.setImageDirectory("image");
HashMap<String, Object> contextMap = new HashMap<String, Object>();
contextMap.put(EngineConstants.APPCONTEXT_HTML_RENDER_CONTEX T,
renderContext);
task.setAppContext(contextMap);
task.setParameterValues(parameters);
populateDatabaseConnectionParameters( design );
// create output stream
ByteArrayOutputStream out = new ByteArrayOutputStream(2048);
// Set rendering options - such as file or stream output,
// output format, whether it is embeddable, etc
HTMLRenderOption options = new HTMLRenderOption();
options.setOutputStream(out);
options.setOutputFormat("html");
task.setRenderOption(options);
// UTF-8
task.run();
pWriter.printRaw( out.toString( "UTF-8" ) );
//printReport(pWriter, engine, report);
}
catch (Exception e1)
{
e1.printStackTrace();
}
// Shut down the engine.
if (engine != null)
{
// engine.destroy();
}
}
private IReportRunnable getReportSpecification( ReportEngine pEngine,
String pFileName ) throws Exception
{
InputStream stream =
PeReportRenderer.class.getClassLoader().getResourceAsStream( pFileName );
IReportRunnable design = pEngine.openReportDesign( stream );
return design;
}
---------------------------------------------------------
Code that builds the WHERE clause and sets it as a parameter
/** This is for the Worksheet_Report.rptdesign */
public void populateParameters1( HashMap<String, Object> pOut, Object[]
pIn )
{
StringBuffer where = new StringBuffer();
where.append( "\nCLAIM1.DISCRIMINATOR = 'PRO' " );
where.append( "\nAND CLAIM1.CLAIM_STATUS = '" + Identifiable.ACTIVE +
"' " );
where.append( "\nAND CLAIM1.ANALYSIS_STATUS <> 'NN' " );
where.append( "\nAND CLAIM1.ENTERPRISE_IID = " +
this.getUser().getDefaultEnterpriseID() );
where.append( populateStartAndEndClaimIID( pOut, pIn[ 1 ],
pIn[ 2 ] ) );
/** The following logic is necessary in case we arrived here via the
Claim Edit Screen, which only
* supplies parameters for parms 0, 1, 2 */
if( pIn.length > 3 )
{
where.append( populateStartAndEndSystemEntryDate( pOut, pIn[ 3
], pIn[ 4 ] ) );
where.append( populateStartAndEndServiceStartDate( pOut, pIn[ 5
], pIn[ 6 ] ) );
where.append( populateStartAndEndBatchEID( pOut, pIn[ 7
], pIn[ 8 ] ) );
where.append( populateStartAndEndPractitionerEID( pOut, pIn[ 9
], pIn[ 10 ] ) );
where.append( populateStartAndEndMedicalRecordNumber( pOut, pIn[ 11
], pIn[ 12 ] ) );
where.append( populateFlags( pOut, pIn[ 17
], pIn[ 18 ], pIn[ 19 ], pIn[ 20 ] ) );
where.append( populateEnvironment( pOut, pIn[ 21
], pIn[ 22 ] ) );
where.append( populateAnalysisType( pOut, pIn[ 26
] ) );
where.append( populateAccountsList( pOut, pIn ) );
where.append( populatePlansList( pOut, pIn ) );
where.append( populateFlagsList( pOut, pIn ) );
where.append( populateEnterpriseList( pOut, pIn) );
}
pOut.put( "WHERE_CLAUSE", where.toString() );
}
|
|
|
| Re: Appending to SQL Where statement [message #184924 is a reply to message #183500] |
Tue, 15 August 2006 03:42 |
| Eclipse User |
|
|
|
Originally posted by: harunhasdal.gmail.com
Tyrone Hed wrote:
> There is a very easy way to do this. You just build your WHERE clause in
> Java and then append it entirely as a single parameter. This works great
> and I will append a complete example that does it:
>
> ---------------------------------------------------------
> XXX.rptdesign file (excerpt):
> Three things to notice in here
>
> 1.) Notice how the WHERE_CLAUSE is passed in as a parameter. This
> will need to be set in your Java class before you execute the report file.
>
> 2.) Notice before the SQL statement I have a line:
>
> <method name="beforeOpen"><![CDATA[this.queryText = this.queryText
> + " " + params["WHERE_CLAUSE"];]]></method>
> This line takes the existing query (via its property named
> 'queryText') and appends the where clause to it.
>
> 3.) Notice that I have built my query to accept that the where
> clause
> will be appended.
>
> <?xml version="1.0" encoding="UTF-8"?>
> <!-- Written by Eclipse BIRT 2.0 -->
> <report xmlns="http://www.eclipse.org/birt/2005/design" version="3" id="1">
> <property name="createdBy">Eclipse BIRT Designer Version 1.0.1 Build
> <20051130-1157></property>
> <property name="units">in</property>
> <text-property name="displayName">Diagnosis Frequency</text-property>
>
> <parameters>
> <scalar-parameter name="WHERE_CLAUSE" id="7">
> <property name="valueType">static</property>
> <property name="dataType">string</property>
> <property name="allowNull">true</property>
> <property name="defaultValue">1 = 0</property>
> <property name="controlType">text-box</property>
> <property name="format">Unformatted</property>
> </scalar-parameter>
> </parameters> <data-sources>
> <oda-data-source
> extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source"
> id="44">
> <property
> name="odaDriverClass">com.microsoft.sqlserver.jdbc.SQLServerDriver </property>
>
> <property
> name="odaURL">jdbc:sqlserver://localhost:1433;databaseName=FREYA_DB</property>
>
> <property name="odaUser">FREYA</property>
> <encrypted-property
> name="odaPassword">bWlkZ2FyZDE=</encrypted-property>
> </oda-data-source>
> </data-sources>
> <data-sets>
> <oda-data-set
> extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet "
> name="Diagnosis Code Data Set" id="1200">
> <property name="dataSource">Data Source</property>
> <method name="beforeOpen"><![CDATA[this.queryText =
> this.queryText + " " + params["WHERE_CLAUSE"];]]></method>
> <property name="queryText">
> SELECT * FROM CLAIM WHERE </property>
> </oda-data-set>
> </data-sets>
>
> [rest of the file omitted because it has nothing that pertains to this
> point.]
>
> ---------------------------------------------------------
>
> Java class that executes the report
>
> protected void renderComponent(IMarkupWriter pWriter, IRequestCycle
> pCycle)
> { pWriter.begin("p");
> getHTMLReport(pWriter, pCycle.getListenerParameters());
> pWriter.end("p");
> }
>
> private void getHTMLReport(IMarkupWriter pWriter, Object[] pParameters)
> {
> ReportEngine engine = ReportEngineFactory.getEngine();
> // Run reports, etc.
> try
> {
> HashMap<String, Object> parameters = new HashMap<String, Object>();
> setOutgoingParameters( parameters, pParameters );
> IReportRunnable design = getReportSpecification( engine,
> (String)pParameters[ 0 ] );
> // Create task to run the report and render the
> report
> IRunAndRenderTask task = engine.createRunAndRenderTask(design);
> // Set Render context to handle url and image locataions
> HTMLRenderContext renderContext = new HTMLRenderContext();
> // renderContext.setImageDirectory("image");
> HashMap<String, Object> contextMap = new HashMap<String, Object>();
> contextMap.put(EngineConstants.APPCONTEXT_HTML_RENDER_CONTEX T,
> renderContext); task.setAppContext(contextMap);
> task.setParameterValues(parameters);
> populateDatabaseConnectionParameters( design );
> // create output stream
> ByteArrayOutputStream out = new ByteArrayOutputStream(2048);
> // Set rendering options - such as file or stream output,
> // output format, whether it is embeddable, etc
> HTMLRenderOption options = new HTMLRenderOption();
> options.setOutputStream(out);
> options.setOutputFormat("html");
> task.setRenderOption(options);
>
> // UTF-8
> task.run();
> pWriter.printRaw( out.toString( "UTF-8" ) );
> //printReport(pWriter, engine, report);
> }
> catch (Exception e1)
> {
> e1.printStackTrace();
> }
> // Shut down the engine.
>
> if (engine != null)
> {
> // engine.destroy();
> }
> }
>
> private IReportRunnable getReportSpecification( ReportEngine pEngine,
> String pFileName ) throws Exception
> {
> InputStream stream =
> PeReportRenderer.class.getClassLoader().getResourceAsStream( pFileName );
> IReportRunnable design = pEngine.openReportDesign( stream );
> return design;
> }
>
>
> ---------------------------------------------------------
> Code that builds the WHERE clause and sets it as a parameter
>
> /** This is for the Worksheet_Report.rptdesign */
> public void populateParameters1( HashMap<String, Object> pOut, Object[]
> pIn )
> {
> StringBuffer where = new StringBuffer();
> where.append( "\nCLAIM1.DISCRIMINATOR = 'PRO' " );
> where.append( "\nAND CLAIM1.CLAIM_STATUS = '" + Identifiable.ACTIVE
> + "' " );
> where.append( "\nAND CLAIM1.ANALYSIS_STATUS <> 'NN' " );
> where.append( "\nAND CLAIM1.ENTERPRISE_IID = " +
> this.getUser().getDefaultEnterpriseID() );
> where.append( populateStartAndEndClaimIID( pOut, pIn[ 1 ],
> pIn[ 2 ] ) );
> /** The following logic is necessary in case we arrived here
> via the Claim Edit Screen, which only
> * supplies parameters for parms 0, 1, 2 */
> if( pIn.length > 3 )
> {
> where.append( populateStartAndEndSystemEntryDate( pOut, pIn[ 3
> ], pIn[ 4 ] ) ); where.append(
> populateStartAndEndServiceStartDate( pOut, pIn[ 5 ], pIn[ 6 ] ) );
> where.append( populateStartAndEndBatchEID( pOut, pIn[ 7
> ], pIn[ 8 ] ) ); where.append(
> populateStartAndEndPractitionerEID( pOut, pIn[ 9 ], pIn[ 10 ] ) );
> where.append( populateStartAndEndMedicalRecordNumber( pOut, pIn[ 11
> ], pIn[ 12 ] ) );
> where.append( populateFlags( pOut,
> pIn[ 17 ], pIn[ 18 ], pIn[ 19 ], pIn[ 20 ] ) );
> where.append( populateEnvironment( pOut, pIn[ 21
> ], pIn[ 22 ] ) ); where.append(
> populateAnalysisType( pOut, pIn[ 26 ] ) );
> where.append( populateAccountsList( pOut, pIn
> ) );
> where.append( populatePlansList( pOut, pIn ) );
> where.append( populateFlagsList( pOut, pIn ) );
> where.append( populateEnterpriseList( pOut, pIn) );
> }
> pOut.put( "WHERE_CLAUSE", where.toString() ); }
>
>
>
>
Hi,
You can also use conditionals in beforeOpen method. If there is not
another need for java code. You can build the whole query in the
beforeOpen script.
Harun.
|
|
|
Goto Forum:
Current Time: Thu Nov 06 13:51:57 EST 2025
Powered by FUDForum. Page generated in 0.04606 seconds |
Home