Post reply

ERROR: Subquery returned more than 1 value

  • May 28, 2010 at 3:39 am

    #141255

    Hi,

    I have the following named calculation in Data Source View (Sql Server 2008)

    (SELECT data FROM DimDate WHERE DimDate.data >= '2008-01-01')

    or

    (SELECT data FROM DimDate WHERE DimDate.data between '2008-01-01' and '2010-01-01')

    And when I try to deploy, it gives me the following error:

    OLE DB error: OLE DB or ODBC error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.; 21000.

    Can anyone please help here?

    Thanks in advance,

    Cafc.

  • May 28, 2010 at 4:06 am

    #1173809

    You are returning multiple rows for a single result value.

    try with a top 1 in your select statement and see if it works.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • May 28, 2010 at 4:25 am

    #1173816

    it doesn't work! Can you tell me please another any to implement such query, in order to filter this dates?

    Thanks,

    Cafc

  • May 28, 2010 at 8:22 am

    #1173944

    This should work

    (SELECT TOP 1 data FROM DimDate WHERE DimDate.data >= '2008-01-01')

    however, if you post the business problem you're trying to solve rather than simply a TSQL query we may be able to assist you in determining what might truly *work* for you versus what just "works" 🙂

    Steve.

  • May 28, 2010 at 3:57 pm

    #1174170

    Hi, thank you very much for your answer!

    Indeed I had already tested the query that you write! but it doesn't return what I want! select top 1... it returned always the same value (in case 2008-01-01) for all rows!

    why I want make such a filter in Data Source View?

    I want somehow filter the field 'data' (because it has a lot of rows, more than 30000 rows) and I do not want to see all of that rows when browsing. For instance, if I want make a Excel pivot table (connecting Excel 2007 to analysis services) the excel filters for this field (data) will display only the first 30000 rows ! or if we were in sharepoint (using excel services) the filters display only the first 1000 rows) ! So I thought of creating a named calculation in DSV in order filter the last data.

    I don't know if this is right way to my solve my problem.

    Thanks

    Cafc.

  • May 28, 2010 at 4:44 pm

    #1174181

    To do 'exactly' what you want, you need to use a Named Query rather than a calculation (or even use a view in the source DB). There, you'll use the WHERE clause as normal (e.g. SELECT blah , blahblah FROM BigTable WHERE myDate BETWEEN '2009-01-01' AND '2010-03-01')

    As i said, this will do exactly what you asked for, limit the rows. This may *not be* what your users want. You may want to try using hierarchies. Organizing your items into logical hierarchies can allow you user/s to filter or show items in groups rather than trying to pick from a huge list.

    Steve.

  • May 29, 2010 at 9:27 am

    #1174254

    Hi Steve!

    Yes, I agree with you! Its better using hierarchies, I forgot completly the hierarchies !!!! (I have some, but never using/need them, until now 🙂 ) .

    But, just for curiosity where I can find the "Named Query" to bulid that query? Under dsv? "Replace Table -> With New Named Query" ?

    But that will not replace the entire Table (DimDate) for the new query?

    Thank very much for your help! You help me a lot!!

    Best Regards,

    Cafc

  • May 29, 2010 at 2:41 pm

    #1174276

    It replaces the object/reference within the DSV, not the actual base view/table. Using a named query like

    SELECT fieldA, fieldB FROM TableA WHERE Field3 = 'ABC'

    is the equivalent of using a view with the same statement.

    Depending on who is going to be maintaining/managing the BI solution will usually dictate which location you make this change. for e.g. if you have a dedicated BI team and they aren't allowed to make changes to the source DB, then the DSV is the logical choice. If you're a BI consultant who is walking way after the project is done but the client has a dedicated DBA, then the view would make more sense as the DBA is likely to be more comfortable in that environment.

    Steve.

  • May 31, 2010 at 5:06 am

    #1174463

    hi steve!

    Ok! I didn't have the opportunity to try-it (to create a named query) but I will! but for the moment the hierarchies solve a lot my pbs!! 🙂

    Thank you very much again for your advice!

    Best Regards,

    Cafc

Viewing 9 posts - 1 through 8 (of 8 total)

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