SQL Generation Problem

rbuser

We are using Version 6.01 connected to DB2 and are having problems with a
query which has worked in the past.

The query created using the Query Wizard is producing incorrect results and
I think it is an internal issue.

When I copy the generated SQL from the SQL tab and run it in Database
Desktop it produces the correct result. When I use the query preview in
Report Builder, or run the report, the numbers are incorrect.

I haven't been able to pinpoint whether the problem is the way RB is doing
the outer joins, or the way the Calc Function is being processed, but I
would like to know whether there were any known issues with SQL generation
in version 6.01, and whether anyone else has experienced a similiar problem.

The Query listed below is copied from the SQL preview tab. When I run it in
any other tool it produces the correct results. Within RB the results
produce totals which are significantly lower than they should be.
Any help would be greatly appreciated.
Many Thanks,

Peter Collas
Australian Open Tennis

SELECT DISTINCT MEDIACAT.SUBCATEGORY,
MEDIACAT.DESCRIPTION,
Count(DISTINCT MEDIAVIEW.PersonID)
Count_DISTINCT_MEDIAVIEW,
Count(ConfDate) Count_ConfDate,
Count(DISTINCT PASSVIEW.PersonId) Count_DISTINCT_PASSVIEW_P
FROM MEDIAVIEW MEDIAVIEW
LEFT OUTER JOIN PASSVIEW PASSVIEW ON
(PASSVIEW.PERSONID = MEDIAVIEW.PERSONID)
RIGHT OUTER JOIN MEDIACAT MEDIACAT ON
(MEDIACAT.SUBCATEGORY = MEDIAVIEW.SUBCATEGORY)
AND (MEDIACAT.DESCRIPTION = MEDIAVIEW.DESCRIPTION)
WHERE (( PASSVIEW.APPROVED IS NULL )
OR ( PASSVIEW.APPROVED <> 'F' )) AND (
( PASSVIEW.PASSTYPE = 'Photo' )
OR ( PASSVIEW.PASSTYPE IS NULL ))
AND ( MEDIAVIEW."YEAR" = 2004 )
GROUP BY MEDIACAT.SUBCATEGORY,
MEDIACAT.DESCRIPTION
ORDER BY MEDIACAT.SUBCATEGORY,
MEDIACAT.DESCRIPTION

nardmoseley

I this query linked to a master query?

When a detail query is linked to a master, RB generates special linking SQL.
To view the linking SQL, position the mouse over the dataview tool window
and press Ctrl + left mouse. To view the query results of the linking SQL,
press Shft + left mouse.




--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Thanks Nard,

This helped a lot as it showed me what the problem is, but doesn't yet give
me a solution.

The Query is the Master query and has one detail query attached to it.

When I press Ctrl-left mouse and view the 'magic' SQL, what I see is that
all of my outer joins that were previously represented in the FROM clause
are now just standard joins in the WHERE clause.

Before :-
FROM MEDIAVIEW MEDIAVIEW
LEFT OUTER JOIN PASSVIEW PASSVIEW ON
(PASSVIEW.PERSONID = MEDIAVIEW.PERSONID)
RIGHT OUTER JOIN MEDIACAT MEDIACAT ON
(MEDIACAT.SUBCATEGORY = MEDIAVIEW.SUBCATEGORY)
AND (MEDIACAT.DESCRIPTION = MEDIAVIEW.DESCRIPTION)
WHERE (( PASSVIEW.APPROVED IS NULL )
OR ( PASSVIEW.APPROVED <> 'F' )) AND (
( PASSVIEW.PASSTYPE = 'Photo' )
OR ( PASSVIEW.PASSTYPE IS NULL ))
AND ( MEDIAVIEW."YEAR" = 2004 )

