?
Solved

UNION ALL with no duplicate records

Posted on 2011-10-10
13
Medium Priority
?
300 Views
Last Modified: 2012-05-12
Hi experts i am trying to merge to sql query in one but with no duplicate record, for example if both sql querie return the record number 002 then i want only one record to be shown in the table, i used UNION ALL but its not working, so how can i get the required result

can you help me please

thank you
sql.txt
0
Comment
Question by:AZZA-KHAMEES
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36941974
use Union
0
 
LVL 25

Assisted Solution

by:TempDBA
TempDBA earned 668 total points
ID: 36941979
You should use union instead of union all... union all just adds everything whereas union only adds and give you the distinct values.
0
 
LVL 15

Assisted Solution

by:Eyal
Eyal earned 332 total points
ID: 36941982
Just use Union without the ALL
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 18

Assisted Solution

by:deighton
deighton earned 332 total points
ID: 36941984
try UNION rather than UNION ALL
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 668 total points
ID: 36941990
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.
0
 

Author Comment

by:AZZA-KHAMEES
ID: 36942005
Thank you all for your replies
i used UNION but it gave me the following error
The ntext data type cannot be selected as DISTINCT because it is not comparable.

i just want the two sql query to compared by the trk.ReferenceNo
0
 
LVL 25

Accepted Solution

by:
TempDBA earned 668 total points
ID: 36942014
or insert the data into a temporary table by casting it to varchar.

cast(the filed as varchar(max))

And then select distinct from the temporary table.
0
 

Author Comment

by:AZZA-KHAMEES
ID: 36942064
I dont know how to do this since i am not profissional in SQL query :-P, can you help me
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 668 total points
ID: 36942068
Try
cast(the filed as varchar(max))
within your query for example ir PER.EngDir is ntext data type then ,change likt this for your ntext field



SELECT    TASK.ID as CurrentTaskId,TRK.Details,trk.ReferenceNo, SER.ServiceNo + '-' + SER.ServiceName AS ServiceName,
SER.KPI, trk.InitiatorName AS InitiatorUID, PER.DirCode,PER2.SecCode, PER.EngSec,SER.DirCode as ServiceDirCode,
SER.SecCode as ServiceSecCode, WorkFlow.dbo.CurrentUserName(trk.InitiatorName) AS InitiatorName,
trk.RequestedDate AS RequestedDateString, CAST(trk.RequestedDate AS DATETIME) AS RequestedDate, trk.Status,
trk.WorkflowPID, trk.CurrentActorUID, WorkFlow.dbo.CurrentUserName(trk.CurrentActorUID) AS CurrentActorName, trk.ApprovedDate,
trk.DeliveredDate, 'Out' AS InOut,
cast(PER.EngDir as varchar(max)) EngDir
FROM PR1.dbo.GetUserDetailsE AS PER INNER JOIN
PR1.dbo.GetUserDetailsE AS PER2 ON PER.DirCode = PER2.DirCode AND PER.SecCode = PER2.SecCode INNER JOIN
WorkFlow.dbo.tbl_ServiceTracking AS TRK ON PER2.PERUserName = TRK.InitiatorName INNER JOIN
WorkFlow.dbo.tblService AS SER ON TRK.ServiceNo = SER.id   LEFT OUTER JOIN
AdobeLCES2.dbo.tb_task AS TASK ON TASK.process_instance_id=TRK.ReferenceNo AND TASK.status=3
WHERE     (PER.PERUserName = 'mfsbeab') AND CAST(TRK.RequestedDate AS DATETIME)>='09/01/2011'
AND CAST(TRK.RequestedDate AS DATETIME)<='01/01/2012'


UNION

SELECT     TASK.ID as CurrentTaskId,TRK.Details,trk.ReferenceNo, SER.ServiceNo + '-' + SER.ServiceName AS ServiceName, SER.KPI,
trk.InitiatorName AS InitiatorUID, PER.DirCode,
PER.SecCode, PER.EngSec,SER.DirCode as ServiceDirCode,SER.SecCode as ServiceSecCode,
WorkFlow.dbo.CurrentUserName(trk.InitiatorName) AS InitiatorName, trk.RequestedDate AS RequestedDateString,
CAST(trk.RequestedDate AS DATETIME) AS RequestedDate, trk.Status, trk.WorkflowPID, trk.CurrentActorUID,
WorkFlow.dbo.CurrentUserName(trk.CurrentActorUID) AS CurrentActorName, trk.ApprovedDate, trk.DeliveredDate, 'Out' AS InOut,
cast(PER.EngDir as varchar(max)) EngDir
FROM         PR1.dbo.GetUserDetailsE AS PER INNER JOIN
WorkFlow.dbo.tblService AS SER ON SER.DirCode = PER.DirCode INNER JOIN
WorkFlow.dbo.tbl_ServiceTracking AS trk ON trk.ServiceNo = SER.id   LEFT OUTER JOIN
AdobeLCES2.dbo.tb_task AS TASK ON TASK.process_instance_id=TRK.ReferenceNo AND TASK.status=3
WHERE     (PER.PERUserName = 'mfsbeab') AND CAST(TRK.RequestedDate AS DATETIME)>='09/01/2011'
AND CAST(TRK.RequestedDate AS DATETIME)<='01/01/2012'
ORDER BY ReferenceNo desc
0
 

Author Comment

by:AZZA-KHAMEES
ID: 36942078
Yeay, its worked, thank you alots for your help, i was really struggling with this huge query :-)

Thank you
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36942083
Try
cast(the filed as nvarchar(max))
within your query for example ir PER.EngDir is ntext data type then ,change likt this for your ntext field



SELECT    TASK.ID as CurrentTaskId,TRK.Details,trk.ReferenceNo, SER.ServiceNo + '-' + SER.ServiceName AS ServiceName,
SER.KPI, trk.InitiatorName AS InitiatorUID, PER.DirCode,PER2.SecCode, PER.EngSec,SER.DirCode as ServiceDirCode,
SER.SecCode as ServiceSecCode, WorkFlow.dbo.CurrentUserName(trk.InitiatorName) AS InitiatorName,
trk.RequestedDate AS RequestedDateString, CAST(trk.RequestedDate AS DATETIME) AS RequestedDate, trk.Status,
trk.WorkflowPID, trk.CurrentActorUID, WorkFlow.dbo.CurrentUserName(trk.CurrentActorUID) AS CurrentActorName, trk.ApprovedDate,
trk.DeliveredDate, 'Out' AS InOut,
cast(PER.EngDir as nvarchar(max)) EngDir
FROM PR1.dbo.GetUserDetailsE AS PER INNER JOIN
PR1.dbo.GetUserDetailsE AS PER2 ON PER.DirCode = PER2.DirCode AND PER.SecCode = PER2.SecCode INNER JOIN
WorkFlow.dbo.tbl_ServiceTracking AS TRK ON PER2.PERUserName = TRK.InitiatorName INNER JOIN
WorkFlow.dbo.tblService AS SER ON TRK.ServiceNo = SER.id   LEFT OUTER JOIN
AdobeLCES2.dbo.tb_task AS TASK ON TASK.process_instance_id=TRK.ReferenceNo AND TASK.status=3
WHERE     (PER.PERUserName = 'mfsbeab') AND CAST(TRK.RequestedDate AS DATETIME)>='09/01/2011'
AND CAST(TRK.RequestedDate AS DATETIME)<='01/01/2012'


UNION

SELECT     TASK.ID as CurrentTaskId,TRK.Details,trk.ReferenceNo, SER.ServiceNo + '-' + SER.ServiceName AS ServiceName, SER.KPI,
trk.InitiatorName AS InitiatorUID, PER.DirCode,
PER.SecCode, PER.EngSec,SER.DirCode as ServiceDirCode,SER.SecCode as ServiceSecCode,
WorkFlow.dbo.CurrentUserName(trk.InitiatorName) AS InitiatorName, trk.RequestedDate AS RequestedDateString,
CAST(trk.RequestedDate AS DATETIME) AS RequestedDate, trk.Status, trk.WorkflowPID, trk.CurrentActorUID,
WorkFlow.dbo.CurrentUserName(trk.CurrentActorUID) AS CurrentActorName, trk.ApprovedDate, trk.DeliveredDate, 'Out' AS InOut,
cast(PER.EngDir as nvarchar(max)) EngDir
FROM         PR1.dbo.GetUserDetailsE AS PER INNER JOIN
WorkFlow.dbo.tblService AS SER ON SER.DirCode = PER.DirCode INNER JOIN
WorkFlow.dbo.tbl_ServiceTracking AS trk ON trk.ServiceNo = SER.id   LEFT OUTER JOIN
AdobeLCES2.dbo.tb_task AS TASK ON TASK.process_instance_id=TRK.ReferenceNo AND TASK.status=3
WHERE     (PER.PERUserName = 'mfsbeab') AND CAST(TRK.RequestedDate AS DATETIME)>='09/01/2011'
AND CAST(TRK.RequestedDate AS DATETIME)<='01/01/2012'
ORDER BY ReferenceNo desc
0
 

Author Closing Comment

by:AZZA-KHAMEES
ID: 36942089
Thank you all for your help (^_^)b
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36942090
glad to help you :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question