Home » SQL & PL/SQL » SQL & PL/SQL » Get Record Count (Oracle 11g)
Get Record Count [message #670936] Mon, 06 August 2018 01:14 Go to next message
lokimisc
Messages: 101
Registered: February 2008
Senior Member
Hi,
I'm looking for effectively writing SQL query which will fetch record count and records from source table into target table. Also details are logged into logging table.

Requirement :
I want to get source table record count without executing query 2 times i.e one time for reading data and inserting into target table and next time execute same query and assign count to variable which is later used to insert into logging table because in real time my query is complicated having 6 to 10 tables joins with lakh of records.

Can you please suggest better way of getting source table record count.

SET SERVEROUTPUT ON;
DECLARE
v_count number;
v_src varchar2 := 'EMP_TMP';
v_scnt number := 0;
v_trgt varchar2 := 'EMP_TEST';
v_tcnt number := 0;

BEGIN

INSERT INTO emp_test(empno, ename, job, mgr, hiredate, sal, comm, deptno)
WITH etmp AS
( 	SELECT 	empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) over (partition by deptno)
	FROM 	emp_tmp
			INNER JOIN DEPT ON DEPT.DEPTNO = emp_tmp.deptno
	WHERE 	empno in (7935, 7936, 3243, 4032)
)
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM etmp;

--- Need to avoid below assignment of record count to variable
--- SELECT COUNT(*) INTO v_scnt FROM etmp;

v_tcnt := SQL%ROWCOUNT;

-- Insert source and target tables and its record counts into log table.

INSERT INTO logging (log_id, source_table, source_count, target_table, target_count)
VALUES (log_seq.nextval, v_src, v_scnt, v_trgt, vtcnt);

COMMIT;

END;

Thanks and Regards,
Lokesh
Re: Get Record Count [message #670938 is a reply to message #670936] Mon, 06 August 2018 02:50 Go to previous messageGo to next message
cookiemonster
Messages: 13199
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is source count supposed to be the count of all records in source table or just the records that are being inserted into target?
If it's all records then you're going to have to run a seperate select - there's no way round it.
If it's the records being inserted then sql%rowcount is what you need.
Re: Get Record Count [message #670939 is a reply to message #670938] Mon, 06 August 2018 02:59 Go to previous messageGo to next message
lokimisc
Messages: 101
Registered: February 2008
Senior Member
Thanks for your response.

Source count refers to resultant count of source tables ( 2 or more tables joined together ).
Is there anyway (workaround) where I can assign source count variable at the time of executing query which insert data into target table.

Regards,
Lokesh
Re: Get Record Count [message #670940 is a reply to message #670939] Mon, 06 August 2018 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 65661
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is this not what you do with:
v_tcnt := SQL%ROWCOUNT;
Re: Get Record Count [message #670941 is a reply to message #670940] Mon, 06 August 2018 03:26 Go to previous messageGo to next message
lokimisc
Messages: 101
Registered: February 2008
Senior Member
Thanks Michel for your response.

v_tcnt variable is getting assigned after execution of insert statement but I'm looking for assigning source variable count.

some thing like below

INSERT ALL   
  WHEN 1=1 THEN 
    INTO tbl_dtls ( tbl_id , src_tbl, src_count, trgt_tbl, trgt_count)
    VALUES (1, 'emp_tmp', 2, 'emp_test', 2)
  WHEN empno = 7935 then
  INTO emp_test(empno, ename, job, mgr, hiredate, sal, comm, deptno)
  VALUES (empno, ename, job, mgr, hiredate, sal, comm, deptno)
WITH etmp AS
( 	SELECT 	empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) over (partition by empno)
	FROM 	emp_tmp
			INNER JOIN DEPT ON DEPT.DEPTNO = emp_tmp.deptno
	WHERE 	empno in (7935, 7936, 3243, 4032)
)
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM etmp;

I don't want to insert logging table at the time of inserting record into target table.

Is there anyway I can assign count(*) over (partition by empno) value to some variable.

Please suggest.
Re: Get Record Count [message #670942 is a reply to message #670941] Mon, 06 August 2018 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 65661
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't understand you.
You say:
Quote:
I don't want to insert logging table at the time of inserting record into target table.
but your INSERT ALL statement intends to precisely do this, doesn't it?

Or do you show us codes that don't do what you want expecting we then know what you want to do?

Explain with plain words what you want to get as result, don't ask how to do something you want to write.
We need specification (and "Is there anyway I can assign count(*) over (partition by empno) value to some variable" is not a specification).

Re: Get Record Count [message #670943 is a reply to message #670939] Mon, 06 August 2018 05:44 Go to previous messageGo to next message
cookiemonster
Messages: 13199
Registered: September 2008
Location: Rainy Manchester
Senior Member
lokimisc wrote on Mon, 06 August 2018 08:59
Thanks for your response.

Source count refers to resultant count of source tables ( 2 or more tables joined together ).
Is there anyway (workaround) where I can assign source count variable at the time of executing query which insert data into target table.

Regards,
Lokesh
The number of tables involved isn't really relevant, unless you want a separate count for each source table.

Is it the data being inserted you want to count?
Or is it all the data in the source tables including data not being inserted?
Re: Get Record Count [message #670950 is a reply to message #670943] Mon, 06 August 2018 23:54 Go to previous messageGo to next message
pablolee
Messages: 2854
Registered: May 2007
Location: Scotland
Senior Member
Maybe I'm missing something but:
SELECT COUNT(*) INTO v_scnt FROM etmp;
would be invalid as etmp is a CTE and not valid to be referenced outwith the query in which it it being used. If the 'source variable count' (very unclear as to what that is at the moment) is (and I'm making a leap here) is the count of rows returned by
SELECT 	empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) over (partition by deptno)
	FROM 	emp_tmp
			INNER JOIN DEPT ON DEPT.DEPTNO = emp_tmp.deptno
	WHERE 	empno in (7935, 7936, 3243, 4032)
Then v_tcnt is equivalent to v_scnt and SQL%ROWCOUNT is what you are looking for.
If the v_scnt is a count of the rows in emp_test then you MUST count those rows (select count(*) into v_scnt...).

If neither of those scenarios meets your requirements, then you will need to supply a test case (sample tables, sample data and expected results.)
Re: Get Record Count [message #670994 is a reply to message #670950] Wed, 08 August 2018 12:46 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3131
Registered: May 2013
Location: World Wide on the Web
Senior Member
pablolee wrote on Tue, 07 August 2018 10:24
would be invalid as etmp is a CTE
It is commented out, perhaps OP used it initially in the SELECT from the CTE and later modified the column list.
Re: Get Record Count [message #671002 is a reply to message #670994] Thu, 09 August 2018 04:19 Go to previous message
pablolee
Messages: 2854
Registered: May 2007
Location: Scotland
Senior Member
Yes, I know that it's commented out. Does that in some way invalidate the point that I made? Bear in mind that the op commented it out because
" Need to avoid below assignment of record count to variable"
NOT because it was semantically invalid.

[Updated on: Thu, 09 August 2018 04:20]

Report message to a moderator

Previous Topic: Back fill records
Next Topic: PL/SQL Collection
Goto Forum:
  


Current Time: Wed Aug 15 23:09:57 CDT 2018