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.
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)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'ASKER
I will get an error message of:
inconsistent datatypes: expected - got CLOB
inconsistent datatypes: expected - got CLOB
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
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
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I only used a couple fields and it stops working when I add the NULLs. The NULLS is what is creating the duplicates
ASKER
mlmcc: Yes you are correct.. So you mean linking in what way?
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
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
ASKER
Do you think I can do a distinct by writing a formula in Crystal?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Saweet!
Thanks!!
Thanks!!
UNION removes duplicates