Replace data fields v2

[RodrigoOlmedo]

Hello all and @AngelosPachis 

This is the other issue:

First table:

COUNTRY CODE	STATE CODE	CITY CODE	DESCRIPTION
001			USA
001	001		CA
001	001	001	SAN DIEGO
001	002		TX
001	002	001	AUSTIN

The second table:

CUST ID	COUNTRY CODE	STATE CODE	CITY CODE
1010	001	001	001
2020	001	002	001

And the request is the same: replace CODES by DESCRIPTION.

thank you very much for your help.

Regards

Rodrigo

[AngelosPachis · Accepted solution]

Hey @RodrigoOlmedo ,

This may not be the most orthodox of solutions, but since you have a hierarchy of "Country-State-City", I created an ID field that you can use to join on.

Do you mind if you give this approach a try and let me know if it works for you please?

Cheers,

Angelos

[RodrigoOlmedo]

Angelos, thank you for your help,

I'm still working on it but the issue that I found is when the same city exists in more than one STATE, example SAN DIEGO in CA and SAN DIEGO in TX (just example) so when makes the GROUP BY not works. 

Can you give me a hint ?

Thank you

Rodrigo

[L_T · Accepted solution]

Hi Rodrigo, 

Would the attached work?