Help with SQL/report

[bmos...@consrv.ca.gov]

Hello Informix users,

I am an Informix SE user who needs some help generating a report. I have a
table like this:

lithology.official_name integer <------key fields
top_depth real <-----/
bottom_depth real /
lith_type char(6) <---/

I am trying to generate a report of all official_name that has the
interval defined by the top_depth and bottom_depth intersecting the top_depth
and bottom_depth of the next layer(s).

For example:

official_name top_depth bottom_depth lith_type
----------------------------------------------------
WELL1 0.0 6.0 SD
WELL1 6.0 7.5 ML <--- this layer crosses
WELL1 7.25 9.0 CL <--- this layer
WELL1 9.0 14.0 SD

Hope I made my problem clear. I would greatly appreciate any help and would
be willing to summarize any responses I get. Thanks.

Bob Moskovitz
bmos...@consrv.ca.gov

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/ Now offering spam-free web-based newsreading

[Douglas Wilson]

On Tue, 19 May 1998 18:49:42 GMT, bmos...@consrv.ca.gov wrote:

>Hello Informix users,
>
>I am an Informix SE user who needs some help generating a report. I have a
>table like this:
>
> lithology.official_name integer <------key fields
> top_depth real <-----/
> bottom_depth real /
> lith_type char(6) <---/
>
>I am trying to generate a report of all official_name that has the
>interval defined by the top_depth and bottom_depth intersecting the top_depth
>and bottom_depth of the next layer(s).
>
>For example:
>
>official_name top_depth bottom_depth lith_type
>----------------------------------------------------
>WELL1 0.0 6.0 SD
>WELL1 6.0 7.5 ML <--- this layer crosses
>WELL1 7.25 9.0 CL <--- this layer
>WELL1 9.0 14.0 SD

This might work (if your data is as I think it is):
select *
from lithology a
where exists
(select 1
from lithology b
where
(a.top_depth between b.top_depth and b.bottom_depth or
a.bottom_depth between b.top_depth and b.bottom_depth)
and a.lith_type != b.lith_type
)

Hope that helps,
Douglas Wilson

[Douglas Wilson]

On Tue, 19 May 1998 20:51:52 GMT, dgwi...@gte.net (Douglas Wilson)
wrote:

>This might work (if your data is as I think it is):
>select *
>from lithology a
>where exists
>(select 1
> from lithology b
> where
> (a.top_depth between b.top_depth and b.bottom_depth or
> a.bottom_depth between b.top_depth and b.bottom_depth)
> and a.lith_type != b.lith_type

and a.official_name=b.official_name

[Douglas Wilson]

On Tue, 19 May 1998 21:00:50 GMT, dgwi...@gte.net (Douglas Wilson)
wrote:

>On Tue, 19 May 1998 20:51:52 GMT, dgwi...@gte.net (Douglas Wilson)
>wrote:

>>select *

>>from lithology a
>>where exists
>>(select 1
>> from lithology b
>> where
>> (a.top_depth between b.top_depth and b.bottom_depth or
>> a.bottom_depth between b.top_depth and b.bottom_depth)

'between' is inclusive which is probably not what you want, so
expand the 'between' with '<' and '>' and then you won't need
the following line:

[Paul Roberts]

{ How about this: }

create temp table
lithology ( official_name char(5) ,
top_depth real ,
bottom_depth real ,
lith_type char(4) ) with no log ;

{ ... I made official_name a char(5) instead of an integer,
the better to accomodate the value 'WELL1'... and I
made lith_type a char(4), I forget exacxtly why. }

insert into lithology values ( 'WELL1', 0.0, 6.0, 'SD' ) ;
insert into lithology values ( 'WELL1', 6.0, 7.5, 'ML' ) ;
insert into lithology values ( 'WELL1', 7.25, 9.0, 'CL' ) ;
insert into lithology values ( 'WELL1', 9.0, 14.0, 'SD' ) ;

select l1.official_name o_name,
l1.top_depth tp1, l1.bottom_depth bd1, l1.lith_type lt1,
l2.top_depth td2, l2.bottom_depth bd2, l2.lith_type lt2
from lithology l1, lithology l2
where l1.official_name = l2.official_name
and l2.top_depth >= l1.top_depth
and l2.top_depth < l1.bottom_depth
and l1.lith_type != l2.lith_type

{ which returns the following:

o_name tp1 bd1 lt1 td2 bd2 lt2

WELL1 6.00 7.50 ML 7.25 9.00 CL

Will that do the job, do you think?

- Paul (just lil ol' me, not a spokesman, etc) }