Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Mar 2004
    Posts
    84

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

    Hi, I was trying to run an insert statement which has subquery, But it is returning the error like this..
    Subqueries are not allowed in this context. Only scalar expressions are allowed.
    --------------
    Is there a way to reparse the insert statement without have to assign the subquery to a temp value and insert it?
    thanks..
    Here is my SQL..
    insert into admincriteria values (nextID,(select id from nodetable where description like 'Example - Quality Analytics'),8071,2,'Failures by Customer',2,0,0)
    Kishore

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    INSERT INTO admincriteria
       SELECT @nextID, id
    ,     8071, 2, 'Failures by Customer'
    ,     2, 0, 0
          FROM nodetable
          WHERE description = 'Example - Quality Analytics'
    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    84
    Quote Originally Posted by Pat Phelan
    Code:
    INSERT INTO admincriteria
       SELECT @nextID, id
    ,     8071, 2, 'Failures by Customer'
    ,     2, 0, 0
          FROM nodetable
          WHERE description = 'Example - Quality Analytics'
    -PatP
    Hi Pat, I am still confused, I am taking only ID field from nodetable with select statement. Rest is hard insert..
    Let me simplify and rephrase the insert statement.


    insert into admincriteria(text, id) values (sample,(select id from nodetable where description like 'Example - Quality Analytics'))
    Kishore

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    insert into admincriteria(text, id)
    select sample, id
    from nodetable
    where description like 'Example - Quality Analytics'

  5. #5
    Join Date
    Mar 2004
    Posts
    84
    Quote Originally Posted by Pat Phelan
    Code:
    INSERT INTO admincriteria
       SELECT @nextID, id
    ,     8071, 2, 'Failures by Customer'
    ,     2, 0, 0
          FROM nodetable
          WHERE description = 'Example - Quality Analytics'
    -PatP
    Thanks a lot guys...It worked...
    Kishore

  6. #6
    Join Date
    Mar 2004
    Posts
    84

    Another statement with 2 sub queries

    insert into adminmsatt values ((select id from nodetable where description like 'Example - Run Quality Analytics'),412,(select id from nodetable where description like 'Example - Quality Analytics'),0,null)

    Guys, can you please help on this one too?
    Thanks a lot in advance
    Kishore

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your best method of handling something like this is to declare variables for storing the values you want to insert:

    declare @id1 integer
    declare @id2 integer

    set @id1 = (select id from nodetable where description like 'Example - Run Quality Analytics')

    set @id2 = (select id from nodetable where description like 'Example - Quality Analytics')

    insert into adminmsatt values (@id1,412,@id2,0,null)

    Otherwise you'd have to run a cross-join or something even goofier to get this to fly as a single statement.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Couldn't you use:
    Code:
    INSERT INTO adminmsatt
       SELECT
          (select id from nodetable where description like 'Example - Run Quality Analytics')
    ,    412
    ,    (select id from nodetable where description like 'Example - Quality Analytics')
    ,     0, NULL
    -PatP

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or this....

    INSERT INTO adminmsatt
    select SubQ1.id, 412, SubQ2.id, 0, Null
    from
    (select 1 as Dummy, id from nodetable where description like 'Example - Run Quality Analytics') SubQ1
    inner join
    (select 1 as Dummy, id from nodetable where description like 'Example - Quality Analytics') SubQ2
    on SubQ1.Dummy = SubQ2.Dummy

    ...or this....

    INSERT INTO adminmsatt
    select nodetable1.id, 412, nodetable2.id, 0, Null
    from
    nodetable nodetable1, nodetable nodetable2
    where nodetable1.description like 'Example - Run Quality Analytics'
    and nodetable1.description like 'Example - Quality Analytics'

    ...but I still think the variable declare is preferable because it is simpler. And yes, I know that must sound odd coming from the guy who came up with this monstrosity:

    select Product, EXP(sum(log(abs(nullif(Value, 0))))) * (1+2*(cast(sum(sign(Value)-1)/2 as int) % 2)) * min(abs(sign(value)))
    from @PRODUCT
    group by Product

    The whole thing is probably moot anyway, since he's bound to run into trouble with that LIKE clause matching multiple records.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm just generally allergic to using variables, since that forces you to process one row at a time like a cursor does... It prevents you from being able to scale up, which is a bad thing in my book.

    You've got a good point about the use of LIKE, although since there are no wildcards in those patterns, the only way it could match multiple rows would be if you had two rows with the same value which would clobber an equality test too.

    -PatP

  11. #11
    Join Date
    Jul 2007
    Posts
    1
    I Have the next query but I don't understand the solution above.

    Code:
    INSERT INTO
      tbl_Tickets (
        vld_Onderwerp, 
        vld_Omschrijving, 
        vld_Aangemaakt, 
        vld_Type, 
        vld_Apparaat, 
        vld_Klant, 
        vld_Gebruiker
      ) 
      VALUES (
        'Test ', 
        'hoi', 
        '7/9/2007 1:41:15 PM', 
        (
          SELECT 
            fld_id 
          FROM 
            tbl_storing_types 
          WHERE 
            fld_Naam = 'Storing'
        ),
        '54684 ', 
        '1', 
        '1'
      )
    Who can tell me where i find the solution?

  12. #12
    Join Date
    May 2007
    Posts
    49
    Code:
    INSERT INTO
      tbl_Tickets (vld_Onderwerp, vld_Omschrijving, vld_Aangemaakt,  vld_Type,  vld_Apparaat, vld_Klant, vld_Gebruiker) 
      SELECT 'Test ', 'hoi', '7/9/2007 1:41:15 PM',  fld_id, '54684',  '1', '1'  
    	FROM 
    	tbl_storing_types 
      WHERE 
      fld_Naam = 'Storing'
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  13. #13
    Join Date
    Dec 2007
    Posts
    1
    By reading the above thread, I was not able to find the answer.
    This worked for me - I hope it will work for you:

    INSERT INTO tbl_Tickets (vld_Onderwerp, vld_Omschrijving, ..., ...)
    SELECT 'Test ', 'hoi', '7/9/2007 1:41:15 PM', T.fld_id, '54684', '1', '1'
    FROM tbl_storing_types T
    WHERE T.fld_Naam = 'Storing';


    The key is to NAME the table you do the sub-query on. I named mine "T". You can name it whatever you want. Then in the list of fields you're inserting, T.collumn_Name will be the field that you did the Select query on. Make sense?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, you do not have to give the table an alias, e.g. T

    works perfectly okay without it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    May 2008
    Posts
    4
    Hi all,

    I also have the same error, I am trying to do two things in my Stored Proc.

    1) - Insert a parent record.
    2) - Insert 1 or many records in the child table using the parent ID

    The child records are being passed as a XML param.

    I am also getting the
    'Subqueries are not allowed in this context. Only scalar expressions are allowed.'
    error when I try to create the procedure.

    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		pryder
    -- Create date: 
    -- Description:	
    -- =============================================
    CREATE PROCEDURE TestProc 
    	-- Add the parameters for the stored procedure here
    	@Name String ,
    	@bings XML 
    AS
    BEGIN transaction
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    declare @NewID as int
    declare @err as int
        -- Insert statements for procedure here
    	INSERT INTO PARENT
        (Name)
        VALUES (@Name)
        SELECT @err = @@error
        if @err <> 0 
    begin
    rollback transaction
    return @err
    end
    
    
    SELECT SCOPE_IDENTITY = @NewID
    
    
    INSERT INTO Child 
    (ParentID,
    name)
    
    Values
    
    ((
    SELECT @NewID, ParamValues.ID.value('.','VARCHAR(MAX)')
    FROM @bings.nodes('bings/group') as ParamValues(ID)
    ))  
    
    
    SELECT @err = @@error
    if @err <> 0  begin rollback transaction return @err end
    
    
    commit transaction
    return @@error
    GO

Posting Permissions

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