Urgent : How to run a query multiple time

[2005 IR Help]

By: akmalkhan - akmalkhan

Urgent : How to run a query multiple time

2005-11-29 00:56

Hi,

 

I am new to jasper report. I need your help to solve a problem. Pls help me , it is urgent. I have two tables called EMPLOYEE and DEPENDANT.

 

User would select list of employee and somes dates.

I need to display DEPENDANT information for selected employees.

 

For example , if user select 500 employee from list, I need to display DEPENDANT information of all 500 Employee,the output would be as follow :-

 

EMP NO : 001 EMP NAME : DAVID

 

DEPENDANT NAME : RAHUL

RELATIONSHIP TYPE: SON

SEX : M

.

.

.

 

EMP NO : 500 EMP NAME : PETER

 

DEPENDANT NAME : LINDA

RELATIONSHIP TYPE: DAUGHTER

SEX : F

 

My doubt is as follow :-

 

*) How to form a query for Employee table. Since number of employee is based on dynamic selection.

 

*) How to retrieve dependant info for employee which is selected .

 

*) or if you have any good solution for this problem pls let me know soon. It is so urgent. Awaiting for your reply.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

By: hillary - wikkitt

RE: Urgent : How to run a query multiple time

2005-11-29 10:57

You will need to create your sql query dynamically and either send the whole query as a $P{} or just the where caluse.

First, do a join on the two tables. Assuming you use employee no. as a primary and foriegn key.

 

-following your example-

select e.emp_no, e.emp_name, d.dep_name, d.relationship, d.sex from employee e, dependant d where e.emp_no = d.emp_no

 

now to select a list of chosen employees you will probably need to use the sql IN operator. This you can pass as a $P{}.

 

where e.emp_no IN (001, 501,....)

 

this will pull all records matching the list.

 

look at http://www.firstsql.com/tutor2.htm for mor e sql tips.

 

 

 

 

By: akmalkhan - akmalkhan

RE: Urgent : How to run a query multiple time

2005-11-29 18:43

Hi,

Thank you wikkitt. if user selects more than 2000 employee, can we include 2000 EMP_NO in query.Last time When I execute a query which had 2500 Charectors , i got exception from JDBC.

In my case the length of query is changed based on employee selection. if query size is bigger, will we get any exception ? How many items can be included with in the bracket of IN. Pls suggest me.

 

 

 

 

 

 

 

 

 

 

 

 

By: hillary - wikkitt

RE: Urgent : How to run a query multiple time

2005-11-30 14:54

I wasn't aware that there was a limit to the size of a sql stmt.

 

Perhaps there is a way to put the selection list in a sql bind variable and putting that in your sql stmt. That would cut down on the character count. But, to be honest, I'm not familiar with using sql bind.

 

I would find someone who knows more about the database you are using and ask them.

 

Sorry