Post reply

Subqueries are not allowed in this context. Only scalar expressions are allowed.

  • May 25, 2011 at 10:07 am

    #145604

    Hello everybody,

    I have ran into a problem.When running the following query into SQL2005 I get the following error:

    Subqueries are not allowed in this context. Only scalar expressions are allowed.

    INSERT INTO [{databaseOwner}].[{objectQualifier}User](BoardID,RankID,[Name],DisplayName,Password,Email,Joined,LastVisit,NumPosts,TimeZone,Flags,ProviderUserKey)

    VALUES(@BoardID,@RankID,@UserName,@DisplayName,'-',@Email,GETUTCDATE() ,GETUTCDATE() ,0, ISNULL((SELECT CAST(CAST(Value AS VARCHAR(5)) AS INT) from [{databaseOwner}].[{objectQualifier}Registry] where Name LIKE 'timezone' and BoardID = @BoardID), 0),@approvedFlag,@ProviderUserKey)

    Any idea what am I doing wrong?

    Thanks

  • May 25, 2011 at 10:12 am

    #1329799

    You just need to take the SELECT CAST out of the ISNULL expression.

    John

  • May 26, 2011 at 3:00 am

    #1330115

    Hi John,

    I replace this:

    VALUES(@BoardID,@RankID,@UserName,@DisplayName,'-',@Email,GETUTCDATE() ,GETUTCDATE() ,0, ISNULL((SELECT CAST(CAST(Value AS VARCHAR(5)) AS INT) from [{databaseOwner}].[{objectQualifier}Registry] where Name LIKE 'timezone' and BoardID = @BoardID), 0),@approvedFlag,@ProviderUserKey)

    with this:

    VALUES(@BoardID,@RankID,@UserName,@DisplayName,'-',@Email,GETUTCDATE() ,GETUTCDATE() ,0, ISNULL(CAST(Value AS VARCHAR(5)) AS INT) from [{databaseOwner}].[{objectQualifier}Registry] where Name LIKE 'timezone' and BoardID = @BoardID), 0),@approvedFlag,@ProviderUserKey)

    But it gives the following error:

    The name "Value" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

  • May 26, 2011 at 3:16 am

    #1330118

    I see what's happened here. You need to use the following construction:

    INSERT INTO MyTable (Col1, Col2, Col3)

    SELECT @var1, @var2, COALESCE(CAST(Value AS varchar(5)),'0')

    FROM MyOtherTable

    WHERE Name LIKE 'timezone'

    AND BoardID = @BoardID

    John

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply