Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 query to fetch records from tables based on inputs.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: DB2 query to fetch records from tables based on inputs.
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

Site Director


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

PostPosted: Wed Nov 13, 2013 4:01 am    Post subject:
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

Moderator


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

PostPosted: Wed Nov 13, 2013 4:27 pm    Post subject:
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

Site Director


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

PostPosted: Wed Nov 13, 2013 7:27 pm    Post subject:
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

Moderator


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

PostPosted: Wed Nov 13, 2013 7:32 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Wed Nov 13, 2013 7:47 pm    Post subject:
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

Site Director


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

PostPosted: Wed Nov 13, 2013 8:05 pm    Post subject:
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    Post subject: Reply to: DB2 query to fetch records from tables based on in
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us