Home » SQL & PL/SQL » SQL & PL/SQL » Get Record Count (Oracle 11g)
Get Record Count [message #670936] |
Mon, 06 August 2018 01:14 |
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 |
cookiemonster
Messages: 13925 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 |
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 #670941 is a reply to message #670940] |
Mon, 06 August 2018 03:26 |
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 #670943 is a reply to message #670939] |
Mon, 06 August 2018 05:44 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
lokimisc wrote on Mon, 06 August 2018 08:59Thanks 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 |
pablolee
Messages: 2882 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 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
pablolee wrote on Tue, 07 August 2018 10:24would 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 |
pablolee
Messages: 2882 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
|
|
|
Goto Forum:
Current Time: Mon Jun 10 05:47:09 CDT 2024
|