Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Subquery returned more than 1 value

    I am onto another migration query, I dont understand where I have gone wrong in this code below when running Subquery's

    I have this error message and dont understand where its erroring

    How can I rectify this?

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


    Code:
    USE thejanitorDEV
    SET ANSI_PADDING  OFF 
    DECLARE @address1 VARCHAR(200)
    DECLARE @addressChunk VARCHAR(50)
    DECLARE @ID INT
    DECLARE @uprn varchar(100)
    DECLARE @client_id INT
    DECLARE @City VARCHAR(255)
    DECLARE @district VARCHAR(255)
    DECLARE @postcode VARCHAR(255)
    DECLARE @tenanttitle VARCHAR(255)
    DECLARE @tenantforename VARCHAR(255)
    DECLARE @tenantsurnae VARCHAR(255)
    DECLARE @tenantprimary VARCHAR(255)
    DECLARE @tenentsecondary VARCHAR(255)
    DECLARE @tenantemail VARCHAR(255)
    DECLARE @notes varchar(8000)
    DECLARE @fueltype VARCHAR(255)
    DECLARE @risk VARCHAR(255)
    DECLARE @added DATETIME
    DECLARE @tag VARCHAR(255)
    DECLARE address1 CURSOR FOR
    SELECT id, uprn,
    ISNULL((SELECT id 
    FROM thejanitorDEV.dbo.contracts 
    WHERE id = (SELECT JanitorID 
    FROM JanSiRPSMigration.dbo.tblMigrationLookup
    WHERE SiRPSID = A.client_ID 
    AND SirRPSTableName = 'client')),25),
    LTRIM(ISNULL(property_name_flat,'')+ ' '+ISNULL(property_flat_number,'')+' '+REPLACE([property_address_1],' ',' ')),
    property_city, property_district, property_postcode, tenant_title, tenant_forename, tenant_surname, tenant_primary, tenant_secondary, 
    ISNULL((SELECT [fuel_type] 
    FROM thejanitorDEV.dbo.quicklist_fuel_types 
    WHERE id = (SELECT JanitorID 
    FROM JanSiRPSMigration.dbo.tblMigrationLookup 
    WHERE SiRPSID = A.Fuel_Type 
    AND SirRPSTableName = 'fuel_type')),
    (SELECT [fuel_type] 
    FROM thejanitorDEV.dbo.quicklist_fuel_types 
    WHERE id = A.Fuel_type)), 
    ISNULL(risk,''),CONVERT(SMALLDATETIME,added), tag
    FROM [navitas].[dbo].[address] A
    WHERE ID 
    NOT IN (SELECT DISTINCT SiRPSID 
    FROM JanSiRPSMigration.dbo.tblMigrationLookup
    WHERE SirRPSTableName = 'address')
    AND Active = 1
    ORDER BY client_id
    OPEN address1
    	FETCH NEXT FROM address1 
    	INTO @ID, @uprn, @client_id, @address1, @City, @district, @postcode, @tenanttitle, @tenantforename, @tenantsurnae, 
    	@tenantprimary, @tenentsecondary, @fueltype, @risk, @added, @tag
    --	SET IDENTITY_INSERT thejanitorDEV.dbo.property ON
    	WHILE @@FETCH_STATUS = 0
    	BEGIN
    	DECLARE chunk CURSOR FOR
    	SELECT * FROM [JanSiRPSMigration].[dbo].[UTILfn_Split] (@address1 ,' ')
    DECLARE @Flat VARCHAR(255)
    DECLARE @FlatFinal VARCHAR(255)
    DECLARE @address VARCHAR(255)
    DECLARE @Number INT 
    SET @flat = ''
    SET @address = ''
    SET @Number = NULL
    		OPEN chunk
    		FETCH NEXT FROM chunk 
    		INTO @addressChunk
    --		SET IDENTITY_INSERT thejanitorDEV.dbo.property ON
    		WHILE @@FETCH_STATUS = 0
    		BEGIN
    		IF (@flat <> '')
    		BEGIN
    			Set @flatFinal = @flat + ' ' + @addressChunk
    				SET @Flat = ''
    				FETCH NEXT FROM chunk 
    				INTO @addressChunk
    		END
    		IF (@addressChunk = 'FLAT')
    		BEGIN
    			SET @flat = @addressChunk
    		END
    		ELSE
    			BEGIN
    				IF((SELECT [JanSiRPSMigration].[dbo].[IsInteger] (@addressChunk)) = 1 AND @number IS NULL)
    				BEGIN
    					SET @Number = CONVERT(INT,@addressChunk)
    				END
    				ELSE
    					BEGIN
    						if (@address is null)
    							BEGIN
    							SET @address = @addressChunk 
    							END
    						ELSE
    							BEGIN
    								SET @address = @address  + ' ' + @addressChunk
    							END
    					END
    			END
    				FETCH NEXT FROM chunk 
    				INTO @addressChunk
    	END
    					SELECT @notes = notes
    					FROM navitas.dbo.address
    					WHERE uprn = @uprn
    					BEGIN TRY
    					INSERT INTO thejanitorDEV.[dbo].[property] ([prop_seql], [prop_ref], [contract_id], [status], [number], [suffix], [address_1], 
    					[district], [city], [postcode], [tenant], [phone], [address_received], [address_comments], [fuel_type], [tag])
    					VALUES (@ID, @uprn, @client_id, '1', @number, @flatFinal, @address, @district, @City, @postcode, 
    					@tenanttitle + ' ' + @tenantforename + ' ' + @tenantsurnae, @tenantprimary + '-' + @tenentsecondary, @added, 
    					@notes + ' ' + @risk, @fueltype, @tag )
    					END TRY
    					BEGIN CATCH
    					PRINT 'ERROR '+ @uprn
    					END CATCH
    					IF @@ERROR <> 0 
    							BEGIN
    								INSERT INTO [JanSiRPSMigration].[dbo].[tblErrors] ([ErrorDetails])
    								VALUES ('error occured when inserting property. ~ SiRPS ID:'+ CONVERT(VARCHAR,@ID))
    							END
    						ELSE
    							BEGIN
    					DECLARE @OID INT
    					SET @OID = SCOPE_IDENTITY()
    					EXECUTE [JanSiRPSMigration].[dbo].[AddDataTolookup] @ID,@OID,'address'
    					PRINT 'Inserted SiRPS address: ' + CONVERT(varchar,@uprn) + ' at Position: ' + CONVERT(varchar,@OID)
    							END
    			SET @flat = ''
    			SET @address = ''
    			SET @FlatFinal = ''
    			SET @Number = NULL
    		CLOSE chunk
    		DEALLOCATE chunk
    	FETCH NEXT FROM address1 
    	INTO @ID, @uprn, @client_id, @address1, @City, @district, @postcode, @tenanttitle, @tenantforename, @tenantsurnae, @tenantprimary, 
    		@tenentsecondary, @fueltype, @risk, @added, @tag 
    	END
    CLOSE address1
    DEALLOCATE address1
    --SET IDENTITY_INSERT thejanitorDEV.dbo.property OFF
    --SET IDENTITY_INSERT thejanitorDEV.dbo.property OFF

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't put subqueries in your SELECT clauses.
    JOIN them as virtual tables in your FROM clause instead.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Why not use them in Subqueries?

    How would I join them as virtual tables?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Thanks for that, Ive been through a few example on the link

    Why not use Subqueries in my SELECT Statements though?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •