A Means To Create Field During DTS Execute

[MCuthill]

Hi Everyone,

I am not sure if this is the correct forum, but here goes.

Software Involved:
- Microsoft SQL Server 2000

Background:
- Currently utilizing 3 SQL Databases for 1 Crystal Report.
- Have to use Subreports (about 20) in order to link (via Formula Fields) all the Data together (there are not any fields that can be linked between DB's directly)

Question:
- Is there a way that I can run the Data Manipulation (formula's) while running my DTS packages? All the DTS's do is pull from a MS Access DB and place it in SQL.

The formulas I am currently using are simple tasks like removing the spaces in a insurance number. One Database has XXX XXX XXX and the other has XXXXXXXXX.

I am not 100% certain I can build fields to house all the "link" fields I need. At this point all I need to know is if it is possible and how difficult a task this would be as I am not overly familiar with SQL Server.

Thank You,



Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[AlexCuse]

Mike -

For a short answer, yes (it depends on what the formulas you speak of are doing, but my money is on yes)

I am pretty sure you can do what you are asking. If the 'formulas' you speak of are functions written in access, then it is quite simple to convert these to vbScript most of the time. It might be something you can handle using SQL Code too.

Can you give some more info, like :

1. What is your source query for DTS, or are you using the whole table?

2. What fields do you need to manipulate as you bring them in?

3. How are you currently performing these manipulations?

Good Luck,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[MCuthill]

Thanks For The Reply Alex,

To answer your questions:
1) I am running the whole table (for all DTS's in each of the 3 DB's)

2) This one I am not certain... I know one field for sure.
a) Database 1: {Customer.SocInsNum} (XXX XXX XXX)
Needs a new field of type XXXXXXXXX
b) Database 1: {Customer.NewField}
New Field Needed... based on query.

The second one I need to do is a little more involved and I will explain in #3 as how I am currently running it is what I need to replicate.

3) Case a:
I am using [blue]Replace[/blue] in a Formula Field in crystal reports to replace all instances of " " with "" (removing the spaces) and then linking the subreports on this field.
Case b:
I use the a "ToText" of DB 1: Customer ID {Customer.ID} as the link on a subreport that returns the unique Customer ID from DB 2.

FYI: The ID in Database 1 is know as a CIF # (Customer Information File Number) and therefore why it is linked to "CIFNumber" in DB2

Graphically:
ToText(DB1.Customer.ID) <-LINK-> DB2.Customer.CIFNumber
DB2.Customer.ID <--Value Returned

I am not sure if this makes any sense, please feel free for clarification on any of the above.

Thanks,


Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[AlexCuse]

It sounds like you want to use a query as the source for your data pumps. You can replace spaces within the SSN in this query, and probably handle whatever 'query based' column you need to create as well. Can you provide the select statement that would return everything from your table (column by column, not *) and indicate what transformations need to take place? This would make things very easy.

Good LUck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[MCuthill]

Alex,

I have been pondering the situation, I think I may have gotten ahead of myself. I thank you for you assistance, but I beleive it may not be worth the effort as I will still need subreports. As long as the subreports exist, I am not saving any processing time and therfore there is no real reason to make things more complex than they already are.

Thank You Again,
-Mike



If you are wondering the specifics as to why, here is my best attempt at an explanation:

I am already running a series of Subreports within the report only on info from DB1. Because I cannot think of a ficticious example, here is what I am working with.
The report is a Customer Profile report pulling off of Three different systems (hence 3 DB's). I work in the financial sector, and without subreports I would not get the info on a customer's account unless it had links to all the other info I was looking up, for example:
CUSTOMER->ACCOUNT->AccountNotes
I would not get any information returned (from the Account Table) unless the account had a note attached.
To make matters more complicated, there are about 8 different "items" that can exist at a level below the account (notes, restrictions, holds, etc etc etc) and therefore the account would not show unless it had ALL criteria.

By using subreports, I am able to address each of these "sublevels" by sending in the Customer # and get the details. (it isn't quite that simple, but that is the gist of it)

By attempting to bridge all three databases together I fear I am only making things worse as the customer would need to have details in all (30+) areas in order to return any information at all.

Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"