DTS problem

[Tomadams]

I have a DTS problem that is driving me crazy. When I built an upload from BW oracle tables I did not consider one issue. We get data via EDI from Our customer. That data has information by model numbers that differ from our model number. I have built a cross refernece table with their erroneous model and our correct model and need to correct their model.



Here is the SQL statement that gets the BW data:

SELECT SUM("/BIC/ZQTYSLD") AS QTY, "/BIC/ZQTYSLD",

to_date(DATE0,'YYYYMMDD') as HD_Date,

MATERIAL,

VENDOR,

EANUPC,

SUM("/BIC/ZDLRAMT") AS AMT,

CURRENCY

FROM SAPBW2."/BIC/AZHMDPSLT00"

where "/BIC/ZQTYSLD" > 0

GROUP BY DATE0,MATERIAL, VENDOR, EANUPC,"/BIC/ZQTYSLD", "/BIC/ZDLRAMT",CURRENCY



The field of my concern is MATERIAL. It loads a table called SELL_THRU and the field MATERIAL is moved to the field Model_No

I have a simple cross reference table with two fields, NON-STANDARD and STANDARD.

If the field Model_No matches the field NON-STANDARD then I want to overwrite Model_No with STANDARD



I know it is simple, but I have tried and tried and can not get it to work. Any suggestions??

Thanks

 

[mrdenny]

Use a case statement to do the switch out.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Tomadams]

Thanks,
I know I tried that and can't remember what the error was that screwed me up. I'll try it again and get back to you.

 

[Tomadams]

How to I link the SQL table with the BW table?

 

[mrdenny]

Via a JOIN.

You will need to get the data all into one system first. Load the Oracle data into a staging table on the SQL Server and then do the join.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com