Different Results With View Versus UDF

I have two views, for various reasons I decided to wrap a Select * From View with two UDFs. When run on their own, the UDFs return the exact same resultset as their respective view (did a compare and stuff to make sure). However, when I join them together, what was once 130,000 records (when joining the views) skyrockets into millions of records, and the resulting resultset is filled with duplicates. What is going on?Firstly I would use a stored procedure to return result sets, not UDF's. Secondly, it looks like you are getting a cartesian join, where every row in one table joins to many or every row in the other table. This is caused by incorrectly joining the two table (or views). This is illustrated by the duplicate rows you are getting. Check your joins, and make sure you join primary key to foreign key. To test the query, change the select list to a select count(*), and then run the query with just the first join condition. Then add each condition one by one. At some point the rowcount will explode. However, if the first join produces a massive row count, experiment with adding additional joins until you get the correct amount of records returned.

HTH

For more SQL tips, check out my blog:|||The exact same join with the exact same conditions using the views (which return an identical result set) returns the proper result.|||that's weird, you could have been executing a cross join|||You will have to post a repro script to demonstrate the problem. Otherwise it will be hard to guess what might be wrong. Your SELECT statement could be incorrect, the UDF could be wrong and so on.

Different results same query between original and copied db

Hi all,

I restored a backup of a database running SQL Server in W2K to my own laptop (Windows XP) for report testing pourposes. The restore worked perfectly, but when I ran the store procedure that returns my "report" set I noticed that several of the fields within the result set are different, the number of rows and customers are a perfect match to the production report. The fields that are different are calculated fields that invoque a user defined function, which again are exactly the same on both databases. I tried dropping the stored procedure and the 4 functions and recreating them again but I get the same results, the number of rows, the customers and all "non" function calculated fields are perfect, only the fields calculated with the functions are wrong.

Has anybody seen this behavior?

Thanks for your help

Luis Torresdo u have Nondeterministic Functions like rand() ..??|||nope, but I solved the problem, I didnt have the service pack 3 installed on my laptop and after installing it everything worked perfect.

Thanks for your help :)

Luis Torres|||would like to know wat SP3 did !!!|||Yeah, I would love to, I like the solution better when I understand the problem

Different reportparameters for same dimension in different cube

Hi,

I have 1 report with 2 charts, both charts have their own dataset. The two datasets are mdx queries on 2 different cubes, but some dimensions have the same name.

Now I want to have 2 differenent selectable parameters for the [dim time] dimension. One for the first query in the first cube and the second for the other query in the other cube .

So I check in the mdx query builder, both dimensions as parameter, but because both dimensions have the same name, i have only one selectable [dim time] -parameter in my report.

How can i solve this?

Thanks,

Dennis

Dear Dennis

Please help me to pass a parameter thru reportbulder to get drill down from report1 to jump into report2 . I created report1 and report 2. I want to jump into report 2 using parameter . I am getting one error when I run the report1 after giving drill throu in property page of the report in reportBulder

"Query Parameter missing " . Please help me

regards

Polachan

|||

Hi Dennis,

You can solve this by mapping a second parameter to the dataset.

Under Report > Report Parameters, Add a new parameter.

Create a new name and copy over the rest of the information from the parameter created by the MDX designer.

Within the properties of the second dataset (click the ellipses next to the DataSet name), modify the Parameters Value to point at the new parameter you just created.

That should give you two parameters, each mapped to the appropriate dataset.

HTH,

Jessica

different kind of Indexes for different tables

Hi ,
I read in Oracle that each table's can have it's own kind of indexes e.g IOT
, BitMap , B-Tree for different purposes used by Application.
Is there such function(s)/options in SQL Server ?
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200602/1
Indexes in MS-SQL Server are in B-Tree structure.
But you can fine tune them for your needs with some options (like the
fill factor). take a look at the "CREATE INDEX" in the BOL.
|||Hi ,
tks for ur advise
rdgs
amit.tzafrir@.gmail.com wrote:
>Indexes in MS-SQL Server are in B-Tree structure.
>But you can fine tune them for your needs with some options (like the
>fill factor). take a look at the "CREATE INDEX" in the BOL.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200602/1

different kind of Indexes for different tables

Hi ,
I read in Oracle that each table's can have it's own kind of indexes e.g IOT
, BitMap , B-Tree for different purposes used by Application.
Is there such function(s)/options in SQL Server ?
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200602/1Indexes in MS-SQL Server are in B-Tree structure.
But you can fine tune them for your needs with some options (like the
fill factor). take a look at the "CREATE INDEX" in the BOL.|||Hi ,
tks for ur advise
rdgs
amit.tzafrir@.gmail.com wrote:
>Indexes in MS-SQL Server are in B-Tree structure.
>But you can fine tune them for your needs with some options (like the
>fill factor). take a look at the "CREATE INDEX" in the BOL.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200602/1

different kind of Indexes for different tables

Hi ,
I read in Oracle that each table's can have it's own kind of indexes e.g IOT
, BitMap , B-Tree for different purposes used by Application.
Is there such function(s)/options in SQL Server ?
appreciate ur advise
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1Indexes in MS-SQL Server are in B-Tree structure.
But you can fine tune them for your needs with some options (like the
fill factor). take a look at the "CREATE INDEX" in the BOL.|||Hi ,
tks for ur advise
rdgs
amit.tzafrir@.gmail.com wrote:
>Indexes in MS-SQL Server are in B-Tree structure.
>But you can fine tune them for your needs with some options (like the
>fill factor). take a look at the "CREATE INDEX" in the BOL.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1

Different behavior between 2 installs of SQL Server 2000 !

Hi,
anyone could explain this : I have installed the same asp.net application on 2 distinct hosts, each with its own SQL Server 2000 database.
However, in some SQL queries in Stored Procedures, the results do not come out the same.
For example, on one server the query "select @.value = (select ...)-(select ...)" works and on the other one it returns a null value
Any hint ?
Thanks for your help
Johannoff the top of my head - does one have a different setting for, say, case-sensitivity?
|||

Well, I don't know about this since I am using distant hosting services.

However, the Stored Procedures are identical since I did an automatic install of both databases using one SQL script.

So ...