1 Replies - 6609 Views - Last Post: 23 November 2010 - 07:13 AM

#1 Guest_Julie*


Reputation:

Finding a row count for my Query

Posted 23 November 2010 - 06:15 AM

How to find the row count of below query? Could you please help me out.

SELECT 'S' tsd_type -
      ,serv.wsm_new_service tsd_service_name -
      ,'N/A F' tsd_team_from -
      ,'N/A T' tsd_team_to -
      ,'N/A F' tsd_work_group_from -
      ,'N/A T' tsd_work_group_to -
      ,call.ser_id tsd_call_id -
      ,pri.cdl_name tsd_priority -
      ,ctxt.rct_name tsd_category -
      ,hsc.hsc_subject tsd_subject -
      ,hsc.hsc_valuefrom tsd_valuefrom -
      ,hsc.hsc_valueto tsd_valueto -
		,hsc.hsc_created tsd_created -
FROM  sddata.itsm_servicecalls call -
      INNER JOIN sddata.itsm_codes_locale pri ON (call.ser_pri_oid = pri.cdl_cod_oid) -
      INNER JOIN sddata.itsm_historylines_servicecall hsc ON (call.ser_oid = hsc.hsc_ser_oid) -
      INNER JOIN wsfpbr.w_wsfpbr_mapped_services_v serv ON (call.ser_srv_oid = serv.srv_oid) -
      INNER JOIN sddata.rep_codes_text ctxt ON (call.ser_cat_oid = ctxt.rct_rcd_oid) -
WHERE  NOT (hsc.hsc_valuefrom IS NULL AND hsc.hsc_valueto IS NULL) -
AND    hsc.hsc_created >= TO_DATE ('01/01/2009', 'DD/MM/YYYY') -
AND    (UPPER (hsc.hsc_subject) LIKE 'GROUP%' -
OR       UPPER (hsc.hsc_subject) LIKE 'STATUS%COMPLETED%' -
OR       UPPER (hsc.hsc_subject) LIKE 'STATUS%CLOSED%' -
OR       UPPER (hsc.hsc_subject) LIKE 'STATUS%TO%ASSIGNED%') -
AND    call.ser_oid IN -
       (SELECT hsc.hsc_ser_oid -
        FROM   sddata.itsm_historylines_servicecall hsc -
        WHERE  (hsc.hsc_valuefrom IN -
                (281487049886142,281478391857491,281478391857483,281478391857499,281478391857445,281489726771673,281489726771696,281489726771708) -
        OR      hsc.hsc_valueto IN -
                (281487049886142,281478391857491,281478391857483,281478391857499,281478391857445,281489726771673,281489726771696,281489726771708)) -
        AND    hsc.hsc_created >= TO_DATE ('01/01/2009', 'DD/MM/YYYY'))


This post has been edited by baavgai: 23 November 2010 - 06:50 AM
Reason for edit:: tagged


Is This A Good Question/Topic? 0

Replies To: Finding a row count for my Query

#2 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7183
  • View blog
  • Posts: 14,971
  • Joined: 16-October 07

Re: Finding a row count for my Query

Posted 23 November 2010 - 07:13 AM

What's with all the little dashes at the end of each line?

You should be able to just wrap it as a sub query.

