stored procedure

Question

Post reply

stored procedure

aslhanhanc

Old Hand

Points: 391
More actions
July 8, 2009 at 5:33 am

#95556

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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Melo SSC Veteran Points: 299 More actions · Answer 1

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,