|
View previous topic :: View next topic
|
| Author |
Message |
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
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
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 |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
+1 to enrico.
It's better off using 3 independent selects on the three tables rather than joining all three. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
|
|
