Home » SQL & PL/SQL » SQL & PL/SQL » Large query with multiple conditional joins
Large query with multiple conditional joins [message #248942] Mon, 02 July 2007 15:24 Go to next message
elbarto
Messages: 11
Registered: July 2007
Location: Buenos Aires
Junior Member
Hi guys,

This is my first post here. I'm not sure if this is the best forum to do it, but I figured since it's about procedures, trigger, etc. it should be ok.

Here's my problem.
I have a table which contains call records:

SQL> desc cdr_record;
Name Null? Type
----------------------------------------- -------- ----------------------------
RECORDID NUMBER
FROM_TN VARCHAR2(20)
TO_TN VARCHAR2(20)
CONNECT_TIME DATE
ELAPSED_TIME NUMBER
HEADERID NUMBER
ANSWERED NUMBER(1)
AUTHCODE VARCHAR2(14)

The most important here are from_tn (the caller's telephone number), to_tn (the receiver's phone number) and connect_time (time when the call took place).

I have an application where you can generate reports from those call records. The user has a number of filters that he can apply to generate the report (start and end date, owner of the line, if the calls are incoming, outcoming or both, etc.). All this infomation is in other tables of the database, so according to the filter he chooses I need to make some joins or not.

For this reason I want to create a function or stored procedure which receives an object with the filters and retrieves the information needed.

For example, the first thing I need to check is that the records the user sees are only the ones related to his lines. So I need to filter the records he can see by checking if the from_tn or the to_tn is among his lines.

As I read here:
http://www.orafaq.com/forum/t/11970/2/
I think I could create a view with the records of the line that belong to the user, and then work with that. But when it comes to "conditional conditions" I'm completely lost.

What do I mean with conditional conditions?
For example, if I receive a UserId I need to filter the lines that belong to that user. If I receive a flag saying that missed calls should be displayed I should add something like "AND r.answered = 1".

I don't know if I'm clear.

I think most probably my problem is that I'm not thinking this right, because I don't know much about PL/SQL. I would appreciate any kind of enlightment.

Thanks








Re: Large query with multiple conditional joins [message #248944 is a reply to message #248942] Mon, 02 July 2007 15:28 Go to previous messageGo to next message
elbarto
Messages: 11
Registered: July 2007
Location: Buenos Aires
Junior Member
BTW: currently the report is being generated by using a single horribly large query which is created by PHP following the logic I need.

However the query is awfully expensive and is executed many times, so I need to optimize all this process performance as much as possible.
Re: Large query with multiple conditional joins [message #248949 is a reply to message #248944] Mon, 02 July 2007 17:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You could create a procedure that accepts values for various parameters and defaults to null for those parameters that are not provided, then evaluate each condition using something like "and column_value = nvl (parameter_value, column_value)". That way if a parameter_value is provided, the evaluated condition becomes "column_value = parameter_value", but if no value is provided, then it evaluates to "column_value = column_value" and should therefore be ignored and not affect performance. It would be something like:

CREATE OR REPLACE PROCEDURE your_proc
  (p_results  OUT SYS_REFCURSOR,
   p_answered IN  NUMBER DEFAULT NULL,
   ... other parameters in same manner)  
AS
BEGIN
  OPEN p_results FOR 
  SELECT ... whatever columns you want
  FROM   cdr_record r, other_tables t
  WHERE  ... join conditions
  AND    r.answered = NVL (p_answered, r.answered)
  ... other filter conditions in same manner; 
END;
/      
VARIABLE g_results REFCURSOR
SET AUTOPRINT ON
-- for all values of answered:  
EXEC your_proc (:g_results)
-- for answered = 1:  
EXEC your_proc (:g_results, 1)
-- or from pl/sql, which allows you to specify named parameters
-- in any order or skip them if they have default values: 
BEGIN
  your_proc
    (p_results  => :g_results,
     p_answered => 1);
END;
/
 

Re: Large query with multiple conditional joins [message #248954 is a reply to message #248949] Mon, 02 July 2007 18:41 Go to previous messageGo to next message
elbarto
Messages: 11
Registered: July 2007
Location: Buenos Aires
Junior Member
Thanks Barbara.
That kind of help with the approach was what I was looking for.

I will try your suggestion and look into my code to see if it works for all the conditions I need to set.

I will let you know if I need anything else.

Thanks again,

Regards

Re: Large query with multiple conditional joins [message #249139 is a reply to message #248949] Tue, 03 July 2007 08:29 Go to previous messageGo to next message
elbarto
Messages: 11
Registered: July 2007
Location: Buenos Aires
Junior Member
I've been applying Barbara's suggestion and it works for most of the conditions I need to apply, but there are some that are a little bit different:

1. I need to add a left join if I'm receiving groupid.
In case the groupid is not null I'm doing this:

LEFT JOIN centrex_line_group lg ON (lg.tn = l_from.tn  OR  lg.tn = l_to.tn )


I thought that if I change the join style and put all the tables after the FROM and then put all the conditions after the WHERE, I could use NVL, but I wouldn't know what to put on the "IS NULL" part of the function, because I don't want the join to be made if the value is null.

2. I need to put a different condition depending on a third variable. For example, if the user is an Administrator I need to find his data in a specific table, and if it is a regular user I need to look for it in a different table. This is what I was doing in PHP:

switch ($aFilters['iUserType'])
{
	case UT_ADMIN:
		$sSql .= " AND ((l_from.gcxid = :gcxid) OR (l_to.gcxid = :gcxid))\n";
		break;
	case UT_SUBADMIN:
	case UT_USER:
		$sSql .= " AND ((ul_from.userid = :loggedUser) OR (ul_to.userid = :loggedUser))\n";
		break;
}


3. (and last) The user can choose to see INCOMING, OUTCOMING or BOTH type of calls. If he selects BOTH I don't need to do anything. But if he chooses incoming or outcoming I need to check that the from_tn or the to_tn of each record is among the recordset of another query.

In PHP I was doing it like this:

if($aFilters['type'] == 'incoming')
{
	$sSql .= "   AND l_to.tnid  IN (" . $sTypeFilterSql . ")\n";
}else{
	$sSql .= "   AND l_from.tnid  IN (" . $sTypeFilterSql . ")\n";
}

Where $sTypeFilterSql is a variable that stores the SQL that would give me the all the IDs of the lines that belong to a user.

I don't want you to make all the code for me, but I would appreciate the kind of help Barbara gave which help me to think the issue in a different way. I think that's my biggest problem as, again, I'm not a PL/SQL programmer.

Thanks in advance
Re: Large query with multiple conditional joins [message #249316 is a reply to message #248942] Wed, 04 July 2007 01:36 Go to previous messageGo to next message
fastfreeeasy
Messages: 25
Registered: June 2007
Junior Member
For this type of complex query where there are multiple joins might occur, it is better to use dynamic SQL. Based on the parameters you required create dynamic SQL and execute it.
Re: Large query with multiple conditional joins [message #249524 is a reply to message #249316] Wed, 04 July 2007 14:14 Go to previous messageGo to next message
elbarto
Messages: 11
Registered: July 2007
Location: Buenos Aires
Junior Member
Ok, I will check that out.
If you have a link to some kind of documentation that covers this type of queries that you can provide it would be really useful.
I'll search the web in the meantime.

Thanks
Re: Large query with multiple conditional joins [message #249539 is a reply to message #249524] Wed, 04 July 2007 23:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Your exact requirements are a little fuzzy and I wonder if the left join syntax with an or condition will return the results you want. It might help if you were to post some create table and insert scripts for sample data, and examples of parameter values that would be passed and the results that you want based on those. Please see the sticky at the top of the forum for posting guidelines. If there is a way that you can standardize your query and avoid dynamic sql, it is likely to be more efficient. You can search for things like "dynamic sql" and "case" and "decode" in the online documentation, for syntax and examples. If you have to resort to dynamic sql, you would just be building and executing a string as you have been in php, something like this:

CREATE OR REPLACE PROCEDURE your_proc
  (p_results  OUT SYS_REFCURSOR,
   p_answered IN  NUMBER DEFAULT NULL,
   p_groupid  IN  NUMBER DEFAULT NULL,
   ... other parameters in same manner)  
AS
  v_sql           VARCHAR2 (32767); 
BEGIN
  v_sql := 'SELECT * FROM cdr_record l';
  IF p_groupid IS NOT NULL THEN
    v_sql := v_sql || ' LEFT JOIN centrex_line_group lg ON (lg.tn = l.from_tn  OR  lg.tn = l.to_tn )';  
  END IF; 
  ... other join conditions in similar manner
  v_sql := v_sql || ' WHERE 1 = 1';  
  IF p_answered IS NOT NULL THEN
    v_sql := v_sql || ' AND r.aswered = ' || p_answered;
  END IF;
  ... other filter conditions in similar manner
  OPEN p_results FOR v_sql;
END your_proc;
/      



Re: Large query with multiple conditional joins [message #249587 is a reply to message #249539] Thu, 05 July 2007 02:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please consider Barbara's example as (I think) she meant it to be: a rough outlined example.
In your production version you should use bind variables instead of concatenating the variables into the query.
This will both enhance scalability and security (prevents sql injection)

[Updated on: Thu, 05 July 2007 02:13]

Report message to a moderator

Re: Large query with multiple conditional joins [message #249652 is a reply to message #249539] Thu, 05 July 2007 07:57 Go to previous messageGo to next message
elbarto
Messages: 11
Registered: July 2007
Location: Buenos Aires
Junior Member
Ok, here are the most important tables.

CREATE TABLE SRV_VOIP_LINES
(
  TNID              NUMBER,
  IDCLIENTE         NUMBER,
  IDEMPRESA         NUMBER,
  IDPRODUCTO        NUMBER                      DEFAULT 0,
  IDVENTA           NUMBER                      DEFAULT 0,
  FECHAALTA         DATE                        DEFAULT sysdate,
  FECHAMODIF        DATE,
  FECHAACTIVACION   DATE,
  DISABLED          NUMBER                      DEFAULT 0,
  PROVISIONING      NUMBER                      DEFAULT 0,
  TN                NUMBER,
  SIP_USERNAME      VARCHAR2(255 BYTE),
  SIP_PASSWORD      VARCHAR2(255 BYTE),
  DEVICE_PORT       NUMBER,
  LINE_TYPE         VARCHAR2(32 BYTE),
  ADMINSTATUS       VARCHAR2(15 BYTE),
  ROUTINGPROFILEID  NUMBER,
  DEVICEPROFILEID   NUMBER,
  SSPROFILEID       NUMBER,
  CARRIERPROFILEID  NUMBER,
  GCXID             NUMBER,
  MTAID             NUMBER,
  VOIPDEVICEID      NUMBER,
  GWCID             NUMBER,
  NAME              VARCHAR2(50 BYTE),
  EXTENSION         VARCHAR2(5 BYTE)            DEFAULT null,
  TEMPLATEID        NUMBER(11)                  DEFAULT null,
  EXTERNAL_INFO     VARCHAR2(255 BYTE)
)

CREATE TABLE CDR_RECORD
(
  RECORDID      NUMBER,
  FROM_TN       VARCHAR2(20 BYTE),
  TO_TN         VARCHAR2(20 BYTE),
  CONNECT_TIME  DATE,
  ELAPSED_TIME  NUMBER,
  HEADERID      NUMBER,
  ANSWERED      NUMBER(1),
  AUTHCODE      VARCHAR2(14 BYTE)
)

CREATE TABLE CENTREX_USERS
(
  USERID                NUMBER                  NOT NULL,
  CENTREXID             NUMBER,
  USERNAME              VARCHAR2(32 BYTE)       NOT NULL,
  USERPASSWORD          VARCHAR2(32 BYTE)       NOT NULL,
  NAME                  VARCHAR2(50 BYTE)       NOT NULL,
  EMAIL                 VARCHAR2(100 BYTE),
  USERTYPE              NUMBER(1)               DEFAULT 0                     NOT NULL,
  USERPARENT            NUMBER,
  FAILED_LOGINS         NUMBER                  DEFAULT 0,
  LAST_FAILED_LOGIN     DATE,
  LAST_PASSWORD_CHANGE  DATE
)

CREATE TABLE CENTREX_USER_LINES
(
  USERID  NUMBER                                NOT NULL,
  TNID    NUMBER								NOT NULL
)

CREATE TABLE CENTREX_LINE_GROUP
(
  GROUPID  NUMBER,
  TN       NUMBER,
  ORDERNO  NUMBER(2)
)


And here is some sample data:

Insert into SRV_VOIP_LINES
   (TNID, IDCLIENTE, IDEMPRESA, IDPRODUCTO, IDVENTA, 
    FECHAALTA, FECHAMODIF, FECHAACTIVACION, DISABLED, PROVISIONING, 
    TN, SIP_USERNAME, SIP_PASSWORD, DEVICE_PORT, LINE_TYPE, 
    ADMINSTATUS, ROUTINGPROFILEID, DEVICEPROFILEID, SSPROFILEID, CARRIERPROFILEID, 
    GCXID, MTAID, VOIPDEVICEID, GWCID, NAME, 
    EXTENSION, TEMPLATEID, EXTERNAL_INFO)
 Values
   (1601, 656551, 1, 0, 1199819, 
    TO_DATE('06/05/2007 16:10:56', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/22/2007 12:38:02', 'MM/DD/YYYY HH24:MI:SS'), NULL, 0, 0, 
    13770010, 'sip13770010', '13770010', NULL, NULL, 
    'ACTIVE', 1, 1, 1, 1, 
    87, NULL, NULL, 85, 'Some Name', 
    '0010', NULL, NULL);	
	
Insert into CENTREX_USERS
   (USERID, CENTREXID, USERNAME, USERPASSWORD, NAME, 
    EMAIL, USERTYPE, USERPARENT, FAILED_LOGINS, LAST_FAILED_LOGIN, 
    LAST_PASSWORD_CHANGE)
 Values
   (68, 48, 'charlie27', 'ab123456', 'Carlos Benavidez', 
    'asdas@hotmail.com', 0, 64, 0, NULL, 
    TO_DATE('05/31/2007 12:51:51', 'MM/DD/YYYY HH24:MI:SS'));
	
Insert into CENTREX_USER_LINES
   (USERID, TNID)
 Values
   (68, 1601);
   
Insert into CENTREX_LINE_GROUP
   (GROUPID, TN, ORDERNO)
 Values
   (40, 1601, NULL);
   
   
Insert into CDR_RECORD
   (RECORDID, FROM_TN, TO_TN, CONNECT_TIME, ELAPSED_TIME, 
    HEADERID, ANSWERED, AUTHCODE)
 Values
   (320108, '13770010', '0019011103535', TO_DATE('05/24/2007 11:05:31', 'MM/DD/YYYY HH24:MI:SS'), 3000, 
    2071, 1, NULL);
Insert into CDR_RECORD
   (RECORDID, FROM_TN, TO_TN, CONNECT_TIME, ELAPSED_TIME, 
    HEADERID, ANSWERED, AUTHCODE)
 Values
   (320109, '13777557', '3770010', TO_DATE('05/24/2007 11:05:59', 'MM/DD/YYYY HH24:MI:SS'), 2000, 
    2071, 1, NULL);
Insert into CDR_RECORD
   (RECORDID, FROM_TN, TO_TN, CONNECT_TIME, ELAPSED_TIME, 
    HEADERID, ANSWERED, AUTHCODE)
 Values
   (320110, '13779601', '83770010', TO_DATE('05/24/2007 11:06:14', 'MM/DD/YYYY HH24:MI:SS'), 18000, 
    2071, 0, NULL);



And here is the procedure I've been working on. It has many things that are wrong (IF statements that will not work for example). I wrote them like that to keep in mind the logic I need to follow. I need to replace that with proper PL/SQL statements.

CREATE OR REPLACE PROCEDURE getCdrReport 
(
	pResults            OUT SYS_REFCURSOR,
	dDateFrom           IN 	DATE, 					-- Initial Date : Format DD/MM/YYYY
	dDateTo             IN 	DATE, 					-- End Date : Format DD/MM/YYYY
	iCentrexId          IN	NUMBER,				 	-- Centrex Group Id
	iGroupId            IN	NUMBER DEFAULT NULL,	-- ID of the Line Group to filter by
	bShowMissedCalls    IN	NUMBER DEFAULT NULL, 	-- Show or not the unanswered calls
	iLoggedUserType     IN	NUMBER DEFAULT NULL, 	-- Type of user requesting the report
	iLoggedUserId       IN	NUMBER DEFAULT NULL,	--  ID of user requesting the report
	iUserId             IN	NUMBER DEFAULT NULL,	-- ID of the user to filter by
	iLineId             IN	NUMBER DEFAULT NULL,	-- ID of line to filter by
	iCallType           IN	NUMBER DEFAULT NULL		-- Type of Call to filter by (incoming, outgoing, both).
)
AS
DECLARE
	/* Constants*/
    -- User Types
    UT_ADMIN       CONSTANT NUMBER := 0
    UT_SUBADMIN    CONSTANT NUMBER := 1
    UT_USER        CONSTANT NUMBER := 2

    -- Call Types
    CT_BOTH        CONSTANT NUMBER := 0
    CT_INCOMING    CONSTANT NUMBER := 1
    CT_OUTCOMING   CONSTANT NUMBER := 2
BEGIN	
	OPEN pResults FOR
	SELECT
		recordid, from_tn, to_tn,  TO_CHAR (connect_time, 'DD/MM/YYYY HH24:MI')  connect_time,
		elapsed_time, answered, from_extension, to_extension, fromname, authcode, toname, typefrom, typeto
	FROM
	(
		SELECT
			r.recordid, r.from_tn, r.to_tn, connect_time,
			r.elapsed_time, r.answered, l_from.extension from_extension,r.authcode,
			l_to.extension to_extension, u_from.name fromname,
			u_to.name toname, u_from.usertype typefrom,
			u_to.usertype typeto
		FROM cdr_record r
			/* FROM LINES*/
			LEFT JOIN srv_voip_lines l_from ON TO_CHAR(l_from.tn) = r.from_tn
			LEFT JOIN centrex_user_lines ul_from ON ul_from.tnid = l_from.tnid
			LEFT JOIN centrex_users u_from ON u_from.userid = ul_from.userid

			/* TO LINES*/
			LEFT JOIN srv_voip_lines l_to ON SUBSTR (TO_CHAR (l_to.tn), 2) LIKE '%' || SUBSTR (TO_CHAR (r.to_tn), -4) || '%'
			LEFT JOIN centrex_user_lines ul_to ON ul_to.tnid = l_to.tnid
			LEFT JOIN centrex_users u_to ON u_to.userid = ul_to.userid

			if cFilters.iGroupId is not null and cFilters.iGroupId > 0 then
				LEFT JOIN centrex_line_group lg ON (lg.tn = l_from.tn  OR  lg.tn = l_to.tn )
			end if;
			
		WHERE
			r.connect_time BETWEEN dDateFrom and dDateTo
			AND ((l_from.gcxid = iCentrexId) OR (l_to.gcxid = iCentrexId)
			
			-- START CONDITIONAL FILTERS --
			
			-- Missed Calls
			AND r.answered = NVL (bShowMissedCalls, r.answered)
			
			-- Top Level Group
			if cFilters.iLoggedUserType is not null and cFilters.iLoggedUserType = UT_ADMIN then
				AND ((l_from.gcxid = cFilters.iCentrexId) OR (l_to.gcxid = cFilters.iCentrexId))
			else
				AND ((ul_from.userid = cFilters.iLoggedUserId) OR (ul_to.userid = cFilters.iLoggedUserId))
			end if;

			-- User Filter
			AND ((ul_from.userid = NVL(iUserId, ul_from.userid)) OR (ul_to.userid = NVL(iUserId, ul_to.userid)))

			-- Line Filter
			AND ((l_from.tnid = NVL(iLineId, l_from.tnid) ) OR (l_to.tnid = NVL(iLineId, l_from.tnid)))

			-- Group Filter
			AND lg.groupid = NVL (iGroupId, lg.groupid)

			if iCallType is not null and iCallType != CT_BOTH then
				if iCallType = CT_INCOMING then
					AND l_to.tnid  IN (getFilteredLines())
				else if iCallType = CT_OUTCOMING then
					AND l_from.tnid  IN (getFilteredLines())
				end if;
			end if;
			-- END CONDITIONAL FILTERS --
	)
	WHERE
		(
			(typefrom = 0 OR typefrom IS NULL) AND (typeto = 0 OR typeto IS NULL)
		)
		AND
		connect_time > ( SELECT fechaalta FROM srv_centrex WHERE centrexid = iCentrexId )
END;
/
Re: Large query with multiple conditional joins [message #249699 is a reply to message #249587] Thu, 05 July 2007 10:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Frank wrote on Thu, 05 July 2007 00:13
Please consider Barbara's example as (I think) she meant it to be: a rough outlined example.
In your production version you should use bind variables instead of concatenating the variables into the query.
This will both enhance scalability and security (prevents sql injection)



I did intend it as a rough outlined example. Bind variables are always preferable, when you can use them. However, in this case, if dynamically building this query, he does not know in advance, how many variables there will be, so he can't know how many bind variables to provide. That is the main reason I was trying to steer him away from dynamic sql, if at all possible.
Re: Large query with multiple conditional joins [message #249705 is a reply to message #249652] Thu, 05 July 2007 10:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
In a stored procedure the AS takes the place of the DECLARE that you would use in an anonymous pl/sql block, so remove the DECLARE. You need a semicolon at the end of each line of your constant values and at the end of your select statement. You cannot have an if ... endif within a sql statement; you would need to use case or decode instead. You need to either form a standardized sql statement as in my first example or dynamically concatenate a string as a variable and use it, as in my second example. You cannot combine the two methods, as you seem to be trying to do. Those are the first few errors that jumped out at me. I did not debug the whole thing. I would lean strongly toward the first method, without dynamic sql. The only thing that might pose a problem would be your conditional join. However, as I said before, your syntax looks strange, which is why I need to see an example of what results you want, based on what parameters. I suspect that once the correct syntax is established, there will be no problem with leaving the correct join in the standardized select statement. All the rest of the filter conditions can probably be accomplished using nvl or case or decode. You need to look up the case syntax in the sql reference manual and see how to use it in a sql statement, instead of if ... endif.
Re: Large query with multiple conditional joins [message #249711 is a reply to message #249652] Thu, 05 July 2007 11:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
It is best if you add a little section at a time, test and debug until it compiles and executes correctly, then add another little section. When you add a whole bunch of stuff and then try to run it, it will be harder to figure out where the problems are. Below is what you have so far that is correct enough to compile and execute. You might start from there and add to it. Also, I forgot to mention that you cannot have blank lines in the middle of a select statement. You can use empty comment lines if you want the spacing for readability. You also need to check your parentheses. You must have the same number of left and right. I added a right parenthesis to the code below.



SCOTT@10gXE> CREATE OR REPLACE PROCEDURE getCdrReport
  2  (
  3  	     pResults		 OUT SYS_REFCURSOR,
  4  	     dDateFrom		 IN  DATE,				     -- Initial Date : Format DD/MM/YYYY
  5  	     dDateTo		 IN  DATE,				     -- End Date : Format DD/MM/YYYY
  6  	     iCentrexId 	 IN  NUMBER,				     -- Centrex Group Id
  7  	     iGroupId		 IN  NUMBER DEFAULT NULL,    -- ID of the Line Group to filter by
  8  	     bShowMissedCalls	 IN  NUMBER DEFAULT NULL,    -- Show or not the unanswered calls
  9  	     iLoggedUserType	 IN  NUMBER DEFAULT NULL,    -- Type of user requesting the report
 10  	     iLoggedUserId	 IN  NUMBER DEFAULT NULL,    --  ID of user requesting the report
 11  	     iUserId		 IN  NUMBER DEFAULT NULL,    -- ID of the user to filter by
 12  	     iLineId		 IN  NUMBER DEFAULT NULL,    -- ID of line to filter by
 13  	     iCallType		 IN  NUMBER DEFAULT NULL	     -- Type of Call to filter by (incoming, outgoing, both).
 14  )
 15  AS
 16  	 /* Constants*/
 17  	 -- User Types
 18  	 UT_ADMIN	CONSTANT NUMBER := 0;
 19  	 UT_SUBADMIN	CONSTANT NUMBER := 1;
 20  	 UT_USER	CONSTANT NUMBER := 2;
 21  
 22  	 -- Call Types
 23  	 CT_BOTH	CONSTANT NUMBER := 0;
 24  	 CT_INCOMING	CONSTANT NUMBER := 1;
 25  	 CT_OUTCOMING	CONSTANT NUMBER := 2;
 26  BEGIN
 27  	     OPEN pResults FOR
 28  	     SELECT
 29  		     recordid, from_tn, to_tn,	TO_CHAR (connect_time, 'DD/MM/YYYY HH24:MI')  connect_time,
 30  		     elapsed_time, answered, from_extension, to_extension, fromname, authcode, toname, typefrom, typeto
 31  	     FROM
 32  	     (
 33  		     SELECT
 34  			     r.recordid, r.from_tn, r.to_tn, connect_time,
 35  			     r.elapsed_time, r.answered, l_from.extension from_extension,r.authcode,
 36  			     l_to.extension to_extension, u_from.name fromname,
 37  			     u_to.name toname, u_from.usertype typefrom,
 38  			     u_to.usertype typeto
 39  		     FROM cdr_record r
 40  			     /* FROM LINES*/
 41  			     LEFT JOIN srv_voip_lines l_from ON TO_CHAR(l_from.tn) = r.from_tn
 42  			     LEFT JOIN centrex_user_lines ul_from ON ul_from.tnid = l_from.tnid
 43  			     LEFT JOIN centrex_users u_from ON u_from.userid = ul_from.userid
 44  			     /* TO LINES*/
 45  			     LEFT JOIN srv_voip_lines l_to ON SUBSTR (TO_CHAR (l_to.tn), 2) LIKE '%' || SUBSTR (TO_CHAR (r.to_tn), -4) || '%'
 46  			     LEFT JOIN centrex_user_lines ul_to ON ul_to.tnid = l_to.tnid
 47  			     LEFT JOIN centrex_users u_to ON u_to.userid = ul_to.userid
 48  	     WHERE
 49  			     r.connect_time BETWEEN dDateFrom and dDateTo
 50  			     AND ((l_from.gcxid = iCentrexId) OR (l_to.gcxid = iCentrexId))
 51  			     -- START CONDITIONAL FILTERS --
 52  			     -- Missed Calls
 53  			     AND r.answered = NVL (bShowMissedCalls, r.answered)
 54  			     -- User Filter
 55  			     AND ((ul_from.userid = NVL(iUserId, ul_from.userid)) OR (ul_to.userid = NVL(iUserId, ul_to.userid)))
 56  			     -- Line Filter
 57  			     AND ((l_from.tnid = NVL(iLineId, l_from.tnid) ) OR (l_to.tnid = NVL(iLineId, l_from.tnid)))
 58  	     )
 59  	     WHERE
 60  		     (
 61  			     (typefrom = 0 OR typefrom IS NULL) AND (typeto = 0 OR typeto IS NULL)
 62  		     );
 63  END getCdrReport;
 64  /

Procedure created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> VARIABLE g_results REFCURSOR
SCOTT@10gXE> SET AUTOPRINT ON
SCOTT@10gXE> SET LINESIZE 300
SCOTT@10gXE> EXEC getCdrReport (:g_results, SYSDATE-90, SYSDATE, 87)

PL/SQL procedure successfully completed.


  RECORDID FROM_TN              TO_TN                CONNECT_TIME     ELAPSED_TIME   ANSWERED FROM_ TO_EX FROMNAME                                           AUTHCODE       TONAME                                               TYPEFROM     TYPETO
---------- -------------------- -------------------- ---------------- ------------ ---------- ----- ----- -------------------------------------------------- -------------- -------------------------------------------------- ---------- ----------
    320108 13770010             0019011103535        24/05/2007 11:05         3000          1 0010        Carlos Benavidez                                                                                                              0

SCOTT@10gXE> 

Re: Large query with multiple conditional joins [message #249730 is a reply to message #249711] Thu, 05 July 2007 12:24 Go to previous message
elbarto
Messages: 11
Registered: July 2007
Location: Buenos Aires
Junior Member
Barbara, thanks for your help.
As I said I knew my procedure was wrong (and wouldn't compile), but I wrote it like that just to have a clear idea of the logic I needed to follow.

I will read about CASE and DECODE in the documentation to figure out how to do those more complicated conditions, and will also try to keep away from the dynamic SQL as performance is a priority here.

I'll do some research and then will post my failure or success.

Thanks
Previous Topic: Performance issue post parallelism change
Next Topic: delay calculation
Goto Forum:
  


Current Time: Thu Dec 08 02:05:14 CST 2016

Total time taken to generate the page: 0.11196 seconds