Dear friends,
I have a ETL that have a Lookup transform to get a rate from a table SpotRates.
The problem is when the match od some date in SpotRates Table doens't exist...
And for that records I need to lookup for next date...
For example...
SpotRate Table
When I first try to lookup the date 17-04-2006, doesnt give me any records... and I need to create a new lookup for the next date from 17-04-2006. And in this example the next date is 18-04-2006.. How can I do it?
I made a sql query date gives me the next date with 2 parameters ... but I'm having some errors...
SELECT TOP 1 Data
FROM Spot_Rates
WHERE (Currencies_Name = ?) AND (Data > CONVERT(DATETIME, ?, 102))
ORDER BY Data DESC
In this exampple, the parameters returned from lookup1 is:
Currencies_name= 'DOLAR ESTADOS UNIDOS'
DATE='17-04-2006'
I need to create a second lookup transform to return the next date/currency for each row that didnt match in the first lookup...
Regards,
Pedro
What are your errors?I also think you'd want "Data >= CONVERT(DATETIME......)"|||
I have tried this way...
In lookUp transformation in 1oTab "Reference Table" I inserted SQL query:
SELECT Data, RevalRate, Currencies_Name
FROM Spot_Rates
In 2oTab "Columns" I'm inserted the relations between input/output and check the field that I want for output.
In 3oTab "Advanced" I'm inserted :
select TOP 1 * from
(SELECT Data, RevalRate, Currencies_Name
FROM Spot_Rates) as refTable
WHERE ([refTable].[Currencies_Name] = ?) AND ( [refTable].[Data] > CONVERT(DATETIME, ?, 102))
ORDER BY Data DESC
But at this moment the package are spending a lot of time to finalize... I still waiting... :-(
Regards,
pedro
|||And it's not being return the correct revalrate for each row with Currencies_Name/Date...
It's returning the last date for this currency and not for input parameter date for each row... :-(
Regards
|||Do you have indexes at all on the Spot_Rates table? How large is the Spot_Rates table?|||USE [dbRentabilidade]
GO
/****** Object: Table [dbo].[Spot_Rates] Script Date: 04/16/2007 15:36:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Spot_Rates](
[Currencies_ShortName] [nvarchar](3) NULL,
[Currencies_Name] [varchar](32) NULL,
[Data] [datetime] NULL,
[RevalRate] [float] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
About 55.000 rows...
|||Pedro,
I would like to make some points:
The query needs to have an OR clause; so it retrives the exact match or the nearest greater one. If you are going to use TOP 1; the query must guarentee that resultset is ordered ASC If you use a Lookup transform the way you are using it (query w/parameters) it will work using partial cache; which menas it will run the query for each row passing through. That is way it is so slow. How many rows are passing through? If the Spot_dates table and the rows passing through the pipeline are in the same database; you are better of creating a database function to retrive the right RevalRate. That way the DB engine will carry the overhead.|||In spite of looup transform, i'm trying to use OLE DB Command... executing the following SQL Stored Procedure:
USE [dbRentabilidade]
GO
/****** Object: StoredProcedure [dbo].[R_SP_GET_NextDayRate] Script Date: 04/16/2007 18:17:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[R_SP_GET_NextDayRate]
@.MyCurrency varchar(32),
@.MyDate datetime
AS
SELECT TOP 1 Data AS TESTE
FROM Spot_Rates
WHERE (Currencies_Name = @.MyCurrency) AND (Data > CONVERT(DATETIME, @.MyDate, 102))
ORDER BY Data ASC
RETURN
But the problem is that I dont know how to read the values returned from SP in order to continue the dataflow with this values... IT'S Possible?!
|||It looks like you cannot get columns back to the dataflow pipe line via oledb command. But here is the thing, even if you find the way; the performance will be about the same than the one you get using the Lookup component. That is why I was asking if you could include the join or function right on your source component.|||And what you think about insert the non matched values into temp table, and use a lookup to find the rate in this temp table to continue the dataflow?
what you think?
|||I have created a OLEDB Command to insert values that didnt match the lookup in a table, and have a loopup to match the values in thsi table...
The problem now, is that in the dataflow, the values inserted by OLE DB Command are not refreshed in the dataflow in the moment that I do a new match in the lookup transformation to this table...
...
7. LookUp
7.1. MutiCast
7.2. OLE DB Command
7.2.1 LookUp
And the values inserted in OLE DB Command are in the table database, but not in the dataflow in the moment that data is passing throut lookup transformation....
Could someone help me?
Thanks
|||I am getting lost with your approach; but in general you could break the process in 2 data flows; the first one to insert the no matches into the temp table; the second to do the lookup. does this make sense?|||
PedroCGD wrote:
I have created a OLEDB Command to insert values that didnt match the lookup in a table, and have a loopup to match the values in thsi table...
The problem now, is that in the dataflow, the values inserted by OLE DB Command are not refreshed in the dataflow in the moment that I do a new match in the lookup transformation to this table...
...
7. LookUp
7.1. MutiCast
7.2. OLE DB Command
7.2.1 LookUp
And the values inserted in OLE DB Command are in the table database, but not in the dataflow in the moment that data is passing throut lookup transformation....
Could someone help me?
Thanks
Enable memory restriction in your second lookup (on the advanced tab). This will slow down the data flow, but it's the only way you will see the new data in the lookup.
I've been watching your threads on this over the last day. Have you considered just populating your rate table with data for each day? Even if you use a seperate data flow to create a "temporary" working table with all the dates filled in, it would still make your process much simpler to implement and maintain.
|||Make sense and I already tried it before... but when I do the lookup the data not exist yet... :-(
Regards,
Pedro
|||jwelch,
But we are talking about millions of rows...
I'm trying to include one more lookup... but I dont know if it will work,... i give you feedback soon!
Thanks!
No comments:
Post a Comment