Perform a SQL Query according to the Dynamic Condition

Problem description & analysis

There is an EMPLOYEE table in the database. The data is as follows:

EID

NAME

SURNAME

GENDER

STATE

BIRTHDAY

HIREDATE

DEPT

1

Rebecca

Moore

F

California

1974-11-20

2005-03-11

R&D

2

Ashley

Wilson

F

New York

1980-07-19

2008-03-16

Finance

3

Rachel

Johnson

F

New Mexico

1970-12-17

2010-12-01

Sales

4

Emily

Smith

F

Texas

1985-03-07

2006-08-15

HR

5

Ashley

Smith

F

Texas

1975-05-13

2004-07-30

R&D

6

Matthew

Johnson

M

California

1984-07-07

2005-07-07

Sales

7

Alexis

Smith

F

Illinois

1972-08-16

2002-08-16

Sales

8

Megan

Wilson

F

California

1979-04-19

1984-04-19

Marketing

9

Victoria

Davis

F

Texas

1983-12-07

2009-12-07

HR

10

Ryan

Johnson

M

Pennsylvania

1976-03-12

2006-03-12

R&D

We are trying to find records where EID is less than 5 according to the dynamic condition (dynamically composed SQL). Below is the desired result:

EID

NAME

SURNAME

GENDER

STATE

BIRTHDAY

HIREDATE

DEPT

1

Rebecca

Moore

F

California

1974-11-20

2005-03-11

R&D

2

Ashley

Wilson

F

New   York

1980-07-19

2008-03-16

Finance

3

Rachel

Johnson

F

New   Mexico

1970-12-17

2010-12-01

Sales

4

Emily

Smith

F

Texas

1985-03-07

2006-08-15

HR

Solution

We write the following script p1.dfx in esProc:

A

1

=connect("demo")

2

=A1.query@x("SELECT * FROM EMPLOYEE WHERE  "+where)

Explanation:

Set a script parameter where, whose value is EID<5.

A1   Connect to the database named demo.

A2  Return query result as a table sequence and auto-close database connection when the query is finished.

Refer to How to Call an SPL Script in BIRT to learn about the method of integrating the SPL script into BIRT.

Q & A Collection

https://www.eclipse.org/forums/index.php/t/1074988/