View previous topic :: View next topic
|
Author |
Message |
dilip_bangalore
New User
Joined: 29 Sep 2011 Posts: 27 Location: India
|
|
|
|
Hi all,
Can you please help me with the query to retrieve records from db2 tables when the fields in the where clause is not constant(
one field is entered, 2 fields are entered, 3 fields are entered, spaces are passed for the fields which are not entered)
For example :
Employee no, name, dept, salary fields in front end(java), these fields are used in MF DB2 query to retrieve the list of employees.
None of the four fields are mandatory. Only employee no field is entered, 2 fields are entered, 3 fields are entered. employee no is unique. whenever input values are entered with employee no. records are fetched based only on employee no. It should work even if . so on.
say I have entered only dept. and I get 1000 records, to refine the search I enter name along with dept. for this how do I write a query to fetch records for matching dept and name. the value passed for salary is spaces.
Initial query to retrieve records when only 1 field is entered:
Select * from EMP_TABLE
where empno = 123 or dept = 'ÍT' or name = 'empname' or sal = 10000;
When 2 fields are entered, can I make the combination like
where empno = 123 or dept = 'ÍT' or name = 'empname' or sal = 10000 or (empno = 123 and dept = 'ÍT');
The above query also retrieves same records as first.
please help with the query to consider all the fields with combination even if the value passed is spaces.
Thanks in advance,
Dilip |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you consider creating dynamic sql or a combination of equals and hi-lo ranges for the non-specified fields. |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
I would sugggest better validate the data first and then perform different sections which has different SQLs |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
I believe this would be too many "sections" for all of the combinations possible.
Recently (somewhere) someone posted a rather simple, straight-forward sample of doing exactly this. I just don't recall where it was posted . . . |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Dick,
Agree there would be too many sections but the query will be straightforward |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
at least two SQLstmt should be coded
Code: |
IF WS-EMPNO > 0
exec sql
Select * from EMP_TABLE
where empno = :WS-EMPNO
end-exec
else
if WS-DEPT = spaces
move low-values to WS-DEPT-LOW
move high-values to WS-DEPT-HIGH
else
move WS-DEPT to WS-DEPT-LOW
move WS-DEPT to WS-DEPT-HIGH
end-if
if WS-EMPNAME= spaces
move low-values to WS-EMPNAME-LOW
move high-values to WS-EMPNAME-HIGH
else
move WS-EMPNAME to WS-EMPNAME-LOW
move WS-EMPNAME to WS-EMPNAME-HIGH
end-if
if WS-SAL = spaces
move 0 WS-SAL -LOW
move 99999999 to WS-SAL -HIGH
else
move WS-SAL to WS-SAL -LOW
move WS-SAL to WS-SAL -HIGH
end-if
EXEC-SQL
Select * from EMP_TABLE
where dept between :WS-DEPT-LOW and WS-DEPT-HIGH
and name between :WS_EMPNAME-LOW and WS-EMPNAME-HIGH
and SAL between WS-SAL-LOW and WS-SAL-LOW
END-EXEC
end-if |
if you want a really bad performing SQL you can write something like this :
Code: |
EXEC SQL
Select * from EMP_TABLE
where (:WS-EMPNO = 0 or EMPNO= :WS-EMPNO)
and (:WS-DEPT = spaces or DEPT = :WS-DEPT)
and (:WS-EMPNAME= spaces or EMPNAME= :WS-EMPNAME)
and (:WS-SAL= 0 or SAL= :WS-SAL)
END-EXEC |
|
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Thanks Guy!
That was the method i was trying to recall . . .
d |
|
Back to top |
|
|
dilip_bangalore
New User
Joined: 29 Sep 2011 Posts: 27 Location: India
|
|
|
|
Thank you all for you suggestions.
Just to get things working, even though its a bad programming, I tried and got it working. its almost the similar to the second method as suggested by GuyC.
Check the values in each field using if conditions and set flags, say 'N' if the value is spaces and move spaces. If value is entered then set the flag to 'Y' and move actual value.
So the query looks something like below :
SELECT * FROM EMPTABLE
WHERE (:WS-EMPNO-FLAG = 'N' OR EMP_NO = :WS-EMPNO)
AND (:WS-DEPT-FLAG = 'N' OR DEPT = :WS-DEPT)
AND (:WS-EMPNAME-FLAG = 'N' OR EMPNAME = :WS-EMPNAME)
AND (:WS-SAL-FLAG = 'N' OR SAL = :WS-SAL);
Thanks GuyC for your first method, will try this.
Regards,
Dilip Kumar
where (:ws-emp-flag = 'N' or empno = 123 |
|
Back to top |
|
|
|