Re: SQL on a poorly designed table

From: Marc Blum <marc_at_marcblum.de>
Date: Thu, 28 Mar 2002 14:10:14 GMT
Message-ID: <3ca32418.24795463@news.online.de>

How about that one?

SELECT *
  FROM t3
 WHERE EXISTS (SELECT NULL

                 FROM t3
                WHERE documenttype = 'b')
       AND documenttype = 'b'

UNION ALL
SELECT *
  FROM t3
 WHERE NOT EXISTS (SELECT NULL

                     FROM t3
                    WHERE documenttype = 'b')

On Thu, 28 Mar 2002 08:52:49 -0500, Pete Shankey <shankeyp_at_netscape.net> wrote:

>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
>
>

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Thu Mar 28 2002 - 08:10:14 CST