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 |
JR1204
Starting Member
7 Posts |
Posted - 2013-02-14 : 17:16:15
|
Hi folks, I am pulling text files from the web, and inserting into 1 of my three currency tables (fiBaseCurrency.Ratefile). For any given base currency, there is a separate text file we're pulling into the table, and therefore a separate record for each in the fiBaseCurrency table.Text file example:{ "disclaimer": "Exchange rates are provided for informational purposes only, and do not constitute financial advice of any kind. Although every attempt is made to ensure quality, NO guarantees are given whatsoever of accuracy, validity, availability, or fitness for any purpose - please use at your own risk. All usage is subject to your acceptance of the Terms and Conditions of Service, available at: http://openexchangerates.org/terms/", "license": "Data sourced from various providers with public-facing APIs; copyright may apply; resale is prohibited; no warranties given of any kind. All usage is subject to your acceptance of the License Agreement available at: http://openexchangerates.org/license/", "timestamp": 1361365208, "base": "USD", "rates": { "AED": 3.673208, "AFN": 51.905767, "ALL": 104.581251, "AMD": 406.843331, "ANG": 1.7887, "AOA": 95.956866 }}Here are the basic table structures in SQL:First table, This is the header- 'fiExchange' Includes: ExchangeID (PK)Second table- 'fiBaseCurrency' includes: BaseCurrencyID (PK), ExchangeID (FK), Ratefile (This is the currency text file).Third Table- 'fiRate' Includes: RateID (PK), BaseCurrencyID (FK),BaseCurrency, ToCurrency, RateThe stored procedure below parses the text file for a single record from fiBaseCurrency, and inserts each rate into the fiRate table. I will continue with the USD example from above. The BaseCurrencyID for this example is 847.SQL code below:ALTER procedure [dbo].[webExchange_BaseCurrencyParseUpd] @basecurrencyId intas declare @Ratefile nvarchar(max) declare @addedon datetime declare @currency nvarchar(50) select @Ratefile = replace(replace(ratefile , Char(10), ''),char(13), '') from finance..fiBaseCurrency select @Ratefile = right( @ratefile, len(@ratefile)-charindex('base', @ratefile)+1) select @AddedOn=GETDATE()set @Ratefile = replace(@Ratefile , '} }', ',')declare @CommaPos intdeclare @Seg nvarchar(50),@seg1 nvarchar(50),@seg2 nvarchar(50),@Base nvarchar(50)select @CommaPos=charindex (',', @ratefile)while @CommaPos>0 begin select @Seg = left( @Ratefile, @Commapos) set @seg1 =ltrim(rtrim(replace(left(@Seg, charindex(':', @Seg)), ':', ''))) set @Seg2 = ltrim(rtrim(replace(right(@seg, len(@seg)-charindex(':', @seg)), ',', ''))) if @seg1 = 'Rates' begin set @Seg = @seg2 set @seg1 =ltrim(rtrim(replace(replace(left(@Seg, charindex(':', @Seg)), ':', ''),'{', ''))) set @Seg2 = ltrim(rtrim(replace(right(@seg, len(@seg)-charindex(':', @seg)), ',', '')))end --Insert statement that inserts records into fiRate based on BaseCurrencyID if @Seg1 = 'base' set @Base =@Seg2 else insert into fiRate ([BaseCurrencyID],[BaseCurrency],[ToCurrency],[Rate],[Addedon]) select @BasecurrencyId, @Base, @seg1,@seg2, @addedon (select @Ratefile = right( @ratefile, len(@ratefile)-@commapos)) (select @CommaPos=charindex (',', @ratefile)) endThese are the results following the query. The records have been inserted into fiRate:BaseCurrencyID RateID BaseCurrency ToCurrency Rate847 641 USD AED 3.673461847 642 USD AFN 51.699834847 643 USD ALL 104.570001847 644 USD AMD 406.646669847 645 USD ANG 1.788750847 646 USD AOA 95.962466My goal is to Run this stored procedure, or a variation of this stored procedure, and have it cover ALL BaseCurrencyID's (in fiBaseCurrency) for a given ExchangeID (fiExchange). I think a loop may work, or something similar. But I do not know the correct syntax. Please help!!! Thank you in advance! |
|
Mar
Starting Member
47 Posts |
Posted - 2013-02-19 : 08:47:14
|
Maybe someone could help you, but you need to make your question clear.You understand your problem, no one else does.What does "parse and insert ALL fiBaseCurrency records from the header level" mean? Only you know.whats the data look like? Only you know.See the problem?To get an answer you need to provide all the details. First break down your task into peices and start with the first piece that you need help with.Mar |
|
|
JR1204
Starting Member
7 Posts |
Posted - 2013-02-20 : 08:25:47
|
Thanks for the tip Mar. I will edit the post and make it more clear.Regards, |
|
|
Mar
Starting Member
47 Posts |
Posted - 2013-02-20 : 11:19:38
|
Better. I can help you writing a loop. The syntax of a loop is:WHILE <Boolean expression> <code block> Example:DECLARE @i intSET @i = 0WHILE @i < 10 BEGIN PRINT CAST(@i AS char) SET @i = @i + 1 END |
|
|
JR1204
Starting Member
7 Posts |
Posted - 2013-02-20 : 11:58:54
|
Mar, one issue i am having is this: @BaseCurrencyID is the parameter for my sp. It is there so it will run against the correct record from fiBaseCurrency. So, if I try to run it from the fiExchange table, i receive the error 'Procedure expects parameter @BasecurrencyID, which was not supplied'. Is there a way to loop through the BaseCurrencyID based on the ExchangeID? ExchangeID is data type int and is a shared field in both tables. |
|
|
JR1204
Starting Member
7 Posts |
Posted - 2013-02-21 : 08:35:06
|
This may be the incorrect positioning of the loop within the stored procedure:ALTER PROCEDURE [dbo].[webExchange_BaseCurrencyParseUpd] @BaseCurrencyID intas declare @i int declare @Ratefile nvarchar(max) declare @addedon datetime declare @currency nvarchar(50) declare @ExchangeID int select @Ratefile = replace(replace(ratefile , Char(10), ''),char(13), '') from finance..fiBaseCurrency select @ExchangeID= ExchangeID from finance..fiBaseCurrency select @Ratefile = right( @ratefile, len(@ratefile)-charindex('base', @ratefile)+1) select @AddedOn=GETDATE() select @basecurrencyID = basecurrencyID from finance.. fiBaseCurrency set @Ratefile = replace(@Ratefile , '} }', ',')set @i=0while @i<41Begin print cast(@i as char)set @i=@i+1enddeclare @CommaPos intdeclare @Seg nvarchar(50),@seg1 nvarchar(50),@seg2 nvarchar(50),@Base nvarchar(50)select @CommaPos=charindex (',', @ratefile)while @CommaPos>0begin select @Seg = left( @Ratefile, @Commapos) set @seg1 =ltrim(rtrim(replace(left(@Seg, charindex(':', @Seg)), ':', ''))) set @Seg2 = ltrim(rtrim(replace(right(@seg, len(@seg)-charindex(':', @seg)), ',', ''))) if @seg1 = 'Rates' begin set @Seg = @seg2 set @seg1 =ltrim(rtrim(replace(replace(left(@Seg, charindex(':', @Seg)), ':', ''),'{', ''))) set @Seg2 = ltrim(rtrim(replace(right(@seg, len(@seg)-charindex(':', @seg)), ',', '')))end --Insert statement that inserts records into fiRate based on BaseCurrencyID if @Seg1 = 'base' set @Base =@Seg2 else insert into fiRate ([BaseCurrencyID],[BaseCurrency],[ToCurrency],[Rate],[Addedon]) select @BasecurrencyId, @Base, @seg1,@seg2, @addedon (select @Ratefile = right( @ratefile, len(@ratefile)-@commapos)) (select @CommaPos=charindex (',', @ratefile)) endI am receiving this error when i try to execute the SP:Msg 201, Level 16, State 4, Procedure webExchange_BaseCurrencyParseUpd, Line 0Procedure or function 'webExchangeParseFileUpd' expects parameter '@BaseCurrencyID', which was not supplied. |
|
|
Mar
Starting Member
47 Posts |
Posted - 2013-02-22 : 09:13:37
|
You can do a select to get the BaseCurrencyID from the ExchangeID.SELECT BaseCurrencyID FROM fiBaseCurrency WHERE ExchangeID = {insert your variable here} This will give you a record set of BaseCurrencyIDs.You can loop through them using a cursor, but cursors can be slow. Sometimes they are the only option.If that seems like it will work for you then try it. I can help you get through it as I've used cursors before. |
|
|
JR1204
Starting Member
7 Posts |
Posted - 2013-02-22 : 11:04:41
|
Thanks, That sounds like I may need that here. Should I write a new sp that calls the first one? For example...ALTER PROCEDURE [dbo].[webExchangeParseFileUpd] @ExchangeID intasdeclare @BasecurrencyID intdeclare @i intselect @ExchangeID=ExchangeID from fiExchangeselect @basecurrencyID=BasecurrencyID from fiBaseCurrency where ExchangeID=@ExchangeIDset @i=0while @i<41beginexec webExchange_BaseCurrencyParseUpd @BaseCurrencyIDset @i=@i+1endThis procedure parses the correct number of records from fiBaseCurrency (There are 40 text files I am pulling in, and this procedure parses 40 records), however it does not run through all the BaseCurrencyID's associated with the ExchangeID, it only parses the same record 40 times. Please see below:BaseCurencyID RateID BaseCurrency ToCurrency Rate1206 3681 USD AED 3.6728431206 3841 USD AED 3.6728431206 4001 USD AED 3.6728431206 4161 USD AED 3.6728431206 4321 USD AED 3.6728431206 4481 USD AED 3.672843 |
|
|
JR1204
Starting Member
7 Posts |
Posted - 2013-02-25 : 09:06:01
|
Mar,Thanks for your help on this. The final SP that works exactly how I want it to is this:ALTER PROCEDURE [dbo].[webExchangeParseFileUpd] @ExchangeID intasdeclare @BasecurrencyID intdeclare @i intselect @ExchangeID=ExchangeID from fiExchangeselect @basecurrencyID=min(BasecurrencyID) from fiBaseCurrency where ExchangeID=@ExchangeIDset @i=0while @i<40beginexec webExchange_BaseCurrencyParseUpd @BaseCurrencyIDset @i=@i+1set @BasecurrencyID=@BasecurrencyID+1endThanks again |
|
|
Mar
Starting Member
47 Posts |
Posted - 2013-02-25 : 10:01:17
|
You need to explain in words AND comment your code. If you don't explain in words then no one else will ever know what you are trying to do.Commenting your code helps because that explains what you are trying to do.Or you can explain what you wish to accomplish without using code.The stored procedure doesn't make sense.In this line you are requesting a parameterALTER PROCEDURE [dbo].[webExchange_BaseCurrencyParseUpd] @BaseCurrencyID intThen replacing whatever was passed inselect @basecurrencyID = basecurrencyID from finance.. fiBaseCurrencyWhich makes passing in a parameter pointless. Why not remove that line? Then that would require only one record in the table fiBaseCurrency OR a FROM clause.Mar |
|
|
|
|
|
|
|