September 7, 2003 at 3:53 pm
i am facing a problem while updating a fact table..
i have created my table with the follwing SQL Task
CREATE TABLE [companyfact_staging] (
[BASE_PERIOD_SHARES] numeric (12,0) NULL DEFAULT(0),
[AVERAGE_TURNOVER] numeric (12,0) NULL DEFAULT(0),
[COMPANY_CODE] varchar(30) NOT NULL DEFAULT('MISSING'),
[MARKET_TYPE] varchar(30) NOT NULL DEFAULT('MISSING'),
[INDEX_DATE] datetime NOT NULL,
[COMPANY_KEY] varchar (30) NULL DEFAULT ('MISSING'),
[MARKET_KEY] [int] NOT NULL DEFAULT (0),
[TIME_KEY] datetime null DEFAULT ('01-jul-02') )
i have placed default missing in the column but when i company_code which i want to insert from a dimension company..
but when i run the follwing update statement ..in execute SQL task
update companyfact_staging
set company_key =T.company_code
from companyfact_staging F INNER JOIN company_staging T on
T.company_code=F.company_key
WHERE F.company_key='MISSING' Go
i get no error when i run this code it says succesfully executed but the values in the column company_code still remain missing..they are not changed ..
does anyone have any idea ..what i should do about this..
i am also facing problem while updating
table to insert time_key
for which i am using..
the following code
update companyfact_staging
set time_key =T.time_id
from companyfact_staging F INNER JOIN time_staging T on
F.index_date=T.date
WHERE F.time_key='01-jul-02'
Go
...
please could anyone help me..about this..
as to what i should do..
i would be grateful..thnx
September 7, 2003 at 5:01 pm
Not sure I understand your logic here....
If you have inserted 'missing' into the foreign key column, how are you expecting to update it it to a valid key value?
Normally, you would have a row of raw data coming in that has say a textual typoe value (say it has company name of 'Steves Hardware'), which you would then use in your jon (or lookup) to the Company table to determine what surrogate key you want to use.
The reason why your update isn't working is that you are joining on the field that you have already set to 'missing', so even if you had a 'missing' value in your company table, it would update to itself (ie 'missing') anyway.
Send through some more of the schema if you would like some help setting it up.
HTH,
Steve
Steve.
September 8, 2003 at 1:54 pm
sairah,
on your first update statement:
update companyfact_staging
set company_key =T.company_code
from companyfact_staging F INNER JOIN company_staging T on
T.company_code=F.company_key
WHERE F.company_key='MISSING' Go
I believe your problem can be fixed by changing the join to as follows:
from companyfact_staging F INNER JOIN company_staging T on
F.company_code=T.company_key
This is provided there is a company_key field in the company_staging table.
As to the second update, you might want to try a CONVERT on the F.time_key in the where clause. Something like this:
CONVERT(varchar(12), F.time_key, 101) = '07/02/2001'
I hope this helps.
Dave N
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply