Re: Problem with a VIEW
May, James wrote:
--- SNIP ---
> 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.
Interesting query, James.
I see two correlated subqueries where I think a join would have done the
job. If you have properly set up lic_id and pro_cde keys as primary
keys, then these should certainly work, however ineficiently. And you
claim that the error does not occur when you do a simple query, so these
subqueries are [probably] not at fault.
Experiment:
Select the entire view into a temp table and run your offending query as
a join with the temp table instead of with the view. I think your query
will still get that error. My first impulse is to question the join
query, which you have not provided.
I can think of no reason why an order by on the view alone would hit you
with this error.
If it persists, consider rephrasing the subquery as a join. From what I
can see (which may be incorrect) you are using it to effect a join of
table rbdlar to the other tables already joined. Correlated subqueries
may have [un]funny consequences in their implementation.
--
-- Jake (Lost in thought and won't ask for directions)
. .
_..-'( )`-.._
./'. '||\\\\. }\\_/{ .//||` .`\\.
./'.|'.'||||\\\\|.. )o o( ..|//||||`.`|.`\\.
./'..|'.|| |||||\\`````` \\,@,/ ''''''/||||| ||.`|..`\\.
./'.||'.|||| ||||||||||||. ||| .|||||||||||| ||||.`||.`\\.
/'|||'.|||||| ||||||||||||{ | }|||||||||||| ||||||.`|||`\\
'.|||'.||||||| ||||||||||||{ | }|||||||||||| |||||||.`|||.`
'.||| ||||||||| |/' ``\\||`` | ''||/'' `\\| ||||||||| |||.`
|/' \\./' `\\./ \\!|\\ /|!/ \\./' `\\./ `\\|
V V V }' `\\ /' `{ V V V
\\ \\ \\ V / / /
+-----------------------------------------------------------+
| Impeccable Logic: A thought process which successfully |
| resists chicken bites |
+-----------------------------------------------------------+