Re: SQL on a poorly designed table

Something like

select *
from t3
where documenttype = 'b'
union
select *
from t3
where not exists(select * from t3 where documenttype = 'b')

"Pete Shankey" <shankeyp_at_netscape.net> wrote in message news:3CA32031.1060206_at_netscape.net...
> I am have a tough time figuring out how to make an SQL. The real table
> is a bit more complex, but here goes:
>
> create table t3
> (
> DOCUMENTID number(12),
> chargeid number(12),
> DOCUMENTTYPE VARCHAR2(35),
> constraint t3_pk primary key (documentid)
> using index tablespace indx
> )
>
> is loaded with data like:
>
> create sequence t3_documentiID minvalue 1
> /
>
> insert into t3(documentid,chargeid,documenttype)
> values(t3_documentiID.nextval,1,'a')
> /
>
> insert into t3(documentid,chargeid,documenttype)
> values(t3_documentiID.nextval,1,'b')
> /
>
> insert into t3(documentid,chargeid,documenttype)
> values(t3_documentiID.nextval,1,'c')
> /
>
> insert into t3(documentid,chargeid,documenttype)
> values(t3_documentiID.nextval,1,'d')
> /
>
> insert into t3(documentid,chargeid,documenttype)
> values(t3_documentiID.nextval,2,'b')
> /
>
> insert into t3(documentid,chargeid,documenttype)
> values(t3_documentiID.nextval,3,'b')
> /
>
> insert into t3(documentid,chargeid,documenttype)
> values(t3_documentiID.nextval,3,'c')
> /
>
> insert into t3(documentid,chargeid,documenttype)
> values(t3_documentiID.nextval,4,'a')
> /
>
> insert into t3(documentid,chargeid,documenttype)
> values(t3_documentiID.nextval,4,'c')
> /
>
> The goal and my question is:
> If have documenttype of 'b' then list and only list that row. If no
> documenttype 'b' then list any other row. Assuming this can be done I
> would take the SQL turn it into a view then inner join it with another
> table using chargeid as the join element. I think the main problem is
> the desginer of the schema did not propertly normalize the tables.
> However, this is what I have to work with.
>
> Thank you, for your time.
> Pete
>
>
Received on Thu Mar 28 2002 - 08:04:09 CST