SQL query to add the state value dynamically

Question

The below query returns value such as - 'GA','CA','AL'

Query#1

  (SELECT Upper(hzg.geography_name) 
                 FROM   hz_geographies HZG, 
                        hz_geography_identifiers HZGI, 
                        hz_geography_identifiers HZGI1 
                 WHERE  hzg.country_code = 'US' 
                        AND hzg.geography_use = 'MASTER_REF' 
                        AND hzg.geography_type = 'STATE' 
                        AND hzg.geography_element1 = 'United States' 
                        AND hzgi.geography_id = hzg.geography_id 
                        AND hzgi.identifier_subtype = 'STANDARD_NAME' 
                        AND hzgi.identifier_type = 'NAME' 
                        AND hzgi.language_code = 'US' 
                        AND hzgi.geography_use = 'MASTER_REF' 
                        AND hzgi.geography_type = 'STATE' 
                        AND hzgi.primary_flag = 'N' 
                        AND hzgi1.geography_id = hzgi.geography_id 
                        AND hzgi1.identifier_subtype = 'GEO_CODE' 
                        AND hzgi1.identifier_type = 'CODE' 
                        AND hzgi1.language_code = 'US' 
                        AND hzgi1.geography_use = 'MASTER_REF' 
                        AND hzgi1.geography_type = 'STATE' 
                        AND hzgi1.primary_flag = 'N' 
                        AND Substr(hzgi1.identifier_value, 1, 
                            Instr(hzgi1.identifier_value, '-') 
                            - 1) = 
                            pdcc.context_value1) 

I want to use the above query in another query such that - query #2

(SELECT DISTINCT Decode(dir_information_char3, 'Y', 'Yes', 
                                                               'N', 'No', 
                                                               '') 
                 FROM   pay_dir_card_components_f PDCCF3, 
                        pay_dir_comp_details_f PDCDF3 
                 WHERE  PDCCF3.dir_card_id = PDCF.dir_card_id 
                  and pdcc.context_value1 = PDCCF3.context_value1
                        AND PDCCF3.dir_card_comp_id = PDCDF3.dir_card_comp_id 
                        AND PDCDF3.dir_information_category = 
                            'HRX_US_WTH_STATE_GA')

HRX_US_WTH_STATE_GA Should change according to query #1.

i.e. if the query 1 returns 'CA' then query #2 should come like -

(SELECT DISTINCT Decode(dir_information_char3, 'Y', 'Yes', 
                                                               'N', 'No', 
                                                               '') 
                 FROM   pay_dir_card_components_f PDCCF3, 
                        pay_dir_comp_details_f PDCDF3 
                 WHERE  PDCCF3.dir_card_id = PDCF.dir_card_id 
                  and pdcc.context_value1 = PDCCF3.context_value1
                        AND PDCCF3.dir_card_comp_id = PDCDF3.dir_card_comp_id 
                        AND PDCDF3.dir_information_category = 
                            'HRX_US_WTH_STATE_CA')

how can we do that

Answer 1

Just a simple subquery should do the trick no?

SELECT DISTINCT Decode(dir_information_char3, 'Y', 'Yes', 
                                                               'N', 'No', 
                                                               '') 
                 FROM   pay_dir_card_components_f PDCCF3, 
                        pay_dir_comp_details_f PDCDF3 
                 WHERE  PDCCF3.dir_card_id = PDCF.dir_card_id 
                  and pdcc.context_value1 = PDCCF3.context_value1
                        AND PDCCF3.dir_card_comp_id = PDCDF3.dir_card_comp_id 
                        AND PDCDF3.dir_information_category = 
                 (SELECT 'HRX_US_WTH_STATE_'||Upper(hzg.geography_name) 
                 FROM   hz_geographies HZG, 
                        hz_geography_identifiers HZGI, 
                        hz_geography_identifiers HZGI1 
                 WHERE  hzg.country_code = 'US' 
                        AND hzg.geography_use = 'MASTER_REF' 
                        AND hzg.geography_type = 'STATE' 
                        AND hzg.geography_element1 = 'United States' 
                        AND hzgi.geography_id = hzg.geography_id 
                        AND hzgi.identifier_subtype = 'STANDARD_NAME' 
                        AND hzgi.identifier_type = 'NAME' 
                        AND hzgi.language_code = 'US' 
                        AND hzgi.geography_use = 'MASTER_REF' 
                        AND hzgi.geography_type = 'STATE' 
                        AND hzgi.primary_flag = 'N' 
                        AND hzgi1.geography_id = hzgi.geography_id 
                        AND hzgi1.identifier_subtype = 'GEO_CODE' 
                        AND hzgi1.identifier_type = 'CODE' 
                        AND hzgi1.language_code = 'US' 
                        AND hzgi1.geography_use = 'MASTER_REF' 
                        AND hzgi1.geography_type = 'STATE' 
                        AND hzgi1.primary_flag = 'N' 
                        AND Substr(hzgi1.identifier_value, 1, 
                            Instr(hzgi1.identifier_value, '-') 
                            - 1) = 
                            pdcc.context_value1)