Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
karuna
Aged Yak Warrior
582 Posts |
 Posted - 2005-04-29 : 00:12:39
|
This is a activex script I use during import of data to a staging table. I have to get a full productid based on other inputs.(The client is not giving full productid for some strange reasons. )If(left(DTSSource("ProductId"),1)) = "P" Then ProdId = DTSSource("ProductId")&"-X"&DTSSource("PrefCustomerNumber") If(DTSSource("Prod Desc") = "Silver Support") Then ProdId = ProdId&"1" Else If (DTSSource("ProdDesc") = "Gold Support") Then ProdId = ProdId&"2" End IfEnd IfIf ( IsNull ( DTSSource("Support Start Date")) OR IsNull(DTSSource("Support End Date"))) ThenProdId = "" Else ProdId = ProdId&(Datediff("m",DTSSource("Support Start Date"),DTSSource("Support End Date"))) End IfElse ProdId = DTSSource("ProductId")End IfDTSDestination("ProductId") = ProdIdThe complete productid will look something like this.PABC3432-XAB124 what I get is PABC3432. The -X will be a constant one. The AB part(this will differ) will come from this column "PrefCustomerNumber". The next character (1 or 2 ) depends on the "prod desc" and the last 2 characters is the duration of the support.Either I have to do this manipulation during my import or after import to staging table I have to do these manipulations in the sql server. Which one is better? If T-Sql is a better route, How to achieve it.Karunakaran___________It's better to be loved and lost, than ever to be loved... |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-04-29 : 05:32:50
|
| I always like to keep the import as an image of the data so you know where things are going wrong and where to change if the source changes.Does that script work? seems to be losing the prodid if no support dates. And the indentation gets lost - there's an esle if in the middle of the first clause.Something like prodid = case when productid = 'P' then ProductId + '-X' + PrefCustomerNumber+ case when [Prod Desc] = 'Silver Support' then '1' else '' end+ case when [Prod Desc] = 'Gold Support' then '2' else '' end+ case when [Support Start Date] is null or [Support End Date] is null then ''else convert(varchar(2),Datediff('mm',[Support Start Date],[Support End Date"]))end+ ....==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-04-29 : 06:01:13
|
| The script is working fine. What I'm trying is if there is any other way of doing the same in the sql server so that I dont need to do this manipulation in a ActiveX Script during Import.The ProdId can start with any characters and my interest is in ProdId's which starts with P If does start with P then I append -X followed by 1 or 2 based on support level and the duration based on start and end date.nr,I'm still trying to understand your Sql Code. I'll try that out and see how it goes.Karunakaran___________It's better to be loved and lost, than ever to be loved... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-04-29 : 06:44:06
|
| Just seems odd that your script starts to buildthe prodid then loses all that work if there's no support dates.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-04-29 : 09:35:13
|
quote:
Originally posted by nr
Just seems odd that your script starts to buildthe prodid then loses all that work if there's no support dates.
I went through the script again, Now I understand what you are saying.  Why start building a prodid when the dates can be null. I should check for the dates first then build the prodid.Correct?Karunakaran___________It's better to be loved and lost, than ever to be loved... |
|
|
|
|
|
|
|
|
ActiveX or T-Sql Manipulation?