ansaurus

Question

SQL query on columns of a table....(Oracle)

Answer 1

A:

From the information given so far, I'd say that First Normal Form would be your friend here.

So your table would become something more like this:

CREATE TABLE FCT_SALES_SUMMARY
(
  PROD_SID                 NUMBER,
  CURR_CUST_SID            NUMBER,
  BIO_ID                   NUMBER,
  CURR_TERR_SID            NUMBER,
  FRAN_SID                 NUMBER,
  CURR_EMP_SID             NUMBER,
  ESTMT_FLG                VARCHAR2(1 BYTE),
  PROD_WAC_PRC             NUMBER(15,4),
  SALE_RATIO               NUMBER,
  WK_UNITSCUR              NUMBER(15,4),
  WEEK_NUMBER              NUMBER,
  WK_UNITS                 NUMBER(15,4)
)

...with one row per week (as designated by WEEK_NUMBER above - call it whatever is most meaningful for your app).

Now you won't lose any data that you don't want to lose: WEEK_NUMBER (or whatever) just keeps incrementing by one each week.

Will it make the table's storage requirements greater? Yes. Disc space is cheap.

Will it make queries slower? Possibly, but computers are fast... ;-)

Mike Woodhouse 2009-08-25 15:43:59

You're assuming the OP has the ability to change the data model. I've seen accounting programs with similar [horrendous] modelling...

OMG Ponies 2009-08-25 16:20:55

Yes I am. And I've suffered something ghastly like that myself... But if he can't change the schema, there's no good answer that I can think of - only bad ones. Really, really bad. IOW, if he wanted to get to there, he shouldn't start from here...

Mike Woodhouse 2009-08-25 19:34:58

ansaurus

tags:

views:

answers:

SQL query on columns of a table....(Oracle)

related questions