Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Unanswered: multiple use of lookup table

    I am working on management reports from a warehouse system (stock and stuff like that. The database contains a translation table that holds description for all codes used in the application.

    So, this table has a two-field PK:

    Keyword
    Value

    The value is matched to join to a facttable with the Keyword specified in order to address the proper fields. There are hundreds of fields having an entry as keyword in the translation-table.

    I want to use multiple translations in one SQL, which is now impossible because that would create a where-clause with excluding conditions.

    I was contemplating creating specific views for the most general keywords in order to address the translation table more then once in an SQL. Is that a viable strategy. Are there any alternatives?

    Working with ORACLE 7.3.3....
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    can't u use the UNION, MINUS or UNION ALL operators to do this ?

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    No ,not really the translation table may be used on different facttables within one SQL statement like running a query over sales_order_header,sales_order_line and produkttable. Each of these tables holds codes values for one or more fields...

    Can't think of a way to work that out with union queries. I want to use these views on the translation table as objects in Business Objects universes.

    But do you think this is a viable option in itself ?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    i am not sure. if i understand u right, u have 1 code table (say CODE), and multiple child tables all referencing the code table on the same columns. For example:
    Table CODE (id number, code varchar2)
    Table CHILD1 (id number, description varchar2, code_id number) referencing table CODE;
    Table CHILD2 (id number, description varchar2, code_id number) referencing table CODE;

    U want a query to retrieve values from child tables CHILD1 en CHILD2 joining them to the CODE table. So, it would be like in your vision

    select columnlist
    from CODE ca
    , CODE cb
    , CHILD1 c1
    , CHILD2 c2
    where c1.code_id = ca.id
    and c2.code_id = cb.id

    But the problem here is that there is no join to be made between CHILD1 and CHILD2, so u get a carthesian product (i guess), even if u use clauses to exclude values from CHILD1 or CHILD2.
    Using UNION or UNION ALL u do not have this problem.The query would be like :

    select COLUMNLIST
    from CODE c
    , CHILD1 c1
    where c1.code_id = c.id
    and <exclusing clauses on the resultset>
    UNION ALL
    select COLUMNLIST
    from code c
    , child2 c2
    where c2.code_id = c.id
    and <exclusing clauses on the resultset>
    .......

    I think this is the best way to get all results in one resultset, u might even use this in a view to use this Bussiness Objects.

    Hope i got the problem clear and this helps
    Using
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Edwin, thanks for your reaction.

    The situation is however very different.
    Consider having facttables that store a lot of coded information like the following field:

    STOCK.STAGE

    possible values for this field:

    00
    01
    10
    30
    99

    You can imagine that these codes will be meaningless to management.
    The database harbors a special table that stores the descriptions for these values for EVERY field that has coded values.

    This table will have several entries for the STOCK.STAGE field:

    KEYWORD VALUE LANGUAGE DESCRIPTION

    STK_STAGE 00 ENG abcdefgedthgklrgfortogfor
    STK_STAGE 01 ENG KLYHTJIAORIJGEQOG0EQGE
    .................. ... ..... .......................................


    Now for use in SQL the join between the two tables is between
    STOCK.STAGE and VALUE. However many fields will have codes '00' with totally different descriptions, so the KEYWORD has to be specified:


    STOCK.STAGE = TRANSLATIONTABLE.VALUE AND
    TRANSLATIONTABLE.KEYWORD = 'STK_STAGE'

    This will work perfectly for ONE translation. However with fetching several coded fields one would have a WHERE clause that has an exclusive effect: no data will ever be fetched:

    STOCK.STAGE = TRANSLATIONTABLE.VALUE AND
    TRANSLATIONTABLE.KEYWORD = 'STK_STAGE' AND
    FACTTABLE2.FIELD2 = TRANSLATIONTABLE.VALUE AND
    TRANSLATIONTABLE.KEYWORD = 'SOME OTHER KEYWORD'

    My strategy would be to create special views for the more general keywords, giving this WHERE clause:

    STOCK.STAGE = TRANSLATIONVIEW1.VALUE AND
    TRANSLATIONVIEW1.KEYWORD = 'STK_STAGE' AND
    FACTTABLE2.FIELD2 = TRANSLATIONVIEW2.VALUE AND
    TRANSLATIONVIEW2.KEYWORD = 'SOME OTHER KEYWORD'
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    1) Surely your views would look like:

    CREATE OR REPLACE VIEW v_stk_stage AS
    SELECT value FROM translationtable
    WHERE keyword = 'STK_STAGE';

    CREATE OR REPLACE VIEW v_some_other_keyword AS
    SELECT value FROM translationtable
    WHERE keyword = 'SOME OTHER KEYWORD';

    And then your queries look like:

    STOCK.STAGE = v_stk_stage.VALUE AND
    FACTTABLE2.FIELD2 = v_some_other_keyword.VALUE

    2) This "generic lookup table" concept is widely used (it is used where I work unfortunately), but it stinks. Why not just have a separate TABLE per lookup? That will resolve all your issue at a stroke! Plus it will allow the use of foreign key constraints, which apart from enforcing RI can also help the optimizer sometimes.

  7. #7
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Thanks Andrew,

    I know all about creating views :-). I aggree it stinks, but I have to work with a database as it is (it came with the application)
    Sure having separate tables would work, but that would mean having to maintain them too :-(
    Creating seperate views will always keep data in line if it is technically possible to use views instead of tables.........(me thinks)
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    My point with the views was that you don't need these conditions in your main query:

    STOCK.STAGE = TRANSLATIONTABLE.VALUE AND
    TRANSLATIONTABLE.KEYWORD = 'STK_STAGE' AND
    FACTTABLE2.FIELD2 = TRANSLATIONTABLE.VALUE AND
    TRANSLATIONTABLE.KEYWORD = 'SOME OTHER KEYWORD'

    (as they are part of the view definitions). Maybe you just forgot to delete those lines in your post?

    I would only advocate the separate tables INSTEAD OF the big generic table, not in addition to it. But as you say, your hands are tied by the application. Pity.

    Yes, creating these views is definitely technically possible. As I said, where I work we also have a big generic lookup table (alas), and we create a view for each and every lookup to make querying easier. There are no problems with doing that (the problems are on the data input side, where the lack of FKs on all these columns means the data is often corrupt).

  9. #9
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Originally posted by andrewst
    My point with the views was that you don't need these conditions in your main query:

    STOCK.STAGE = TRANSLATIONTABLE.VALUE AND
    TRANSLATIONTABLE.KEYWORD = 'STK_STAGE' AND
    FACTTABLE2.FIELD2 = TRANSLATIONTABLE.VALUE AND
    TRANSLATIONTABLE.KEYWORD = 'SOME OTHER KEYWORD'

    (as they are part of the view definitions). Maybe you just forgot to delete those lines in your post?

    Well, a datatable may have 7 different fields that qualify for translations, therefore I do not want to create a view over both translation table and the facttable , which would create the problem I want to avoid WITHIN the view DDL

    I want the translations for management purposes, but can do without them for the specialists that work with the codes and know them by heart.
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    We seem to be talking at cross-purposes here. I am not advocating joining to the fact table within the view definition at all! I am saying that if your view definition for one of the keywords is e.g.

    CREATE OR REPLACE VIEW v_stk_stage AS
    SELECT value, language, description
    FROM translationtable
    WHERE keyword = 'STK_STAGE';

    ... then when you join to it in your query, you do not want or need to duplicate the condition:

    WHERE keyword = 'STK_STAGE'

    ... in your query. It has already been done by the view.

  11. #11
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    But of course! We are on the same track . My example did not reference the view , but how it would fail without the view :-)

    Thanks again..
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  12. #12
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    i've been thinking about the problem (that's why i did not respond a while). It's like andrew said technically possible and i hope that all info u got from us gave u hope to do it the way u want to.
    Just one other thought from my side: ever thought about creating a stored function to retrieve a management description for a given keyword and value? U can use it in a query and in a view.
    Good luck.

    Greetz
    Edwin van Hattem
    OCP DBA / System analyst

  13. #13
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Originally posted by evanhattem
    hi,

    i've been thinking about the problem (that's why i did not respond a while). It's like andrew said technically possible and i hope that all info u got from us gave u hope to do it the way u want to.
    Just one other thought from my side: ever thought about creating a stored function to retrieve a management description for a given keyword and value? U can use it in a query and in a view.
    Good luck.

    Greetz
    Hello Edwin,

    The thought of an UDF did not cross my mind. I have to very cautious getting data from our warehouse system, with the vendor of the application keeping us in check. So far I managed to get the go ahead for a trigger and some read-only views. Besides that I am really more into DB2/datawarehousing then into ORACLE.

    The tricky thing is also that the keywords have slightly different syntax than the fieldnames themselves. So keyword STKSTAGE corresponds to field STK_STAGE, but the relationship is not always just +/- an underscore. Makes it hard to know what to use into a function....

    Since we are trying to build our management reporting with Business Objects read-only views will fit nicely into BO universes.

    Thanks for your thoughts on this .....
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •