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

Use of "OR" condition in "Where' clause of a


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

New User


Joined: 15 Jul 2009
Posts: 2
Location: chennai

PostPosted: Wed Sep 11, 2013 7:26 pm
Reply with quote

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

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Wed Sep 11, 2013 7:44 pm
Reply with quote

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

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Wed Sep 11, 2013 7:57 pm
Reply with quote

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

New User


Joined: 15 Jul 2009
Posts: 2
Location: chennai

PostPosted: Wed Sep 11, 2013 8:20 pm
Reply with quote

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

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Wed Sep 11, 2013 8:39 pm
Reply with quote

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
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 Extract 2 more lines below line that ... DFSORT/ICETOOL 2
No new posts Build two or more rows given some con... DFSORT/ICETOOL 9
No new posts REMOVE DUPLICATE RECORDS BASED ON A S... DFSORT/ICETOOL 4
No new posts XML Parsing in COBOL creating "h... COBOL Programming 0
No new posts Converting Hex to PD Special Condition SYNCSORT 3
Search our Forums:

Back to Top