Puzzle: Building this into a better SQL?

From: Boyce G. Williams, Jr. <bgwillia_at_vcu.edu>
Date: Thu, 25 Feb 1999 19:09:30 GMT
Message-ID: <36d58bdc.16673302@usenet.acw.vcu.edu>

Hi,

I built a SQL query where for any telephone line, display either the active department or the last department that used that line. The vender uses nulls to indicate an active department in its REMOVE_DATE field or a date if that department stopped using that line. The telephone line can have multiple departments assigned to it during its history. The trick I used is by using DECODE to substitute a very high date if the field contains nulls (I figure that on December 31, 4000 both me and the query will be well in our graves) or just use the already existing date, then select what department has the greater date. When I stub-tested the components of this query, it all works fine. However, when I put it all together and run it as single correleted query the machine times out on me. So I'm trying to learn whether there's a better, faster way to do this query. I'm open for suggestions.

Here's the query. I'm using a very old version of Clear: Access to enable my PC connect to Oracle on a AIX platform via ODBC connection. I start building a query by using the product's graphical interface to perform a "Query By Example" to form the general structure of the query. I then switch over to the code for the finishing touches that the graphical interface can't handle. So please excuse any "strangeness" my query may have. When I setup and run the "inner query" seperately it does work. Its only problem is it won't let me create views which was my first thought to solve this puzzle (I suspect it's a security issue somewhere):

TRANSACTIONREADONLY;
SELECT
'Unrated Line'=TELECOM.UNRATED.FROM_NUMBER ,
'Number of Calls'=Count(*) ,
'Earliest Call'=MIN(TELECOM.UNRATED.CONNECT_DATE) ,
'Dept'=TELECOM.SUBSCRIB.DEPARTMENT_NUMBER
 FROM

	TELECOM.ASSGNEQP t_alias1, 
	TELECOM.SUBSCRIB, 
	TELECOM.UNRATED
 WHERE 
 	TELECOM.UNRATED.UNRATE_REASON  = '12'

 AND DECODE(t_alias1.REMOVE_DATE,NULL,{d'4000-12-31'},t_alias1.REMOVE_DATE)

        =(SELECT MAX(DECODE(t_alias2.REMOVE_DATE,NULL,{d'4000-12-31'},t_alias2.REMOVE_DATE))

	    FROM
		  TELECOM.ASSGNEQP t_alias2
	   WHERE
		  LEFT(t_alias2.BILL_NOTE, 10)=LEFT(t_alias1.BILL_NOTE, 10))
 AND 
	TELECOM.UNRATED.FROM_NUMBER=LEFT(t_alias1.BILL_NOTE, 10)
 AND 
	t_alias1.SUBSCRIBER_ID=TELECOM.SUBSCRIB.SUBSCRIBER_ID
 GROUP BY 
	TELECOM.UNRATED.FROM_NUMBER , 
	TELECOM.SUBSCRIB.DEPARTMENT_NUMBER;

PrintAll;
Commit;

Boyce G. Williams, Jr.

 .--------------------------------------------------------------------.
 | "People should have two virtues:  purpose- the courage to envisage |
 | and pursue valued goals uninhibited by the defeat of infantile     |
 | fantasies, by guilt and the failing fear punishment;  and wisdom- a|
 | detached concern with life itself, in the face of death itself."   |
 |                                                     Norman F. Dixon|
 '--------------------------------------------------------------------'

Received on Thu Feb 25 1999 - 13:09:30 CST