Oracle SQL: UNION ALL stop duplicate values

Experts,

I am importing my data to crystal reports.  I have a query in Oracle 10g that is using a Union All, and is returning values twice.  The reason why I wrote a UNION ALL is because there are some fields that I need in one table and some that I need in the other.  I am pretty new at this stuff and this is what all I could come up with.  

I would like to modify the code to only have 1 Ticket ID value show up.

Here is my code and output. If there is a better way to write this I am all ears.


STATUS	TICKET_ID	CAB_APPROVED	ACTUAL_END_DATE_TIME
Closed	MNT-221051	Yes-National	1301392080
Closed	MNT-221051	Yes-National	(null)
Closed	MNT-221056	Yes-National	1301376960
Closed	MNT-221056	Yes-National	(null)
Closed	MNT-221058	Yes-National	1301380560
Closed	MNT-221058	Yes-National	(null)
Closed	MNT-221061	Yes-National	1301379300
Closed	MNT-221061	Yes-National	(null)
Closed	MNT-221062	Yes-National	1301382000
Closed	MNT-221062	Yes-National	(null)
Closed	MNT-221063	Yes-National	1301382600
Closed	MNT-221063	Yes-National	(null)

Open in new window

SELECT Ticket_ID,Status,CAB_APPROVED,UDF_CONVERT_UNIX_DATETIME(SCHEDULED_END_DATE_TIME, 'US/Eastern') AS SCHEDULED_END_DATE_TIME,UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern')AS SCHEDULED_END_DATE_TIME,COMPLETION_DETAIL,COMBINED_GROUP_NAME,COMPLETION_CODE,COMBINED_SERVICES,NULL as DA_IMPACT,NULL as DA_VIP_IMPACT,IMPACTED_DIVISIONS,MAINTENANCE_TYPE,STATUS,TICKET_ID,NULL as CALLED_END_NOC_INITIATED,NULL as CALLED_END_TIME,NULL as CALLED_START_NOC_INITIATED,NULL as CALLED_START_TIME,NULL as SC_DA_CELL,NULL as SC_DATA,NULL as SC_HFC,NULL as SC_INFRASTRUCTURE,NULL as SC_VIDEO,NULL as SC_VOICE,NULL as DA_T_MOBILE_IMPACT
FROM ARADMIN.RP_DAILY_MAINTENANCE_REPORT
		WHERE UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY)
			AND UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
            AND CAB_APPROVED = 'Yes-National'
            AND STATUS = 'Closed'
UNION ALL
SELECT Ticket_ID,Status,CAB_APPROVED,NULL,NULL,NULL,COMBINED_GROUP_NAME,COMPLETION_CODE,COMBINED_SERVICES,DA_IMPACT,DA_VIP_IMPACT,IMPACTED_DIVISIONS,MAINTENANCE_TYPE,STATUS,TICKET_ID,CALLED_END_NOC_INITIATED,CALLED_END_TIME,CALLED_START_NOC_INITIATED,CALLED_START_TIME,SC_DA_CELL,SC_DATA,SC_HFC,SC_INFRASTRUCTURE,SC_VIDEO,SC_VOICE,DA_T_MOBILE_IMPACT
FROM ARADMIN.MAINTENANCE
		WHERE UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY)
		AND UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
        AND CAB_APPROVED = 'Yes-National'        
        AND STATUS = 'Closed'

Open in new window

Maliki HassaniAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
use UNION instead of UNION ALL


UNION removes duplicates
Maliki HassaniAuthor Commented:
I will get an error message of:
inconsistent datatypes: expected - got CLOB
mlmccCommented:
Are they truly duplicate?
I don't think so because they are returning different fields thus different values

Duplicate means the value of each field in the 2 records are identical and in the same order.

Given these 2 records
1   2   3   4
1  2    3    4
They are identical

1   2   3   4
4   3   2   1
That set is not since the order is different

In your records you have fields set as NULL since there is no equivalent field in the table.  Thus the 2 records can not be duplicate.

WHy don't you link the tables since they have many common fields.  In that way you would get a single record for each ticket id.

mlmcc



Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
ah...

you can't do "DISTINCT" on a CLOB

and that's effectively what UNION is doing.

I'm afraid you won't be able to do this directly.  Can you generate a distinct list set of rows that does NOT include the CLOB columns? and then pull them separately?

It won't be efficient since you need to query the table twice, but you won't be able to sort/distinct as long as they are included
Maliki HassaniAuthor Commented:
I only used a couple fields and it stops working when I add the NULLs. The NULLS is what is creating the duplicates
Maliki HassaniAuthor Commented:
mlmcc:  Yes you are correct..  So you mean linking in what way?
sdstuberCommented:
Where are you duplicates?  Do you mean non-null columns are duplicated?
Your sample output doesn't include any duplicates.

Or do you mean you get duplicate rows where you have created the nulls?
if so,  do DISTINCT on the first query and then UNION ALL
Maliki HassaniAuthor Commented:
Do you think I can do a distinct by writing a formula in Crystal?
mlmccCommented:
Duplicate

Closed      MNT-221051      Yes-National      1301392080   -first query
Closed      MNT-221051      Yes-National      (null)               - second query


Something like

SELECT DMR.Ticket_ID, DMR.Status, DMR.CAB_APPROVED, UDF_CONVERT_UNIX_DATETIME(DMR.SCHEDULED_END_DATE_TIME, 'US/Eastern') AS SCHEDULED_END_DATE_TIME, UDF_CONVERT_UNIX_DATETIME(DMR.SCHEDULED_START_DATE_TIME, 'US/Eastern')AS SCHEDULED_END_DATE_TIME, DMR.COMPLETION_DETAIL, DMR.COMBINED_GROUP_NAME, DMR.COMPLETION_CODE, DMR.COMBINED_SERVICES, DMR.IMPACTED_DIVISIONS, DMR.MAINTENANCE_TYPE, DMR.STATUS,
MX.DA_IMPACT, MX.DA_VIP_IMPACT, MX.IMPACTED_DIVISIONS, MX.MAINTENANCE_TYPE, MX.STATUS, MX.CALLED_END_NOC_INITIATED, MX.CALLED_END_TIME, MX.CALLED_START_NOC_INITIATED, MX.CALLED_START_TIME, MX.SC_DA_CELL, MX.SC_DATA, MX.SC_HFC, MX.SC_INFRASTRUCTURE, MX.SC_VIDEO, MX.SC_VOICE, MX.DA_T_MOBILE_IMPACT
FROM ARADMIN.RP_DAILY_MAINTENANCE_REPORT DMR  INNER JOIN ARADMIN.MAINTENANCE MX
ON DMR.Ticket_ID = MX.Ticket_ID
WHERE ...

mlmcc


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Maliki HassaniAuthor Commented:
Saweet!  

Thanks!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.