Re: Looking for Complex Queries.

rps_at_cray.com (Russell P. Starksen) writes:
> I'm looking for some examples of complex queries that people are
>using out there. In particular those that use mathmatical functions.
> Russ Starksen Phone#: (612) 683-3635
> Cray Research Inc. FAX#: (612) 683-3699
> 655E Lone Oak Drive E-mail: rps_at_cray.com
> Eagan, Mn. 55121

The subquery below USED to have a pythagorean's theorem algorithm applied to the geographic points. No longer, I pre-compute it.

Andy

  cursor listings(fon in number) is

	select listing from fax_listing, fax_order
	where fax_order.id = fon
	and (city_at,state_at)
	  in (select city_at, state_at
		from fax_city_master
	    where miles < nvl(fax_order.miles_away,30)
		  and city_near = fax_order.crit_city 
		  and state_near = fax_order.crit_state
		  )
	and crit_state = state_at
	and (price is null or 
		trunc(price/1000) between decode(crit_lowprice,null,0,crit_lowprice)
		  	and     decode(crit_highprice,null,100000000,crit_highprice))
	and (bedrooms is null or 
		bedrooms between decode(crit_lowbed,null,0,crit_lowbed)
		  	and     decode(crit_highbed,null,100000000,crit_highbed))
	and (bathrooms is null or 
		bathrooms between decode(crit_lowbath,null,0,crit_lowbath)
		  	and     decode(crit_highbath,null,100000000,crit_highbath))
	and (newness is null or decode(crit_new,null,1,decode(newness,null,null,1))=1)
	and (style is null or decode(crit_style,null,style,crit_style)=style)
	order by last_sent, how_many_times;

-- 
Andrew Finkenstadt  |  andy_at_{homes.com,vistachrome.com,genie.geis.com}
Systems Analyst     |  Vista-Chrome, Homes & Land Publishing Corporation
                    |  1600 Capital Circle SW, Tallahassee Florida 32310
+1 904-575-0189     |  GEnie Postmaster, Unix & Internet RoundTables Sysop