Database

Mycroft Holmes14-Mar-10 17:46

14-Mar-10 17:46

So my 2nd post still applies. You cannot get the original data but have the current schema. I would create a sql database/table to take current customers data and just grab a substantial set of data to work with. I would then treat that as the source database. Work with that database to create your new data structures.

I am presuming there is 1 table coming from the source system with a humongous amount redundant data in it! If this is the case I usually write a script to move the source FILE into a data structure. I never do the transformation in the data transfer tool eg get the data into the database then clean it up.

Never underestimate the power of human stupidity
RAH

treefirmy14-Mar-10 18:00

treefirmy

14-Mar-10 18:00

Excellent.

How do I convince my PM that this is the way to go? What rationale should be used? (My PM has managed enterprise type data migration but only with SSIS/tools. I think some of the other developers managed the offshore team and either guided them to the scrub in code scheme or at least allowed it.)

Keep in mind this, scrub/translate in SQL Server, might be a very hard sell as I'm not experienced in writing SQL scripts, and have written C++ and C# code for 10+ yrs.

Thanks so much for the information.

p.s. There are a number of files which make up the ISAM database.

Mycroft Holmes14-Mar-10 18:15

14-Mar-10 18:15

treefirmy wrote:
scrub/translate in SQL Server, might be a very hard sell as I'm not experienced in writing SQL

My recommendation is based on my experience and I'm afraid I DO have extensive experience in SQL. Seriously, I would recommend getting in a consultant to do this job, it is not trivial and screwing it up will (and already has once) totally ruin any app built on it!

The main reason not to use SSIS for the transformation is my own lack of skill with the tool, I find it particularly difficult to audit a process that happens on the server in what is essentially a black box. Another is that we recently did some comparisons between using SSIS to transform the data and stored procs and the procs were 3 times faster than SSIS. When you process over 2k files per day it is important.

Never underestimate the power of human stupidity
RAH

treefirmy14-Mar-10 18:23

treefirmy

14-Mar-10 18:23

I know I'm asking a lot and you should be paid for this kind of info. So thanks again.

Don't get me wrong, if it were up to me I would more than follow your guidance. The only issue here is that I'm going to be a mouthpiece for your ideas and I don't have much standing in the company. I'm a new hire.

Given that, what concrete examples/reasons can I give my PM to convince him of the folly of the current approach and the correctness of the 'move data to SQL Server and then do translation into the new schema' strategy?

Calling Oracle Function via Linked Server returns error [modified]

Jörgen Andersson14-Mar-10 23:27

Jörgen Andersson

14-Mar-10 23:27

You've recieved some excellent info from Mycroft. And I agree with him fully.
I'd just like to add that memory considerations is a major reason to do the job on the database using SQL scripts.
A database is optimised for handling large sets of data.

"When did ignorance become a point of view" - Dilbert

Ph@ntom14-Mar-10 2:13

Ph@ntom

14-Mar-10 2:13

I am trying to call Oracle function via Linked Server, while executing it return the error. Can some one tell what is going wrong with this code, keep in mind that the parameter passed are of exactly same type defined in Oracle function except the date type parameter which I am passing as string because there is no equivalent.

-- returned error --

OLE DB provider "MSDAORA" for linked server "ERPUDEV" returned message "ORA-06550: line 1, column 18:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

-- calling part --
DECLARE @source_code VARCHAR(30)
DECLARE @source_lineid INT
DECLARE @feeder_itemcode VARCHAR(40)
DECLARE @sales_price INT
DECLARE @fdr_org_code VARCHAR(10)
DECLARE @fdr_subinv_code VARCHAR(10)
DECLARE @txn_qty INT
DECLARE @txn_date VARCHAR(30)
DECLARE @feeder_txntype VARCHAR(30)
DECLARE @fdr_subinvcode VARCHAR(10)
DECLARE @fdr_trnsubinvcode VARCHAR(10)
DECLARE @item_desc VARCHAR(10)

DECLARE @ErrMsg VARCHAR(100)
DECLARE @RetVal INT

SET @source_code = 'SSP-SALES'
SET @source_lineid = 8728
SET @txn_no = '15059'
SET @txn_no2 = '29193'
SET @feeder_itemcode = 'SSP1032'
SET @sales_price = 12
SET @fdr_org_code = 'S'
SET @fdr_subinvcode = 'S-SALES'
SET @txn_qty = 1
SET @txn_date = '13/MAR/2010'
SET @feeder_txntype = 'S-SALES_ISSUE'
SET @fdr_trnsubinvcode = ''

EXEC ('BEGIN ? := xx_eginv_f_dfs_erp_insert(?,?,?,?,?,?,?,?,?,?,?,?);END;', @RetVal,@source_code, @source_lineid, @txn_no, @feeder_itemcode, @sales_price,@fdr_org_code, @fdr_subinvcode, @txn_qty, @txn_date, @feeder_txntype, @fdr_trnsubinvcode,@ErrMsg OUTPUT) AT ERPUDEV

The Phantom.

modified on Sunday, March 14, 2010 8:19 AM

Re: Calling Oracle Function via Linked Server returns error

Mycroft Holmes14-Mar-10 14:55

Re: Calling Oracle Function via Linked Server returns error

14-Mar-10 14:55

So have you tried different date formats, I would try 'yyyy-mm-dd' as the second most unambiguous format, you have tried the first, Oracle may not like characters in the data and spits the dummy.

Note this is not a solution, just another avenue to try. I have not used Oracle in a decade or more.

Never underestimate the power of human stupidity
RAH

Ph@ntom14-Mar-10 19:19

Ph@ntom

14-Mar-10 19:19

Thanks for the comments.

A little more to understand the problem, I'v found that the error message was because of the returned type boolean (@RetVal).

-- oracle implementation
--======================
CREATE OR REPLACE FUNCTION TEST_ERP_INSERT (
P_SOURCE_CODE IN VARCHAR2
, P_SOURCE_LINE_ID IN NUMBER
, P_TRANSACTION_NUMBER IN VARCHAR2
, P_FEEDER_ITEM_CODE IN VARCHAR2
, P_SALES_PRICE IN NUMBER
, P_FEEDER_ORGANIZATION_CODE IN VARCHAR2
, P_FEEDER_SUBINVENTORY_CODE IN VARCHAR2
, P_TRANSACTION_QUANTITY IN NUMBER
, P_TRANSACTION_DATE IN VARCHAR2
, P_FEEDER_TRANSACTION_TYPE IN VARCHAR2
, P_FEEDER_TRANSFER_SUBINV_CODE IN VARCHAR2
, p_errmesg OUT VARCHAR2
)
RETURN BOOLEAN
IS
BEGIN

RETURN 1;
--
EXCEPTION
--
WHEN OTHERS
THEN
--
p_errmesg := 'Error:'
|| SQLERRM;
--
RETURN 0;
END;

-- calling from sql server
--========================
DECLARE @ErrMsg VARCHAR(1000)
DECLARE @RetVal SMALLINT
EXEC ('BEGIN ? := TEST_ERP_INSERT(?,?,?,?,?,?,?,?,?,?,?,?);END;', @RetVal,'SSP-SALES', 8728, '15059', 'SSP1032', 12,'S', 'S-SALES', 1, '13/MAR/2010', 'S-SALES_ISSUE', '', @ErrMsg OUTPUT) AT ERPUSTG;

Any idea? I can't change the returned type because its a different system.

The Phantom.

Sample datamodel for marketing promotion

anushh13-Mar-10 20:52

anushh

13-Mar-10 20:52

Sample datamodel for marketing promotion

Re: Sample datamodel for marketing promotion

Mycroft Holmes13-Mar-10 23:55

Re: Sample datamodel for marketing promotion

13-Mar-10 23:55

And your question is?

I will take mercy on you and supply this link [^]to a message on this page.

Never underestimate the power of human stupidity
RAH

Andy_L_J14-Mar-10 1:32

Andy_L_J

14-Mar-10 1:32

Look Here[^]

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

select before and after question

Eli Nurman13-Mar-10 8:44

13-Mar-10 8:44

Hi,

I Wonder if it possible to retrieve from an SQL server a recored plus its 5 recored before anf after.
for example: When I search a product with serial-number "100" i would like to retrieve 11 recorods from serial-number "095" until "105", of course after a "ORDER BY" statement of the serial-number field

Luc Pattyn13-Mar-10 9:08

Luc Pattyn

13-Mar-10 9:08

???

WHERE ID BETWEEN 95 AND 105

depending on the database the limit values will or will not be included (then change to 94 AND 106)

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.

