+ Reply to Thread
Results 1 to 4 of 4

Excel Pivot Table shows zeros from a SQL View hitting an Oracle View via a Linked Server

  1. #1
    Registered User
    Join Date
    08-22-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2010
    Posts
    3

    Excel Pivot Table shows zeros from a SQL View hitting an Oracle View via a Linked Server

    I have a view in Oracle 11G. I want to show the contents in an Excel 2010 Pivot Table without installing the Oracle Client on my users' machines. So I created a SQL Server 2008 R2 view that queries the Oracle view through a Linked Server. I can run the view in Mgt. Studio. It looks good....I can see the text fields as well as the numeric fields. When I connect to this SQL Server view with the Pivot Table, the numbers disappear.....all zeros! The text fields all look good. I tried to connect with the regular SQL Server Native driver as well as with ODBC and Microsoft Query with the same result. If I connect the Pivot Table directly to Oracle, there is no issue. For whatever reason, this scenario is not yielding results in a Google search. I thought I would check here to see if anyone has seen this before.

    It is probably something that Excel should handle. It seems like a UniCode issue, but the source data-type is just Number. I thought there was also a possiblity that the problem could be in my SQL view code or linked-server settings. I included the SQL view code below. I spelled out the fields instead of using *, but it still has the problem.

    CREATE VIEW [dbo].[vwIMPACT_FEE_DATA]
    AS

    SELECT [Applicant]
    ,[Phone]
    ,[Address Line 1]
    ,[Address Line 2]
    ,[City]
    ,[State]
    ,[Zip Code]
    ,[Premise Address]
    ......
    ,[Meter Size]
    ,[Water Flow]
    ,[Water Supply]
    ,[Water System Dev]
    ,[WW Treatment]
    ,[WW Collection]
    ,[Service Line Total]
    FROM HANSEN..SAWS.VW_IMPACT_FEE_DATA

    GO

    Thank you for your help!

  2. #2
    Registered User
    Join Date
    08-22-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Excel Pivot Table shows zeros from a SQL View hitting an Oracle View via a Linked Serv

    I thought I would share the answer, although I am still left with questions.

    Someone on another forum suggested the following to get more insight into what is happening:

    select * into your_table from [dbo].[vwIMPACT_FEE_DATA]

    The table it created consisted of all nvarchar fields--no number fields.

    I also tried drilling into the pivot table to see the raw data. All of the number fields were left justified--an Excel indication of a text field.

    It appeared that SQL Server is unable to detect the Oracle data types from an Oracle view through the linked server. I added some casting to the SQL Server view to allow the Pivot table to see the data as Numbers:

    , CAST([Water Flow] AS Numeric) AS "Water Flow"

    I question why I would have to do this. Why can't SQL Server detect the data types in the Oracle view through the Linked Server?

  3. #3
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Excel Pivot Table shows zeros from a SQL View hitting an Oracle View via a Linked Serv

    Someone on another forum suggested the following
    That makes it sound like you have inadvertently broken one of the forum rules. If you have posted your problem at another forum please read the following:

    Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

  4. #4
    Registered User
    Join Date
    08-22-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Excel Pivot Table shows zeros from a SQL View hitting an Oracle View via a Linked Serv

    Thank you for your help, Cutter. I missed that. The site has been down several days so I am just now replying. This issue crossed several domains so initially it was unclear where the real problem lay. Someone at SQL Server Central (http://www.sqlservercentral.com/Foru....aspx?Update=1) suggested posting on an Excel site. That brought me here. They also suggested opening a case with Microsoft. That led me to the Microsoft Answers(http://answers.microsoft.com/en-us/o...2-91b6215d1d4c) and Microsoft Technet(http://social.technet.microsoft.com/...?prof=required). This ended up being a SQL Server issue, so I asked my final question on the SQL Server Forum(http://social.technet.microsoft.com/...6-1ae322ef0e61.
    As soon as I discovered a work-around (not the root-cause), I posted the answer in all locations. I am new to forums. I will honor this protocol in the future.

    Thanks, Jesse

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1