e.g.
select count(*) from (
	SELECT 'S' tsd_type -
			,serv.wsm_new_service tsd_service_name -
			,'N/A F' tsd_team_from -
			,'N/A T' tsd_team_to -
			,'N/A F' tsd_work_group_from -
			,'N/A T' tsd_work_group_to -
			,call.ser_id tsd_call_id -
			,pri.cdl_name tsd_priority -
			,ctxt.rct_name tsd_category -
			,hsc.hsc_subject tsd_subject -
			,hsc.hsc_valuefrom tsd_valuefrom -
			,hsc.hsc_valueto tsd_valueto -
			,hsc.hsc_created tsd_created -
	FROM  sddata.itsm_servicecalls call -
			INNER JOIN sddata.itsm_codes_locale pri ON (call.ser_pri_oid = pri.cdl_cod_oid) -
			INNER JOIN sddata.itsm_historylines_servicecall hsc ON (call.ser_oid = hsc.hsc_ser_oid) -
			INNER JOIN wsfpbr.w_wsfpbr_mapped_services_v serv ON (call.ser_srv_oid = serv.srv_oid) -
			INNER JOIN sddata.rep_codes_text ctxt ON (call.ser_cat_oid = ctxt.rct_rcd_oid) -
	WHERE  NOT (hsc.hsc_valuefrom IS NULL AND hsc.hsc_valueto IS NULL) -
	AND    hsc.hsc_created >= TO_DATE ('01/01/2009', 'DD/MM/YYYY') -
	AND    (UPPER (hsc.hsc_subject) LIKE 'GROUP%' -
	OR       UPPER (hsc.hsc_subject) LIKE 'STATUS%COMPLETED%' -
	OR       UPPER (hsc.hsc_subject) LIKE 'STATUS%CLOSED%' -
	OR       UPPER (hsc.hsc_subject) LIKE 'STATUS%TO%ASSIGNED%') -
	AND    call.ser_oid IN -
			 (SELECT hsc.hsc_ser_oid -
			  FROM   sddata.itsm_historylines_servicecall hsc -
			  WHERE  (hsc.hsc_valuefrom IN -
						 (281487049886142,281478391857491,281478391857483,281478391857499,281478391857445,281489726771673,281489726771696,281489726771708) -
			  OR      hsc.hsc_valueto IN -
						 (281487049886142,281478391857491,281478391857483,281478391857499,281478391857445,281489726771673,281489726771696,281489726771708)) -
			  AND    hsc.hsc_created >= TO_DATE ('01/01/2009', 'DD/MM/YYYY'))
)



Now, to the code itself...

Let me just do a little formatting:
SELECT * -- I'm not going to worry about the fields
	FROM sddata.itsm_servicecalls call
		INNER JOIN sddata.itsm_codes_locale pri 
			ON call.ser_pri_oid = pri.cdl_cod_oid
		INNER JOIN sddata.itsm_historylines_servicecall hsc 
			ON call.ser_oid = hsc.hsc_ser_oid
		INNER JOIN wsfpbr.w_wsfpbr_mapped_services_v serv 
			ON call.ser_srv_oid = serv.srv_oid
		INNER JOIN sddata.rep_codes_text ctxt 
			ON call.ser_cat_oid = ctxt.rct_rcd_oid
	WHERE hsc.hsc_valuefrom IS NOT NULL
		AND hsc.hsc_valueto IS NOT NULL
		AND hsc.hsc_created >= TO_DATE ('01/01/2009', 'DD/MM/YYYY')
		AND (
			UPPER (hsc.hsc_subject) LIKE 'GROUP%'
				OR UPPER (hsc.hsc_subject) LIKE 'STATUS%COMPLETED%'
				OR UPPER (hsc.hsc_subject) LIKE 'STATUS%CLOSED%'
				OR UPPER (hsc.hsc_subject) LIKE 'STATUS%TO%ASSIGNED%'
			)
		AND call.ser_oid IN (
			SELECT hsc.hsc_ser_oid 
				FROM   sddata.itsm_historylines_servicecall hsc 
				WHERE  hsc.hsc_created >= TO_DATE ('01/01/2009', 'DD/MM/YYYY')
					AND (
						hsc.hsc_valuefrom IN (281487049886142,281478391857491,281478391857483,281478391857499,281478391857445,281489726771673,281489726771696,281489726771708)
							OR hsc.hsc_valueto IN (281487049886142,281478391857491,281478391857483,281478391857499,281478391857445,281489726771673,281489726771696,281489726771708)
						)
			)



I think you can shift this logic up into your joins. Also, IN is the most expensive SQL statement. If you can avoid using it for query results, you'll be better off.

