Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2008
    Posts
    3

    Question Unanswered: How do I imbed a select inside a select

    I need a select that gets a value and than appends another value if the criteria is met otherwise nothing is appended.

    The statement has a select with an imbedded select and when I execute it I get the error:
    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

    Thia is a crude sample of the statement

    SELECT ID + ( select * from tableB where TableB = 0 ) as result1
    FROM TableB

    Why am I getting this error and how do I fix the statement?
    thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @count int
    SET @count = (SELECT Count(*) FROM tableB WHERE field1 = 0)
    
    SELECT id + @count As [result1]
    FROM TableB
    ??
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A crude answer would be... No, nevermind... That's too easy.

    An approximate answer would be use LEFT JOIN to get what you want.

    For an exact answer, please post the actual code instead of somthing that is kinda close to it.

    -PatP

  4. #4
    Join Date
    Apr 2008
    Posts
    3
    Here is the statement

    SET @count = (SELECT Count(*) FROM TableT WHERE [TimeS = 0)
    SELECT str(Project) + str(@count) As [result1]
    FROM TableT
    That produces output
    1 0
    ...

    which is OK except I need a * chatacter in place of the 0

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    look at replace function.

  6. #6
    Join Date
    Apr 2008
    Posts
    3
    The problem here is the value is any number > 0 and replace works with only one value

  7. #7
    Join Date
    Nov 2002
    Posts
    272
    Why do you want the count if you're going to replace it with a * regardless of its value?

    SELECT str(Project) + '*' As [result1]
    FROM TableT

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The question is unclear. Provide details of your tables including sample data and your desired result.
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2002
    Posts
    272
    Oh good. I thought I was the only one who doesn't understand what dbfrumsuser wants.

Posting Permissions

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