Re: SQL syntax hel!

From: Mike <mike_at_nospam.com>
Date: Tue, 09 Jan 2001 17:11:55 GMT
Message-ID: <vDH66.13116$0s3.643452@typhoon.columbus.rr.com>

Thank you so much. Just a few moments ago I started to implement my application to accept 2 queries
(break up this one in 2 :-) ).
I am curious, is there any way to make column info appear completely empty after JOIN
for cases where status='0', so it would not display cancatanation strings -->::::<--- in my case?
I' ve got no problems to clean it up in my Java applet, but still....

Thnak you.
Mikhail

"Mike Krolewski" <mkrolewski_at_rii.com> wrote in message news:93dcj1$b5s$1_at_nnrp1.deja.com...
> In article <ffo66.12471$0s3.618050_at_typhoon.columbus.rr.com>,
> "Mike" <mike_at_nospam.com> wrote:
> > I wonder if someone could help me to resolve the following problem.
> > I need to join 3 table:
> > OUTAGE: unit, line, xdate, status, outage_id
> > DISPATCH: d_code, d_outage_id, d_truck_id, d_date
> > TRUCK: t_code, t_truck_id, t_truck_name
> >
> > select status, t_ruck_name|| '::' ||d_truck_id|| '::'
> ||d_date ''INFO" from
> > OUTAGE,DISPATCH,TRUCK
> > where xdate>sysdate-1 and (
> > (status='0' and outage_id is null) or
> > (status='1' and d_code='AAA' and d_code=t_code and
 d_outage_id=t_outage_id
> > and d_truck_id=t_truck_id)
> > )
> >
> > The problem is when STATUS='0' ==> OUTAGE_IS the column INFO should
 not
> > have any values.
> > (it cannot be connected to DISPATH.d_outage_id ( d_outage_id is never
 NULL)
> > But because I am dooing something worng (that is I am sure of :-)
 with a 3
> > table join, INFO always returned with some value.
> >
> > Basicly, result should be:
> > STATUS INFO
> > --------- -----------------------------
> > 0
> > 0
> > 1 JonhDoe::truck_1::12-jan-00
> > 1 RickTop::truck_2::14-jan-00
> >
> > (But the above query returns:
> > STATUS INFO
> > --------- -----------------------------
> > 0 JonhDoe::truck_1::12-jan-00
> > 0 JonhDoe::truck_1::12-jan-00
> > 0 RickTop::truck_2::14-jan-00
> > 0 RickTop::truck_2::14-jan-00
> > 1 JonhDoe::truck_1::12-jan-00
> > 1 RickTop::truck_2::14-jan-00
> >
> > I would appreciate your help.
> > Thanks.
> > Mike
> >
> >

>

> You are generating a cartenian join basically joining all the tables
> together. You must always explicitly have at least one condition in the
> where clause joining it to at least one other table. ( Basic SQL ).

>

> To have a table in the join sometimes, you create an outer join.

>

> For example:

>

> OUTAGE: unit, line, xdate, status, outage_id
> DISPATCH: d_code, d_outage_id, d_truck_id, d_date
> TRUCK: t_code, t_truck_id, t_truck_name

>

> select
> ot.status,
> tr.t_ruck_name|| '::' ||dis.d_truck_id|| '::' || dis.d_date ''INFO"
> from
> outage ot,
> dispatch dis,
> truck tr
> where
> ot.outage_id = dis.d_outage_id(+)
> and dis.truck_id = t.truck_id(+)

>
>

> should be the start.

>

> Note that the test for ot.outage_id being null is not needed. By
> definition, if the dt.outage_id is null, then none of the data from the
> other two tables is included. This also would allow an outage without a
> truck, if that made sense.

>

> If the code is needed, (there are other codes that you want to ignore),
> add :

>


>         and dis.d_code(+) = 'AAA'

>
>
>
>

> --
> Michael Krolewski
> Rosetta Inpharmatics
> mkrolewski_at_rii.com
> Usual disclaimers

>
>

> Sent via Deja.com
> http://www.deja.com/
Received on Tue Jan 09 2001 - 11:11:55 CST