[3TV] Error when running select

Owned by Alex Stibayev
Last updated: Nov 03, 2017 by Conor McCann

Problem

In some cases when trying to run  3ptv view. You might encounter an error as seen below: 

select * from [3PTV]

 

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

 

 

Solution

The issue occur due to duplicate entry for [User Count (DBA Users)],

  1. It can be identified by running

     

    select ApplicationID,Count(TableName ) as [Count] from OracleCounts 
where TableName ='All Users'
group by ApplicationID
having Count(TableName ) >1

  2. In ideal world the query result should be 0. However while the problem exists

    ApplicationIDCount
    3382

  3. You need to check the entry to verify to duplicate entry by running:

    select * from OracleCounts 
where TableName ='All Users'
 and ApplicationID =338
    OracleCountsIDApplicationIDTableNameCount
    1338All Users48
    3292338All Users48

  4. Line 1 and line Line 2 are duplicates. Hence by removing the older entry the issue can be resolved.

    delete from OracleCounts 
 where OracleCountsID =1