locked
Error - Only one expression can be specified in the select list when the subquery is not introduced with EXISTS RRS feed

  • Question

  • User-1499457942 posted

    Hi

      I am getting above error on below query . I am using sql 2008 SSRS

    IF EXISTS 
            (SELECT 
                 TABLE_NAME 
             FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tblTemp') 
    DROP TABLE tblTemp
    CREATE TABLE tblTemp(
    	EntryNo int NOT NULL
    	,No varchar(20) NOT NULL
    	,DocumentNo varchar(20) NOT NULL
    );
    
    insert into tblTemp select (Select top(1) [Entry No_] FROM [test]  where [No_] = A.[No_]
    and [Document no_] = A.[Document no_]) as EntryNo , A.[No_],Isnull(A.[Document no_],'') FROM [Test] as A
    where A.[Date of Issue] between '2018/08/08' and '2018/08/08'
    group by A.[No_],A.[Document no_] 
    
    insert into tblTemp select 999999 , A.[No_],A.[DocumentNo_] from [test2] as A
    where exists (Select No from tblTemp as B where A.[No_] = B.No_)
    
    select A.EntryNo , A.No , A.DocumentNo 
    from tblTemp as A order by SrfNo , EntryNo

    Thanks

    Wednesday, October 24, 2018 5:10 AM

Answers

  • User77042963 posted

    CREATE TABLE tblTemp(
    EntryNo int NOT NULL
    ,No_ varchar(20) NOT NULL
    ,DocumentNo varchar(20) NOT NULL
    );

    insert into tblTemp (EntryNo , No_ , DocumentNo)
    select max([Entry No_]) as EntryNo
    , [No_],Isnull( [Document no_],'') FROM [Test]
    where [Date of Issue] between '2018/08/08' and '2018/08/08'
    group by [No_], [Document no_]

    insert into tblTemp (EntryNo , No_ , DocumentNo)
    select 999999 , A.[No_],A.[DocumentNo_] from [test2] as A
    where exists (Select No from tblTemp as B where A.[No_] = B.No_)

    select EntryNo , No_ , DocumentNo
    from tblTemp
    order by SrfNo , EntryNo

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 24, 2018 1:46 PM

All replies

  • User-595703101 posted

    I could not see an error, but had to correct typos possible related with column names.

    Please execute your query step by step by commenting last commands and executing from top with adding the one more following command

    Wednesday, October 24, 2018 7:25 AM
  • User77042963 posted

    CREATE TABLE tblTemp(
    EntryNo int NOT NULL
    ,No_ varchar(20) NOT NULL
    ,DocumentNo varchar(20) NOT NULL
    );

    insert into tblTemp (EntryNo , No_ , DocumentNo)
    select max([Entry No_]) as EntryNo
    , [No_],Isnull( [Document no_],'') FROM [Test]
    where [Date of Issue] between '2018/08/08' and '2018/08/08'
    group by [No_], [Document no_]

    insert into tblTemp (EntryNo , No_ , DocumentNo)
    select 999999 , A.[No_],A.[DocumentNo_] from [test2] as A
    where exists (Select No from tblTemp as B where A.[No_] = B.No_)

    select EntryNo , No_ , DocumentNo
    from tblTemp
    order by SrfNo , EntryNo

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 24, 2018 1:46 PM