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
 

 

Need a solution regarding WHERE condition

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sreenigacc

New User


Joined: 16 Oct 2007
Posts: 15
Location: bangalore

PostPosted: Thu Sep 25, 2008 11:24 am    Post subject: Need a solution regarding WHERE condition
Reply with quote

The current scenario is in a cobol+db2 program is like this

DELETE FROM TABLENAME
WHERE NAME = :WS-NAME
AND (COMPANY = 'A' OR
COMPANY = 'B')

OR

SELECT X, Y, Z FROM TABLENAME
WHERE NAME = :WS-NAME
AND (COMPANY = 'A' OR
COMPANY = 'B')


In future if we need to add one or more values in COMPANY column(We know that some more companys will add like 'C' , 'D' etc..), then again we need to change this cobol+db2 program.

instead of changing the program again and again, any one can help me out on this.

(what I am seeking for this is instead of changing in program, can we handle this scenario with DB2 tables or corporate tables etc...)
thanks in advance
sreeni
Back to top
View user's profile Send private message

zero

New User


Joined: 01 Dec 2007
Posts: 21
Location: Hyderabad

PostPosted: Thu Sep 25, 2008 11:53 am    Post subject: Reply to: Need a solution regarding WHERE condition
Reply with quote

Hi Sreeni,
i had the same type of scenario some time back. You asked whether this can be achieved by using some db2 Table. Yes, we can, provided we have a table which can contain all the possible values for 'COMPANY' field.

Assume, we have a table "CODE_TABLE" with the following data :

CODE_NAME CODE_VALUE
COMPANY A
COMPANY B


Code:
DELETE
FROM      TABLENAME
WHERE    NAME = :WS-NAME
AND        COMPANY IN (SELECT  CODE_VALUE
                                   FROM     CODE_TABLE
                                   WHERE   CODE_NAME = 'COMPANY');
The above query will serve as a solution to your Query.
Now, we no do not have to change the program. we just need add new values to the CODE_TABLE.

if you have any table like the above, it will be fine.

One more possible solution i can think of is Dynamic Queries.
we can have all the possible values for COMPANY in a File or we can send them thru PARM in the JCL. after reading these values in the program, we can create a query.


Thanks,
...
Back to top
View user's profile Send private message
sreenigacc

New User


Joined: 16 Oct 2007
Posts: 15
Location: bangalore

PostPosted: Thu Sep 25, 2008 2:15 pm    Post subject: Reply to: Need a solution regarding WHERE condition
Reply with quote

Hi,

first of all thank you very much for your valuable suggestions and you are correct, if we use a DB2 table.

here my concers are
1)There is no DB2 table for this company code as of now. so is it proper way to create a DB2 table with these columns company code and compay indicator.

2) Currently we are using Corporate tables, which will store the above data but the problem is we can't use them as a db2 query. these corporate tables are called by a service(program) and finally it throws a record with all the values of a particular record in that table.

3) If you have any idea about coporate tables please provide me the suggestions and let me know...........

I appreciate your help
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: Thu Sep 25, 2008 8:25 pm    Post subject:
Reply with quote

Hello,

You might consider accessing the corporate tables directly rather than using the service program. . .
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 Joinkeys with condition scdinesh DFSORT/ICETOOL 10 Tue Feb 14, 2017 12:20 am
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts INCLUDE condition questions van bui DFSORT/ICETOOL 1 Thu Jun 30, 2016 9:52 am
No new posts 2 vsam file compare and update a fiel... ABINAYATHULASI DFSORT/ICETOOL 6 Sun May 29, 2016 11:15 am
This topic is locked: you cannot edit posts or make replies. Need help in design the solution ritnihi PL/I & Assembler 1 Tue Dec 22, 2015 1:48 pm


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