July 8, 2009 at 5:33 am
Hello,
I realise a buseiness intelligence project for an enterprise.
I would like to know your advices because I don't know how to do:(
I have a table "tb_beforeFact" and I have 2millions records in it.
The table(fact table) is:
IDCLI M2HBITA M3ETUD M2CONSO
219 10 5 4
518 0 2 3
554 5 0 0
904 0 2 6
and the other table is (dimension table) "dim_credit":
ID_CREDIT NAME
1 habit
2 etudiant
3 conso
I want to construct my proper table as using these 2 tables:
The table I want to construct is:
IDCLI ID_CREDIT AMOUNT
219 1 10
219 2 5
219 3 4
518 2 2
518 3 3
904 2 2
904 3 6
The problem that in the "tb_beforeFact" fact table they are too many records and I use SQL server 2008. i want to code a performant stored procedure and I think we can not use for or if statements in SQL?
Cursor is it a good idea?
July 8, 2009 at 8:25 am
Hi.
I'm not sure if I'm really answering your problem, but if you need a way to achieve the desired result, it's very simple to do so without any cursor. As for performance, I do not know how a solution like this one will react on 2 millions of rows.
-- CREATE the tables we need
DECLARE @tb_beforeFact TABLE
(
IDCLI int PRIMARY KEY,
M2HBITA int,
M3ETUD int,
M2CONSO int
)
DECLARE @dim_credit TABLE
(
ID_CREDIT int PRIMARY KEY,
NAME varchar(20) COLLATE Latin1_General_CI_AI
)
-- INSERT the test data we need
INSERT @tb_beforeFact
(
IDCLI,
M2HBITA,
M3ETUD,
M2CONSO
)
SELECT 219, 10, 5, 4 UNION ALL
SELECT 518, 0, 2, 3 UNION ALL
SELECT 554, 5, 0, 0 UNION ALL
SELECT 904, 0, 2, 6
INSERT @dim_credit
(
ID_CREDIT,
NAME
)
SELECT 1, 'habit' UNION ALL
SELECT 2, 'etudiant' UNION ALL
SELECT 3, 'conso'
-- CHECK the data as it is
SELECT * FROM @tb_beforeFact
SELECT * FROM @dim_credit
-- SELECT the data as we want it
SELECT
tb_beforeFact.IDCLI,
dim_credit.ID_CREDIT,
CASE dim_credit.ID_CREDIT
WHEN 1 THEN tb_beforeFact.M2HBITA
WHEN 2 THEN tb_beforeFact.M3ETUD
WHEN 3 THEN tb_beforeFact.M2CONSO
END AS Amount
FROM
@tb_beforeFact AS tb_beforeFact
INNER JOIN @dim_credit AS dim_credit
ON CASE dim_credit.ID_CREDIT
WHEN 1 THEN tb_beforeFact.M2HBITA
WHEN 2 THEN tb_beforeFact.M3ETUD
WHEN 3 THEN tb_beforeFact.M2CONSO
END 0
ORDER BY
tb_beforeFact.IDCLI,
dim_credit.ID_CREDIT
July 9, 2009 at 7:47 am
Hi,
thank u so much for your kind answer, I didn't really waite as a good query ready to execute:) it is really easier than to use the cursor.
But in fact it was only a part of my needs but this code gives me a good idea to improve my stored procedure
July 9, 2009 at 8:28 am
Glad I could help 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply