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 Update a table based on Excel?

Hi all
I was wondering if it was possible to update a table based off of information from Excel. here is what I though would have worked.

update MyTest Set acctNumber='111' FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\MyFile.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') where [ProductGroup]='Hal Butts'

with 'Update MyTest' being the table name. It does have the same name as the excel file. Just to rule that out.

It gives me this error

Ambiguous column name 'ProductGroup'.

If it is possible what is the correct syntax??

tiborupdate MyTest Set acctNumber='111'
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'
, 'Excel 8.0;Database=C:\MyFile.xls;HDR=YES'
, 'SELECT * FROM [Sheet1$]') WHERE [ProductGroup]='Hal Butts'Isn't complete. Your problem may be in the code that is missing from your post.

-PatP|||Well that is all the code I have.|||update mt
set mt.acctNumber = '111'
from MyTest mt
inner join OPENROWSET('Microsoft.Jet.OLEDB.4.0'
, 'Excel 8.0;Database=C:\MyFile.xls;HDR=YES'
, 'SELECT * FROM [Sheet1$]') ors ON mt.joincolumn = ors.joincolumn
WHERE mt.[ProductGroup]='Hal Butts'

The above isn't going to work, because I have no idea how you are trying to find the right columns to update based on the OPENROWSET. You need to figure it out though. You have to do something like what I have shown in the example, so you have an executable piece of code that makes sense.

Since ProductGroup is in both the MyTest table AND the spreadsheet, the UPDATE statement can't figure out which column you are referring to when you reference it.|||great thanks. its seems to be a bit long but works nicely. thank you so much.

tibor|||However....you would be better served by loading the excel file into a staging table and the perform your dml operations...excel is very unpredictable and is open to be modified by end users that could break you process...

If you do the load, only the load may fail, or if it does load, may fail a data audit the you would write...

I've come to hate excel|||Well i am still quite newbish to sql. how exactly would i load it to a staging table?|||Use defalt DTS import facility to transfer rows from excel to the table (create this table after assessing the columns on the excel sheet). Then use SELECT INTO to get the required data from staging to main table.

Possible to send parameter to Excel (via Web) to pas along through to SQL Server?

Is there anyway to pass a parameter to an Excel spreadsheet (loaded in a browser) to use as a parameter in SQL statement?

I know that the Microsoft Technologies can write Excel files and then load them, but I'm looking to take an existing workbook, parameterize the SQL statement and pass parameters via the Querystring/Form Post to it.

Why you may ask? For existing PivotTables/Charts that can be filtered BEFORE they are run.

Any/all thoughts appreciated.

BobYeah, there's a lot you can do. Check out this KB article for some ideas and code:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934|||What I'm looking to do specifically is to have a "template" of a workbook that I will pass connection/SQL Query information to, to be displayed on the fly.

Example:
A Pivot Table show results of: SELECT * FROM Products WHERE ProductID = <parameter
Web page passes a ProductID and the query is run (with the filter).

I'm assuming that I will have some sort of VBA code to hold the CORE query and I would like to be able to pass the parameter via ASP.

Any additional thoughts?

Thanx,
Bob|||Ok, I think I understand what you're trying to do. I believe this article shows you how (see the Generate XML from a Dataset for use in Excel 2003 section):

http://msdn.microsoft.com/office/understanding/excel/technicalarticles/default.aspx?pull=/library/en-us/odc_xl2003_ta/html/odc_xltransferxmltoxlwvb.asp

Possible bug with excel export

We're having a problem with the excel export in one of our reports. The data region in the report is filtered by the value of a parameter chosen by the user. Everything displays and works fine until it is exported to excel. When a user does this it outputs the results for the same parameter no matter what parameter is actually selected. It's specifically related to the excel export because every other one works as expected. Can anyone reproduce this problem?Are you running the latest service pack?
Can you try copying the report to another report & then exporting?|||This is actually in 2005. Copying the report to another one doesn't help and unfortunately we don't have our new test server in yet to try it on another machine.