Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: Parameter input through a subquery

    Can someone please help me out. I am trying to create a Microsoft SQL query for an excel report that gets its parameter through a subquery. I keep getting the following error however 'Parameters not allowed in queries that can't be displayed graphically.'

    Below is my SQL query that has been simplified. Can someone please show me what I am doing wrong.

    select * from report where column_A is in
    (select column_A from
    from report
    where (column_B = ?) and (column_C = 1))


    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    select distinct foo.* 
      from report as foo
    inner
      join report as bar
        on bar.column_A = foo.column_A 
       and bar.column_B = ?
       and bar.column_C = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2005
    Posts
    91
    Thanks for the try but I still get the same error 'Parameters not allowed in queries that can't be displayed graphically.'

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The query above is regular SQL and works fine on standard-compliant systems. So you may want to ask your question in a SQL Server specific group - not in a group for SQL questions.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change the first AND to WHERE and try it again

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Looking at your original post I would say it looks like you are trying to do a select of a select (which is possible in MySQL, however i'm not so sure for MSSQL).
    My solution would be :
    Code:
    SELECT * FROM
      (
       SELECT column_A
       FROM report
       WHERE (column_B = ?) 
       AND (column_C = 1)
      )
    However I would say what's the different between that and this :
    Code:
    SELECT column_A
    FROM report
    WHERE (column_B = ?) 
    AND (column_C = 1)
    I think i'm missing something here, because it looks to me like you're getting the same data twice just by joining the table to itself on column_A???

Posting Permissions

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