Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate Records in output file (Oracle, SQL Plus)
Duplicate Records in output file [message #683729] Tue, 16 February 2021 16:45 Go to next message
srai.bi
Messages: 11
Registered: February 2021
Junior Member
Hi All,
I am facing this issue where in I am doing a UNION of 2 views and the output spooled to a file shows duplicate records. Below is my code:

DEFINE CUR_PERIOD = &1
DEFINE OTBDIR = &2\
DEFINE Filename= FileName_
DEFINE FileExt= '.txt'

SET LINESIZE 40000
SET TRIMOUT ON
SET ECHO OFF
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET VERIFY OFF
SET PAGESIZE 0
SET TERMOUT OFF
SET TERM OFF
set feed off
set colsep |
SET HEADING OFF

column dt new_val file_pref

SELECT '&Filename&CUR_PERIOD&FileExt' AS DT FROM DUAL;

SPOOL &OTBDIR&file_pref

SELECT "Period"||'|'||"Responsibility Centres"||'|'||"Project Codes"||'|'||"Class Codes"||'|'||'BASE'||'|'||"Data" FROM
"ABC"."View1"
where "Period" ='&CUR_PERIOD'
UNION
SELECT "Period"||'|'||"Responsibility Centres"||'|'||"Project Codes"||'|'||"Class Codes"||'|'||'ADJUST'||'|'||"Data" FROM
"ABC"."View2"
where "Period" ='&CUR_PERIOD';

I have tried following:
1. UNION ALL was changed to UNION.
2. DISTINCT in each query.

Please help me.

Thanks,
Sid
Re: Duplicate Records in output file [message #683732 is a reply to message #683729] Wed, 17 February 2021 00:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Tue, 09 February 2021 13:09

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
...
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
The test case must be representative of your data and different cases you have to handle.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.


What is "duplicates" for you?
Your query can't return duplicates if you use DISTINCT on each query:
SQL> select dummy, 'BASE' from dual
  2  union all
  3  select dummy, 'ADJUST' from dual
  4  /
D 'BASE'
- ------
X BASE
X ADJUST

2 rows selected.
Re: Duplicate Records in output file [message #683761 is a reply to message #683732] Thu, 18 February 2021 20:58 Go to previous messageGo to next message
srai.bi
Messages: 11
Registered: February 2021
Junior Member
Hi Michael,

Thanks for the inputs. As I am new to the forum, I will start follownig the guideline.

The issue was resolved. Basically, I had to remove ";" at the end of the query.

SQL1
UNION
SQL2;

After removing ";" issue was resolved. Thanks.
Re: Duplicate Records in output file [message #683788 is a reply to message #683761] Mon, 22 February 2021 00:11 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Aha; so, the same query ran twice and "duplicated" the result. It wasn't a semi-colon that caused that, but slash that follows - it executes the last statement.

I hope you now see why it is important to post everything you did, exactly as you did it. There's no evidence in your initial message which supports what you've discovered:
Quote:

where "Period" ='&CUR_PERIOD';

I have tried following:
Apparently, SPOOL OFF is missing. Seems that slash is missing too.
Re: Duplicate Records in output file [message #683795 is a reply to message #683788] Mon, 22 February 2021 05:15 Go to previous message
srai.bi
Messages: 11
Registered: February 2021
Junior Member
Hi,
Agree with your comments. I will be careful from next time.

Cheers,
Sid
Previous Topic: convert char to number
Next Topic: A full outer join query
Goto Forum:
  


Current Time: Thu Mar 28 11:39:56 CDT 2024