|
View previous topic :: View next topic
|
| Author |
Message |
stiffknot
New User
Joined: 15 Jul 2009
Posts: 2
Location: chennai
|
|
|
|
Use of "OR" condition in "Where' clause of a DB2 Cursor? Can we?
My requirement is this:
I have data such as EMP ID,NAME,COMPANY all of which reside in the same table DATA - with EMP ID being the key.
Now,my input could be any one of the fields - EMP ID or NAME or COMPANY.(But one one input field would be filled when the call is made)
I will have to read DATA and display all records which match the input.
EMP ID as input - would return one record.
NAME as input - more than one.
COMPANY as input - several records.
Now,i need to populate the screen with a Select cursor of DATA.
Can my WHERE clause in the Declare include OR condition like:
WHERE
EMP ID = :W-emp OR
NAME = :W-name OR
COMPANY = :W-company
Order by blah ;
The trouble is I am creating a SEGLOOP in TELON and Need to know if this would work - Or I might need to change the design.
If this doesn't work,an alternate suggestion would be welcome. |
|
| Back to top |
|
 |
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
You can.. but what should happen when multiple inputs are received?
If only one will be received, why not create three separate queries based on the type of input received?
Or you could make the query generic to handle any combination of three inputs.
| Code: |
MOVE LOW-VALUES TO WS-LOW-EMP WS-LOW-NAME WS-LOW-CMP
MOVE HIGH-VALUES TO WS-HI-EMP WS-HI-NAME WS-HI-CMP
IF W-emp > SPACES -- INPUT RECEIVED
MOVE W-EMP TO WS-LOW-EMP WS-HI-EMP
END-IF
IF W-name > SPACES
MOVE W-name TO WS-LOW-NAME WS-HI-NAME
END-IF
IF W-company > SPACES
MOVE W-company TO WS-LOW-CMP WS-HI-CMP
END-IF
QUERY
WHERE
EMP_ID BETWEEN :WS-LOW-EMP AND :WS-HI-EMP AND
NAME BETWEEN :WS-LOW-NAME AND :WS-HI-NAME AND
COMPANY BETWEEN WS-LOW-CMP AND :WS-HI-CMP
Order by blah ;
|
|
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| agkshirsagar wrote: |
| You can.. but what should happen when multiple inputs are received? |
| stiffknot" wrote: |
| Now,my input could be any one of the fields - EMP ID or NAME or COMPANY.(But one one input field would be filled when the call is made) |
IOW, that's an error to checked for before opening the cursor, and handled outside of DB2  |
|
| Back to top |
|
|
stiffknot
New User
Joined: 15 Jul 2009
Posts: 2
Location: chennai
|
|
|
|
| agkshirsagar wrote: |
You can.. but what should happen when multiple inputs are received?
If only one will be received, why not create three separate queries based on the type of input received?
|
I have made the handling outside DB2 as mentioned if multiple inputs are received.(in the code)
Creating 3 separate cursors make it look more daunting that the actual simplicity of the task
:wink:
Do we need to move low-values & high-values and check using BETWEEN in the WHERE clause - this apparently means we use "AND" condition.
Can't we actually use
| Code: |
WHERE
EMP-ID = :WS_EMP
OR
NAME = :WS_NAME
OR
COMPANY = :WS_COMPANY
ORDER BY BLAH BLAH; |
Curious to know what would happen if we use the OR condition in the WHERE clause,since at one point of time either WS_EMP/WS_NAME/WS_COMPANY will have a value - And the other 2 will have been initialized before encountering the cursor.
Eager to know :) [/code] |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| stiffknot wrote: |
Can't we actually use
| Code: |
WHERE
EMP-ID = :WS_EMP
OR
NAME = :WS_NAME
OR
COMPANY = :WS_COMPANY
ORDER BY BLAH BLAH; |
Curious to know what would happen if we use the OR condition in the WHERE clause,since at one point of time either WS_EMP/WS_NAME/WS_COMPANY will have a value - And the other 2 will have been initialized before encountering the cursor. |
Based on a limited amount of testing, that should work well. |
|
| Back to top |
|
|
|
|
