Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Use of "OR" condition in "Where' clause of a

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Use of "OR" condition in "Where' clause of a
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: 686
Location: Earth

PostPosted: Wed Sep 11, 2013 7:44 pm    Post subject:
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: 1777
Location: Bloomington, IL

PostPosted: Wed Sep 11, 2013 7:57 pm    Post subject:
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    Post subject:
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: 1777
Location: Bloomington, IL

PostPosted: Wed Sep 11, 2013 8:39 pm    Post subject:
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    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 BUILD OUTFIL based on condition other... balaji81_k DFSORT/ICETOOL 13 Fri Sep 08, 2017 11:06 pm
This topic is locked: you cannot edit posts or make replies. Extract all "IF" Statements... Adarsh Damodaran CLIST & REXX 1 Wed Sep 06, 2017 9:28 am
No new posts Changeman get multiple "Browse C... elixir1986 Compuware & Other Tools 4 Fri Jul 14, 2017 1:48 am
No new posts Include/omit with "ALL" pa... mgl DFSORT/ICETOOL 6 Wed Jun 14, 2017 2:31 pm
This topic is locked: you cannot edit posts or make replies. Need help on If condition continuatio... ashok_uddaraju CLIST & REXX 4 Fri May 19, 2017 8:55 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us