| Author |
 Topic  |
|
|
JR1204
Starting Member
6 Posts |
 Posted - 02/14/2013 : 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, Rate
The 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 int
as
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 int
declare @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))
end
These are the results following the query. The records have been inserted into fiRate:
BaseCurrencyID RateID BaseCurrency ToCurrency Rate
847 641 USD AED 3.673461
847 642 USD AFN 51.699834
847 643 USD ALL 104.570001
847 644 USD AMD 406.646669
847 645 USD ANG 1.788750
847 646 USD AOA 95.962466
My 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! |
Edited by - JR1204 on 02/20/2013 09:30:34
|
|
|
Mar
Starting Member
23 Posts |
Posted - 02/19/2013 : 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
6 Posts |
Posted - 02/20/2013 : 08:25:47
|
Thanks for the tip Mar. I will edit the post and make it more clear.
Regards, |
|
|
|
Mar
Starting Member
23 Posts |
Posted - 02/20/2013 : 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 int
SET @i = 0
WHILE @i < 10
BEGIN
PRINT CAST(@i AS char)
SET @i = @i + 1
END |
|
|
|
JR1204
Starting Member
6 Posts |
Posted - 02/20/2013 : 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
6 Posts |
Posted - 02/21/2013 : 08:35:06
|
This may be the incorrect positioning of the loop within the stored procedure:
ALTER PROCEDURE [dbo].[webExchange_BaseCurrencyParseUpd] @BaseCurrencyID int
as
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=0
while @i<41
Begin print cast(@i as char)
set @i=@i+1
end
declare @CommaPos int
declare @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))
end
I am receiving this error when i try to execute the SP:
Msg 201, Level 16, State 4, Procedure webExchange_BaseCurrencyParseUpd, Line 0
Procedure or function 'webExchangeParseFileUpd' expects parameter '@BaseCurrencyID', which was not supplied. |
|
|
|
Mar
Starting Member
23 Posts |
Posted - 02/22/2013 : 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
6 Posts |
Posted - 02/22/2013 : 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 int
as
declare @BasecurrencyID int
declare @i int
select @ExchangeID=ExchangeID from fiExchange
select @basecurrencyID=BasecurrencyID from fiBaseCurrency where ExchangeID=@ExchangeID
set @i=0
while @i<41
begin
exec webExchange_BaseCurrencyParseUpd @BaseCurrencyID
set @i=@i+1
end
This 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 Rate
1206 3681 USD AED 3.672843
1206 3841 USD AED 3.672843
1206 4001 USD AED 3.672843
1206 4161 USD AED 3.672843
1206 4321 USD AED 3.672843
1206 4481 USD AED 3.672843 |
|
|
|
JR1204
Starting Member
6 Posts |
Posted - 02/25/2013 : 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 int
as
declare @BasecurrencyID int
declare @i int
select @ExchangeID=ExchangeID from fiExchange
select @basecurrencyID=min(BasecurrencyID) from fiBaseCurrency where ExchangeID=@ExchangeID
set @i=0
while @i<40
begin
exec webExchange_BaseCurrencyParseUpd @BaseCurrencyID
set @i=@i+1
set @BasecurrencyID=@BasecurrencyID+1
end
Thanks again |
|
|
|
Mar
Starting Member
23 Posts |
Posted - 02/25/2013 : 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 parameter
ALTER PROCEDURE [dbo].[webExchange_BaseCurrencyParseUpd] @BaseCurrencyID int
Then replacing whatever was passed in
select @basecurrencyID = basecurrencyID from finance.. fiBaseCurrency
Which 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 |
|
|
| |
Topic |
|
|
|
Insert Details from Header
