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
 
COBOL DB2 Logic checking the Zipcode, State ID and City Code

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

Active User


Joined: 02 Oct 2008
Posts: 151
Location: India

PostPosted: Thu May 27, 2010 2:40 am    Post subject: COBOL DB2 Logic checking the Zipcode, State ID and City Code
Reply with quote

Hi all,

My reqt is as follows:
In the CICS screen, the user enters State Number, City Code and Zip code and I need to validate whether all the 3 are matching using 3 tables
The State No is in Table A, City code is in Table B, Zip code is in Table C

TABLE A
Code:
ST_ID       ST_NO
TX          42


TABLE B
Code:
CITY             ST_ID     CITY_CD 
PORT TYRO        TX           123
LAVAK            TX           111
PLANO            TX           170 

TABLE C
Code:
ZIP        ST_ID   CITY
75093      TX      PLANO
77979      TX      LAVAK

I have entered the State ID as 42, City code as 111 and Zip code as 75093. I should not get any record extracted because the City code 111 is for LAVAK city and Zip code 75093 is for Plano City. But the below query extracted me a record.

Code:
SELECT COUNT(*) FROM TABLEA A, TABLEB B, TABLEC C
 WHERE A.ST_NO = 42
     AND B.CITY_CD = 111
     AND C.ZIP = 75093
     AND A.ST_ID = B.ST_ID
     AND A.ST_ID = C.ST_ID
    AND  B.ST_ID = C.ST_ID

NOTE: I can;t use CITY name for comparison as the City name in TABLE B and TABLE C are not in sync.
City code is only in Table B.

I think I am using the incorrect Query. Coulf anyone please help me in checking the Zipcode, State ID and City Code matching.

Thanks
Vinu
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10327
Location: italy

PostPosted: Thu May 27, 2010 9:46 am    Post subject: Reply to: COBOL DB2 Logic
Reply with quote

the query is correct...
poor application/data design, You got what You asked for...
You have three tables with unrelated keys...
so the only thing to do is to review the application and the data design ..
seems silly to have two different tables for the city id and the zip code
( nothing relates them)
it will produce the result You are complaining about!
the city name in this case, from CODD's point of view are unrelated attributes of two different tables ,
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu May 27, 2010 10:41 am    Post subject:
Reply with quote

+1 to enrico.
It's better off using 3 independent selects on the three tables rather than joining all three.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu May 27, 2010 1:55 pm    Post subject:
Reply with quote

besides the poor design it's the requirement that is false :

Quote:
I should not get any record extracted because the City code 111 is for LAVAK city and Zip code 75093 is for Plano City

so CITY name is different
Quote:
I can;t use CITY name for comparison as the City name in TABLE B and TABLE C are not in sync.

so CITY name is uncomparable

How to detect a difference in uncomparable fields?
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 151
Location: India

PostPosted: Wed Jun 02, 2010 5:39 am    Post subject:
Reply with quote

Thanks Guys.
I have todl the DBA guys about the poor table esign. They are planning to make the correction by adding City code in Table C. I think that solves the purpose.

Thanks
Chidam
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Jun 03, 2010 3:09 pm    Post subject:
Reply with quote

Actually It would be best - besides adding city_cd to tableC -
to remove city(name) and st_cd from tableC
at least when multiple zipcodes can have the same city_cd.
otherwise just adding ZIP to tableB and dropping TableC would be the solution.

suppose
Code:
TableB :
CITY             ST_ID     CITY_CD 
LAVAK            TX           111

TableC
ZIP        ST_ID   CITY       CITY_CD
75093      TX      PLANO       111

you still wouldn't know which city is correct TX,PLANO or TX,LAVAK.
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 IEW2456E error when link-editing a C ... Senthilraj JCL & VSAM 0 Fri Oct 13, 2017 3:12 pm
No new posts Assembler Macro Copybook converted to... Ziquilix PL/I & Assembler 4 Tue Sep 26, 2017 3:07 am
No new posts Speed-up/reduce CPU use of code prino PL/I & Assembler 0 Tue Sep 26, 2017 12:05 am
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 ALPHABETIC check in COBOL vidyaa COBOL Programming 8 Thu Aug 17, 2017 7:13 pm

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