IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 query to fetch records from tables based on inputs.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
dilip_bangalore

New User


Joined: 29 Sep 2011
Posts: 27
Location: India

PostPosted: Tue Nov 12, 2013 10:03 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Nov 13, 2013 4:01 am
Reply with quote

Hello,

Suggest you consider creating dynamic sql or a combination of equals and hi-lo ranges for the non-specified fields.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Wed Nov 13, 2013 4:27 pm
Reply with quote

I would sugggest better validate the data first and then perform different sections which has different SQLs
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Nov 13, 2013 7:27 pm
Reply with quote

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 . . . icon_redface.gif
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Wed Nov 13, 2013 7:32 pm
Reply with quote

Dick,

Agree there would be too many sections but the query will be straightforward
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Nov 13, 2013 7:47 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Nov 13, 2013 8:05 pm
Reply with quote

Thanks Guy!

That was the method i was trying to recall . . .

d
Back to top
View user's profile Send private message
dilip_bangalore

New User


Joined: 29 Sep 2011
Posts: 27
Location: India

PostPosted: Wed Nov 13, 2013 9:24 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Fetch data from programs execute (dat... DB2 3
Search our Forums:

Back to Top