none
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Question

  • IF (
    select  COUNT(*) AS COUNT  from(select distinct SUBSTRING(LandRiverSegment,7,19) as riverseg,CBSegid from wsmpv24Reporting.dbo.TblLandRiverSegment
    ) as a
    group by riverseg
    having COUNT(*)>1)>1---check if more than Cbsegid are found ..if yes error if no update

    when i run this individually it is showing the count ,---2

    but when this part is kept in a Cursor it throws eror 
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    can any one please help me

    Thanks

    Wednesday, July 02, 2014 3:00 PM

Answers

  • see if this gets your along the right road:

    DECLARE @ID INT, @error VARCHAR(30)
    DECLARE @riverSeg TABLE (ID INT)
    INSERT INTO @riverSeg (ID)
    VALUES  (0),(1),(2),(4),(0),(1)
    
    DECLARE Countah CURSOR FOR
     SELECT ID FROM @riverSeg
    OPEN countah
     FETCH Countah INTO @ID
    WHILE @@FETCH_STATUS <> -1
    BEGIN
     IF(SELECT COUNT(*) FROM @riverSeg WHERE ID = @ID GROUP BY ID) > 1
     BEGIN
      SET @error = 'Opps, too many IDs for ID: '+convert(varchar,@ID)
      RAISERROR(@error,16,1)
     END
     ELSE 
     BEGIN
      PRINT 'Hooray, lets update ID: '+convert(varchar,@ID)
     END
     FETCH Countah INTO @ID
    END
    CLOSE Countah
    DEALLOCATE Countah
    

    Wednesday, July 02, 2014 3:53 PM

All replies

  • Sorry cannot test it right now..

    select  COUNT(*) AS COUNT , riverseg

    from(

    select distinct SUBSTRING(LandRiverSegment,7,19) as riverseg,CBSegid from wsmpv24Reporting.dbo.TblLandRiverSegment

    ) a der GROUP BY riverseg

    HAVING COUNT(*)>1

    IF @@ROWCOUNT>1

         blbalalalalala


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, July 02, 2014 3:09 PM
    Answerer
  • Thanks Uri,what i am trying to do is error out when count >1  ELSE  update if it less than 1

      after the IF statement 

    EX:

    select riverseg, COUNT(*) AS COUNT  from(select distinct SUBSTRING(LandRiverSegment,7,19) as riverseg,CBSegid from wsmpv24Reporting.dbo.TblLandRiverSegment
    ) as a
    group by riverseg
    having COUNT(*)>1---check if more than Cbsegid are found ..if yes error if no update
     

    Riverseg  Count

      abxcd       2

    if i see count more than once , then i will error out using raise error if not >1 then i will update ...

    My original code;

     IF(Select riverseg, COUNT(*) AS COUNT  from(select distinct SUBSTRING(LandRiverSegment,7,19) as riverseg,CBSegid from wsmpv24Reporting.dbo.TblLandRiverSegment
    ) as a
    group by riverseg
    having COUNT(*)>1)>1---check if more than Cbsegid are found ..if yes error if no update
     
     
     BEGIN
     
        PRINT  'UPDATE Failed'
        RAISERROR ('UPDATE Failed on TblLandRiverSegment, More than one CBSeigids found :Insert happened with Landriversgments and TblGeographyLrSeg, you should see NULL value in cbsegid column for this LrSegid , you need to update the cbsegid',16,1)  
        PRINT 'Update failed for LandRiverSegment = ' + coalesce(ltrim(@LandRiverSegment), 'NULL') + '.'

      END   
     
     ELSE
     
    BEGIN   
     
        UPDATE  T
        SET T.CbsegId =S.CbsegId
        FROM TblLandRiverSegment T
        JOIN  TblLandRiverSegment S
        ON S.RiverSegment = T.RiverSegment
        WHERE T.LrsegId =@newlrsegid
    END 

    but your scenario is checking the row count but not the count in count column.....

    can you help me

    Wednesday, July 02, 2014 3:21 PM
  • As the error message says, if you are using a subquery as an expression, and that subquery returns more than one row, you will get an error.  In your case the subquery is

    select  COUNT(*) AS COUNT  from(select distinct SUBSTRING(LandRiverSegment,7,19) as riverseg,CBSegid from wsmpv24Reporting.dbo.TblLandRiverSegment
    ) as a
    group by riverseg
    having COUNT(*)>1

    You can run that query by itself.  If it returns more than one row (which it will if you have more than one riverseq value which has more than one distinct CBSegid values, then running your whole query will generate this error.

    Tom

    Wednesday, July 02, 2014 3:35 PM
  • i am trying to capture the value in count, if i have value greater than 1 that means duplicate value exists then  i will have to error out

     EX:

    Riverseg  Count

    bxcd       2  ---error out

    EX:

     EX:

    Riverseg  Count

    ABd      1---then i will have to update

    Wednesday, July 02, 2014 3:47 PM
  • see if this gets your along the right road:

    DECLARE @ID INT, @error VARCHAR(30)
    DECLARE @riverSeg TABLE (ID INT)
    INSERT INTO @riverSeg (ID)
    VALUES  (0),(1),(2),(4),(0),(1)
    
    DECLARE Countah CURSOR FOR
     SELECT ID FROM @riverSeg
    OPEN countah
     FETCH Countah INTO @ID
    WHILE @@FETCH_STATUS <> -1
    BEGIN
     IF(SELECT COUNT(*) FROM @riverSeg WHERE ID = @ID GROUP BY ID) > 1
     BEGIN
      SET @error = 'Opps, too many IDs for ID: '+convert(varchar,@ID)
      RAISERROR(@error,16,1)
     END
     ELSE 
     BEGIN
      PRINT 'Hooray, lets update ID: '+convert(varchar,@ID)
     END
     FETCH Countah INTO @ID
    END
    CLOSE Countah
    DEALLOCATE Countah
    

    Wednesday, July 02, 2014 3:53 PM
  • Can you post more code (e.g. more context of what you need to do)?

    Do you need to perform different actions depending on do you have duplicates or not? If so, you may work with 2 separate queries - one for dups, another for unique and go through every row in returned result in the first case to generate error messages.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 02, 2014 5:03 PM
    Moderator