Chris Meech13-Mar-10 9:10

Chris Meech

13-Mar-10 9:10

where serial-number between (100-5) and (100+5)
order by seriual-number

Does that do the trick? Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

Eli Nurman13-Mar-10 9:24

13-Mar-10 9:24

Thanks.
But this could be a good solution when querying a numeric field , my main problem is when querying a varchar field like product descriptions

Jörgen Andersson13-Mar-10 10:04

Jörgen Andersson

13-Mar-10 10:04

Assuming that your description field is unique:

SELECT  table.*
FROM    table,(
    SELECT  id
    FROM    table
    WHERE   description = 'whatever'
    ) temp
WHERE   table.id BETWEEN (temp.id - 5) AND (temp.id + 5)

"When did ignorance become a point of view" - Dilbert

Eli Nurman13-Mar-10 10:46

13-Mar-10 10:46

thanks.
this is an excelent solutions but when a few descriptions are 'whatever' is will appear a few times....

Not Active13-Mar-10 11:46

Not Active

13-Mar-10 11:46

Oh come on! Do you want someone to right it for you? You have more than enough to figure out the rest on your own.

I know the language. I've read a book. - _Madmatt

Eli Nurman13-Mar-10 12:09

13-Mar-10 12:09

ok, i am just climbing down the tree...

Mycroft Holmes13-Mar-10 20:27

need help in building this view, please Edited to add results of trying RIGHT OUTER JOIN

13-Mar-10 20:27

Wheres that spoon icon?

Never underestimate the power of human stupidity
RAH

Michael J. Eber12-Mar-10 10:39

Michael J. Eber

12-Mar-10 10:39

I have three primary tables:
CustomerMaster
ContactMaster
AddressList

Each customer master has a one to many relationship with contacts.
Each contact has a possible 1:1 relationship with the AddressList. (ie. null addresses ok)
Each address in the AddressList has a 1:1 relationship with CityEnumerators and StateEnumerators.

I'm trying to get a view working that shows all contacts that are active whose CustomerMaster record is active. Unfortunately I only get the contacts that have addresses and not the ones without addresses. I thought the following query would do it....but no go. (BTW also tried RIGHT OUTER JOIN with no luck also)

Any help would be appreciated as to what I'm doing that is screwing the pooch.

SELECT     TOP (100) PERCENT dbo.CustomerMaster.BSACustomerKey, dbo.ContactMaster.ContactName, dbo.ContactMaster.BusinessPhone, dbo.ContactMaster.MobilePhone, 
                      dbo.ContactMaster.Email, dbo.ContactMaster.FirstName, dbo.ContactMaster.LastName, dbo.ContactMaster.EmergencyMaintenance, 
                      dbo.ContactMaster.NormalMaintenance, dbo.ContactMaster.NetworkAbuse, dbo.ContactMaster.IssuedSwipeCard, dbo.ContactMaster.CardNumber, 
                      dbo.AddressList.CustomerAddressLine1, dbo.AddressList.CustomerAddressLine2, dbo.AddressList.CustomerAddressLine3, dbo.AddressList.CustomerPostBox, 
                      dbo.CityEnumerator.City, dbo.StateEnumerator.State, dbo.AddressList.CustomerCounty, dbo.AddressList.CustomerCountry, dbo.AddressList.CustomerZipcode, 
                      dbo.ContactMaster.BmcCustKey
FROM         dbo.ContactMaster INNER JOIN
                      dbo.CustomerMaster ON dbo.ContactMaster.CompanyID = dbo.CustomerMaster.CustomerIdentity LEFT OUTER JOIN
                      dbo.AddressList ON dbo.ContactMaster.ContactAddress = dbo.AddressList.AddressIdentity INNER JOIN
                      dbo.CityEnumerator ON dbo.AddressList.CustomerCity = dbo.CityEnumerator.CityIdentity INNER JOIN
                      dbo.StateEnumerator ON dbo.AddressList.CustomerStateorProvince = dbo.StateEnumerator.StateIdentity
WHERE     (dbo.CustomerMaster.CustomerStatus = 1) AND (dbo.ContactMaster.Status = 1)
ORDER BY dbo.CustomerMaster.BSACustomerKey

Re: need help in building this view, please Edited to add results of trying RIGHT OUTER JOIN

Mycroft Holmes12-Mar-10 16:52