Re: Problem with a VIEW

"May, James" <mayj@fdhc.state.fl.us> wrote in article <5ekiok$6c4@cssun.mathcs.emory.edu>... > [snip] > I've run into an issue with view that I created to give name, mailing > address , street address, and bed count for hospitals on a regulatory > and admin licensing system. The view contains three outer joins, one > for each address type and one for the bed count. I've done the outers > because not every hospital has an address or bed count entered in the > system and I wanted the view to show all hospitals on the system. > > The problem I'm experiencing is that I cannot join other tables to the > view nor can I do an order by or group by with a simple select from the > view. The error message I get is "A subquery returned not exactly one > row." > > It appears the only use I can get from the view is from a simple select > (works fine). Can anyone who has had similar experiences please advise > on a solution or work-around. The SQL for the view follows: > > create view facility_directory as > select lic.lic_id lic_id, -- license number (system) > lic.pro_cde pro_cde, -- hospital type > lic.lic_nbr license_nbr, -- license number (user) > name.nme name, -- hospital name > saddr.addr_line1 saddr1, -- street address > saddr.addr_line2 saddr2, > saddr.addr_cty scity, > saddr.addr_st sstate, > saddr.addr_zip szip, > saddr.phne_area sphone_area, > saddr.phne_nbr sphone_nbr, > maddr.addr_line1 maddr1, -- mailing address > maddr.addr_line2 maddr2, > maddr.addr_cty mcity, > maddr.addr_st mstate, > maddr.addr_zip mzip, > psd_val beds, -- bed count > lic.lic_sta_cde lic_status, -- Active/Inactive license > lic.rank_cde rank_cde -- a type code > from rbdlic lic, -- license table > rbdlhn name, -- Name table > outer rbdlad saddr, -- address table > outer rbdlad maddr, > outer rbdlpd psd -- table containing bed count > where lic.pro_cde = name.pro_cde > and lic.pro_cde = saddr.pro_cde > and lic.pro_cde = maddr.pro_cde > and lic.pro_cde = psd.pro_cde > and lic.lic_id = name.lic_id > and lic.lic_id = psd.lic_id > and saddr.addr_nbr = (select addr_nbr > from rbdlar a > where a.pro_cde = lic.pro_cde > and a.lic_id = lic.lic_id > and cur_addr_ind = "Y" -- current address > and lic_addr_typ_cde = "ST") -- Street> address > and maddr.addr_nbr = (select addr_nbr > from rbdlar a > where a.pro_cde = lic.pro_cde > and a.lic_id = lic.lic_id > and cur_addr_ind = "Y" > and lic_addr_typ_cde = "ML") > and name.cur_nme_ind = "Y" > and psd.psd_id = "CAPACITY"; > > > A combination of lic_id and pro_cde will uniquely identify a record in > the license table. > > [snip] Caveat: This is based upon the limited understanding of your data model gleaned from the above. I suspect the problem lies in the sub-queries and not actually the view. Have you allowed a "simple select" of the entire view (no additional filters) to execute to completion, or merely looked at the first few rows? I suspect if you do something like: unload to "test.unl" select * from facility_directory you'll get the same error. Doing an order by or group by on the view forces the engine to process the entire view before returning any rows, thus the error shows up under these circumstances, but will only show up on a "simple select" when the problem row(s) are reached. If the filter in any one of those subqueries does not result in a single "addr_nbr" you'll get the error. Try the following select to see if you have any duplicates in the rbdlar table: select pro_cde, lic_id, lic_addr_type_id, count(*) from rbdlar where cur_addr_ind = 'Y' group by 1, 2, 3 having count(*) > 1 If you have duplicates, and you should not, then you need to clean up the duplicates for the query to work. To avoid this in the future you could change your "current" indicator from a "Y/N" column to some sort of sequence number where, for example, 0 is the current address and higher numbers are "non-current" addresses. Then build a unique index on pro_cde, lic_id, lic_addr_type, cur_addr_ind. If the duplicates are legitimate, try eliminating the subqueries and replacing them with joins on the pro_cde, lic_id, lic_addr_type and cur_addr_ind columns. This will give you one row per current address and eliminate the subquery problem. This may also improve query performance even if you do not have more than one "current" address per (pro_cde, lic_id, lic_addr_type). HTH -- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com