October 13, 2011 at 12:27 pm
hi
i have a subquery and i want to return two fields i want the two fields to be used in my main query in the where clause. It runs ok with one field but i get a syntax error when i had the 2nd field. Is it possible to return multiple columns in a subquery?
select *
from tbl_lender_kd_expense ohp, mf_ohp_key_data keydata
where
replace(ohp.FHA_Number,'-','') = keydata.fk_fha_number
and
ohp.project_name = keydata.fk_project_name
and
kd_source_type = 'Net Operating Income'
and (keydata.tag_year,fk_fha_number ) in (Select distinct ohp.year_1,keydata.fk_fha_number from tbl_lender_kd_expense ohp, mf_ohp_key_data keydata
where ohp.year_1 is not null
and replace(ohp.FHA_Number,'-','') = keydata.fk_fha_number
and ohp.kd_source_type = 'Effective Gross Income (from previous table)'
and ohp.project_name = keydata.fk_project_name)
when i run it with one query i get an error and it tells me that multiple rows exist. however, if i can return both values then it should work as intended.
Any assistance would be greatly appreciated
thank you in advance
October 13, 2011 at 1:09 pm
First, you really should start writing your queries using ANSI-92 style joins instead of ANSI-89 style joins. It makes your code easier to read and separates the join criteria from the filter criteria.
Try the following. It is untested as you did not provide the DDL for the tables, sample data, or expected results.
select
*
from
tbl_lender_kd_expense ohp
inner join mf_ohp_key_data keydata
on (ohp.project_name = keydata.fk_project_name
replace(ohp.FHA_Number,'-','') = keydata.fk_fha_number)
inner join (Select distinct
ohp.year_1,
keydata.fk_fha_number
from
tbl_lender_kd_expense ohp1
inner join mf_ohp_key_data keydata1
on (replace(ohp1.FHA_Number,'-','') = keydata1.fk_fha_number
and ohp1.project_name = keydata1.fk_project_name
where
ohp1.year_1 is not null
and ohp1.kd_source_type = 'Effective Gross Income (from previous table)') dt
on (keydata.tag_year = dt.year_1
and keydata.fk_fha_number = dt.fk_fha_number)
where
kd_source_type = 'Net Operating Income'
;
October 13, 2011 at 1:20 pm
thank you. i will try to write in modern times in the future when posting.
June 28, 2012 at 9:44 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply