Problem solve Get help with specific problems with your technologies, process and projects.


I get the following message: "Server: Msg 8163, Level 16, State 3, Line 1: The text, ntext, or image data type cannot be selected as DISTINCT." How do I remove the duplicate rows?

I get the following message:

Server: Msg 8163, Level 16, State 3, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.

And I'm running the following query:

SELECT DISTINCT r.ReqName AS "Requirement Name", 
r.ReqDescription AS "Requirement Description",
r.ReqID AS "Requirement ID",
r.ReqReviewed AS "Requirement Reviewed",
r.ReqCoverageStatus AS "Requirement Coverage Status",
t.TestName AS "Test Name",
t.TestDescription AS "Test Decsription",
t.TestID AS "Test ID",
t.TestContainerName AS "Test Container Name",
r.ProjectName AS "Project Name",
t.TestCreator AS "Test Creator",
t.TestCreated AS "Test Created",
d.CurrentExecStatus AS "Current Execution Status",
t.TestTypeName AS "Test Type"
FROM RTM_V_Requirements r, 
RTM_V_ReqTests t, 
RTM_V_TestDefinitions d
WHERE r.ProjectName = 'New Hampshire Testing Project'
AND r.ProjectName = t.ProjectName 
AND r.ProjectName = d.ProjectName 

How do I remove the duplicate rows?

The simple answer is: you cannot, not like that. That error message is pretty explicit about what's wrong with your query. Apparently you've included at least one TEXT, NTEXT or IMAGE column in your SELECT. You cannot do that and use DISTINCT at the same time.

A closer look at your query suggests that you probably aren't getting true duplicates. You have not mentioned anything about the cardinality of your relationships. Does each requirement have multiple tests, or does a test have multiple requirements? What about test definitions, are they one-to-many with tests or with requirements? Or vice versa? It is next to impossible to tell just by looking at the query. Nevertheless, if the data is correctly joined, you should not be getting true duplicate rows.

What could be happening is that a single test or requirement or whatever is appearing in your result set multiple times, but this is different from entire rows being duplicated.

Some people get query results like this:

aaa 101
aaa 102
aaa 103
bbb 201
bbb 204

Then they want to "remove" the "duplicates" so that the results look like this:

aaa 101
bbb 201

However, those weren't true duplicate rows, and you should not attempt to suppress the repeating values with SQL. Handle this in your front-end application.

If this isn't an accurate understanding of your situation, then I'm sorry, I guess there wasn't enough information provided with your question.

Dig Deeper on Oracle and SQL