possibly merge join bug?

i'm merge joining 2 data sources, one is oracle and the other is excel...the problem is in the oracle source, it's a sql statement like:

select hdr.div_ord_no, hdr.mtr_no, hdr.prod_cd
from qctrl_div_ord_header hdr,
(select max(sub.eff_dt_from) min_eff_dt_from, div_ord_no
from qctrl_div_ord_header sub
group by div_ord_no
) tmp
where hdr.eff_dt_from = tmp.min_eff_dt_from
and hdr.div_ord_no = tmp.div_ord_no

having that sql statement, merging will come out with 0 rows

however, having a simple query like:

select hdr.div_ord_no, hdr.mtr_no, hdr.prod_cd
from qctrl_div_ord_header hdr

merging will come out with 2 rows

you may think that the data in the first sql statement is not there for the merge, which causing the 0 rows, however, the data is there, i'm only joining by one column and definitely the data is there, the merge result should be 2 rows for both query statements

i believe this is a problem with SSIS, anyway around this?

Are the inputs to the MERGE JOIN sorted? I is a requirement that they are for MERGE JOIN to work correctly.

Note that setting IsSorted=Yes on the input does not mean that the data gets sorted for you!

-Jamie

|||yes, the input are sorted, everything should be setup correctly, hence, i got the merge to run and work as expected with the simple query

Possible to add columns to the right-hand side of a matrix?

Is it possible to add columns to the right-hand side of a matrix? Does anyone
have an example of joining a matrix and a table? Our report designs are never
as simple as the AdventureWorks examples. Currently, we use Actuate, which
allows for practically unlimited possibilities for report layout. We are
investigating migrating to Reporting Services, and we would appreciate any
tips/examples for achieving more complex layouts. Thanks!Not sure that I understand your question.
The Idea with the matrix is to dynammically add columns based on the group
you picked for the columns.
Maybe introduce some black"null" members in that group.
i.e if the dataset read something like
rowgroup1,columngroup1,measurefield1
a x1 10
b x2 20
c x3 30
d x4 40
try make it look like
rowgroup1,columngroup1,measurefield1
a x1 10
b x2 20
c x3 30
d x4 40
a Z NULL
b Z1 NULL
then on the report in the measure field ...iif (columngroup1='Z',"",.....)
marcell
We moved to ReportingServices with schemtisism, and have been waiting to
reach a stage where we hit a wall.
Still hasnt happened and I have developed some insanely complicated reports.
Flexibility is great!
"chatlineboy" <chatlineboy@.discussions.microsoft.com> wrote in message
news:79300D9D-0EFA-4047-B8B5-A231FBD68115@.microsoft.com...
> Is it possible to add columns to the right-hand side of a matrix? Does
> anyone
> have an example of joining a matrix and a table? Our report designs are
> never
> as simple as the AdventureWorks examples. Currently, we use Actuate, which
> allows for practically unlimited possibilities for report layout. We are
> investigating migrating to Reporting Services, and we would appreciate any
> tips/examples for achieving more complex layouts. Thanks!
>|||Thanks brickmouse! Yes, we had thought of that, also just introducing extra
groups with repeating values. We are continuing to evaluate Reporting
Services, but so far we are not convinced it is as flexible as Actuate.
"brickmouse" wrote:
> Not sure that I understand your question.
> The Idea with the matrix is to dynammically add columns based on the group
> you picked for the columns.
> Maybe introduce some black"null" members in that group.
> i.e if the dataset read something like
> rowgroup1,columngroup1,measurefield1
> a x1 10
> b x2 20
> c x3 30
> d x4 40
> try make it look like
> rowgroup1,columngroup1,measurefield1
> a x1 10
> b x2 20
> c x3 30
> d x4 40
> a Z NULL
> b Z1 NULL
> then on the report in the measure field ...iif (columngroup1='Z',"",.....)
>
> marcell
>
> We moved to ReportingServices with schemtisism, and have been waiting to
> reach a stage where we hit a wall.
> Still hasnt happened and I have developed some insanely complicated reports.
> Flexibility is great!
>
> "chatlineboy" <chatlineboy@.discussions.microsoft.com> wrote in message
> news:79300D9D-0EFA-4047-B8B5-A231FBD68115@.microsoft.com...
> > Is it possible to add columns to the right-hand side of a matrix? Does
> > anyone
> > have an example of joining a matrix and a table? Our report designs are
> > never
> > as simple as the AdventureWorks examples. Currently, we use Actuate, which
> > allows for practically unlimited possibilities for report layout. We are
> > investigating migrating to Reporting Services, and we would appreciate any
> > tips/examples for achieving more complex layouts. Thanks!
> >
>
>