Perhaps something like:
SELECT *
	FROM sddata.itsm_servicecalls call
		INNER JOIN sddata.itsm_codes_locale pri 
			ON call.ser_pri_oid = pri.cdl_cod_oid
		INNER JOIN sddata.itsm_historylines_servicecall hsc 
			ON call.ser_oid = hsc.hsc_ser_oid
				AND hsc.hsc_created >= TO_DATE ('01/01/2009', 'DD/MM/YYYY')
				AND hsc.hsc_valuefrom IS NOT NULL
				AND hsc.hsc_valueto IS NOT NULL
				AND (
					UPPER (hsc.hsc_subject) LIKE 'GROUP%'
						OR UPPER (hsc.hsc_subject) LIKE 'STATUS%COMPLETED%'
						OR UPPER (hsc.hsc_subject) LIKE 'STATUS%CLOSED%'
						OR UPPER (hsc.hsc_subject) LIKE 'STATUS%TO%ASSIGNED%'
					)
		INNER JOIN wsfpbr.w_wsfpbr_mapped_services_v serv 
			ON call.ser_srv_oid = serv.srv_oid
		INNER JOIN sddata.rep_codes_text ctxt 
			ON call.ser_cat_oid = ctxt.rct_rcd_oid
		INNER JOIN (
			SELECT hsc.hsc_ser_oid as ser_oid
				FROM   sddata.itsm_historylines_servicecall hsc 
				WHERE  hsc.hsc_created >= TO_DATE ('01/01/2009', 'DD/MM/YYYY')
					AND (
						hsc.hsc_valuefrom IN (281487049886142,281478391857491,281478391857483,281478391857499,281478391857445,281489726771673,281489726771696,281489726771708)
							OR hsc.hsc_valueto IN (281487049886142,281478391857491,281478391857483,281478391857499,281478391857445,281489726771673,281489726771696,281489726771708)
						)
			) call_oid
				ON call.ser_oid = call_oid.ser_oid



I don't like that the value IN are identical. If those were in a table somewhere, that would be good. The repetition of the date also nags me.

Perhaps:
SELECT *
	FROM sddata.itsm_servicecalls call
		INNER JOIN sddata.itsm_codes_locale pri 
			ON call.ser_pri_oid = pri.cdl_cod_oid
		INNER JOIN sddata.itsm_historylines_servicecall hsc 
			ON call.ser_oid = hsc.hsc_ser_oid
				AND hsc.hsc_valuefrom IS NOT NULL
				AND hsc.hsc_valueto IS NOT NULL
				AND (
					UPPER (hsc.hsc_subject) LIKE 'GROUP%'
						OR UPPER (hsc.hsc_subject) LIKE 'STATUS%COMPLETED%'
						OR UPPER (hsc.hsc_subject) LIKE 'STATUS%CLOSED%'
						OR UPPER (hsc.hsc_subject) LIKE 'STATUS%TO%ASSIGNED%'
					)
		INNER JOIN wsfpbr.w_wsfpbr_mapped_services_v serv 
			ON call.ser_srv_oid = serv.srv_oid
		INNER JOIN sddata.rep_codes_text ctxt 
			ON call.ser_cat_oid = ctxt.rct_rcd_oid
		INNER JOIN (
			SELECT hsc.hsc_ser_oid as ser_oid, hsc.hsc_created
				FROM   sddata.itsm_historylines_servicecall hsc 
				WHERE  hsc.hsc_valuefrom IN (281487049886142,281478391857491,281478391857483,281478391857499,281478391857445,281489726771673,281489726771696,281489726771708)
					OR hsc.hsc_valueto IN (281487049886142,281478391857491,281478391857483,281478391857499,281478391857445,281489726771673,281489726771696,281489726771708)
			) call_oid
				ON call.ser_oid = call_oid.ser_oid
		INNER JOIN (select TO_DATE ('01/01/2009', 'DD/MM/YYYY') as dt from dual) start_date
			ON hsc.hsc_created >= start_date
				AND call_oid.hsc_created >= start_date



Hope this help.

This post has been edited by baavgai: 23 November 2010 - 07:14 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1