-
sql query
Hi
I have 2 tables country and state that has the following data.
country
=======
code value
---------------
in india
us usa
uk britain
state
code value
----------------
ca california
mi michigan
io iowa
i am getting the value for code (tempcode) from a different source. If tempcode is one among the codes that is in state table then i want to retrive the country.code as 'US' else i want to retrieve the value as it is. there is no link between the 2 tables.
Please help to provide with a sql query on this. Can we use decode function in this case.
Please reply as this is urgent.
VVS Raju
8i DBA OCP
-
Without joining tables how can u get desired results..
the ans wud be no to ur question..
Try with referencing code column of country as country_code in state table.
during loading from tempcode(source)..build a logic to load appropriate country code.
Abhay.
Last edited by abhaysk; 04-14-2003 at 03:30 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
SELECT 'US' FROM state WHERE state.code = temp.code
UNION ALL
SELECT country.code FROM country
WHERE NOT EXISTS(SELECT 'US' FROM state WHERE state.code = temp.code)
AND country.code = temp.code
HTH
- Nandu
Never give up !
Nanda Kumar - Vellore
-
Originally posted by nandu
SELECT 'US' FROM state WHERE state.code = temp.code
UNION ALL
SELECT country.code FROM country
WHERE NOT EXISTS(SELECT 'US' FROM state WHERE state.code = temp.code)
AND country.code = temp.code
HTH
- Nandu
error it will not give required o/p
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
error it will not give required o/p
Abhay, Why? !!
I guess one of us have got the requirement wrong !!!
- Nandu
Never give up !
Nanda Kumar - Vellore
-
Originally posted by nandu
Abhay, Why? !!
I guess one of us have got the requirement wrong !!!
- Nandu
Check his quote
If tempcode is one among the codes that is in state table then i want to retrive the country.code as 'US'
Code:
SELECT 'US' FROM state WHERE state.code = temp.code
correct.
But
else i want to retrieve the value as it is. there is no link between the 2 tables.
Code:
SELECT country.code FROM country
WHERE NOT EXISTS(SELECT 'US' FROM state WHERE state.code = temp.code)
AND country.code = temp.code
error. ( Temp.code consists codes among state.code or not )
If not he wants
in india
uk britain
But from ur code the join "country.code = temp.code" fails watsoever.
coz it has codes like (CA,IO and so). If not he want its particular country, which you cant get.
Just an eg
Say temp.code='BN' ...its code of bangalore city ( country -> India )
from ur code nither prior union will match not after union.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
You have made few points Abhay. True.
But reading between the lines.
If tempcode is one among the codes that is in state table then i want to retrive the country.code as 'US'
He doesn't say that tempcode will not contain the values of country code. He has just mentioned that
there is no link between the 2 tables.
I believe it is one of those applications(as we see on the internet)to select the state if you are residing in US etc., etc,.
I believe he wants to select the Country as US if the value in Tempcode finds a match in State table or the equivalent of the country code which the Tempcode will carry.
You have assumed the design to be in fine granularity
ie., Bangalore, Chennai.. Melbourne, Manchester etc.,
In that case he would not have post this question at all
Would you agree?
- Nandu
Never give up !
Nanda Kumar - Vellore
-
All i can say is, let him come back with requirement precisely.
Abahy.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|