After :-
FROM MEDIAVIEW MEDIAVIEW, PASSVIEW PASSVIEW
, MEDIACAT MEDIACAT
WHERE (PASSVIEW.PERSONID = MEDIAVIEW.PERSONID)
AND
(MEDIACAT.SUBCATEGORY = MEDIAVIEW.SUBCATEGORY)
AND (MEDIACAT.DESCRIPTION = MEDIAVIEW.DESCRIPTION)
AND ((( PASSVIEW.APPROVED IS NULL )
OR ( PASSVIEW.APPROVED <> 'F' )) AND (
( PASSVIEW.PASSTYPE = 'Photo' )
OR ( PASSVIEW.PASSTYPE IS NULL ))
AND ( MEDIAVIEW."YEAR" = 2004 ))


As the query is gathering statistics, this makes a huge difference to the
accuracy of the result.

What puzzles me the most is that this is a three year old report which has
always worked perfectly with previous versions of RB. Has there been a
change in the rendering code, or are there other properties that I should be
checking.

Thanks again,
Peter


----- Original Message -----

nardmoseley

There are limitations when you link dataviews, particulary if you sort the
master data.

You may want to take another approach and build the queries using DADE, but
programmatically write the code to link them. This will avoid having any
MagicSQL generated. When you build the detail query, you just need to ensure
that you order the data properly. The detail data must be ordered by any
OrderBy fields in the master plus the linking fields(s).

Here is an example of programmatically linking DADE Queries::

http://www.digital-metaphors.com/tips/LinkDADEPipelines.zip


--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nard,

Thanks again, but I am still VERY puzzled.

This report has worked succesfully for three years and it is only with the
latest version of RB that we have a problem. The report was not changed,
and yet a change in RB version seems to be producing a different result from
the past.

Our core system contains over 450 reports and they are largely user-created
and stored in BLOB fields in a DB2 database.As you might imagine, creating
views or individual pipelines for each possible report is simply out of the
question.

One of the reasons we have been so thrilled with Report Builder is that we
haven't had to change source code or database structures to achieve great
reporting results.

Has something changed? Why would a report that has previously always
produced correct SQL2 syntax suddenly be ignoring the concept of outer
joins. Do you know if upgrading to V7 might help?

Thanks,
Peter

nardmoseley

Please create an example using RB and DBDemos data. Let me know under what
version it worked and what version you are using now. I can try it here....


--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Unfortunately the DBDemos does not have a structure which in anyway
replicates the nature of my problem. The full queries use expression syntax
that is only valid within a DB2 database. While these expressions are
correctly being passed to DB2, my problem is the way in which RB is
converting the outer join syntax into inner joins within the where clause.

The report has worked perfectly in all RB5 versions. I am not sure whether
we have missed a property setting in RB6 or whether changes to the
underlying SQL generation in the RB product have brought me undone. As this
report has been a key part of our security system for the Australian Open, I
am now under EXTREME pressure to explain why it has failed to work after
three years of success.

You can see quite clearly from the SQL below that RB is running code which
does not create a valid outer join despite the fact that the correct SQL is
shown in the preview tab of the data designer.

WHY DOES RB NOT GENERATE THE SAME CODE AS IS SHOWN IN THE CODE SQL PREVIEW
TAB????

This query has been placed stand-alone in a report without any
'master-child' relationship, and still exhibits the same problem. As you
can see the SQL being run by RB is NEVER going to produce the same result as
the SQL being shown in the preview window. This leaves our development team
in a position where they can no longer have faith that Report Builder is
going to produce the results they expect.

Code shown in PREVIEW tab and therefore the code we expect to be sent to our
database
'Photo' )
2004 )
Code shown in 'MAGICSQL' window, and code that is actually being sent to the
database

nardmoseley

Can you download a trial version of RB 7 and perform the same test. This may
be an issue with RB 6 that has been fixed.

I created a an outer join on customer / orders. I then view the SQL and the
MagicSQL and see that they are them same - both contain the outer join
syntax. I then create another query on Orders and link it to the first. The
MagicSQL for the detail dataview contains the outer joing as well.

Try the same test as I describe using DBDemos and try it using DB2.




--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com