Duplicate Records in output file [message #683729] |
Tue, 16 February 2021 16:45 |
|
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 |
|
Michel Cadot
Messages: 68665 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.
|
|
|
|
|
|