Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136

    Unanswered: Problem creating view with functions

    Hello!


    I have a big question:


    ==================================
    table TEXT
    ==================================
    CREATE TABLE TEXT
    (
    ID INTEGER CONSTRAINT PK_TEXT PRIMARY KEY (ID),
    LANGUAGE_1 VARCHAR2(100),
    LANGUAGE_2 VARCHAR2(100),
    ...
    LANGUAGE_n ..
    );

    ==================================
    table TEST
    ==================================
    CREATE TABLE TEST
    (
    ID INTEGER CONSTRAINT PK_TEST PRIMARY KEY (ID),
    NAME VARCHAR2(30) CONSTRAINT NN_TEST_NAME NOT NULL,
    DESCRIPTION INTEGER,
    COMMENTS INTEGER
    );
    ==================================
    MY QUESTION:
    ==================================
    The real string-values like DESCRIPTION, COMMENTS ..are
    because of language-independence integers.
    They contain Ids from the table TEXT.
    Now i want to create a view that displays the table TEST
    but with the correct string-values from the table TEXT.

    My problem is not the function but its implementation (call).
    How can i input FNC_GET_TEXT_FROM_ID into my view?
    Some other possibilities?

    ======================================
    view VIEW_TEST
    ======================================

    CREATE OR REPLACE FORCE VIEW VIEW_TEST
    (V_ID, V_NAME, V_DESCRIPTION, V_COMMENTS)
    AS
    SELECT
    ID
    , NAME
    , DESCRIPTION
    , COMMENTS
    /* FNC_GET_TEXT_FROM_ID(COMMENTS, 1); --??????????????*/
    FROM TEST;

    =========================================
    function FNC_GET_TEXT_FROM_ID
    =========================================

    CREATE OR REPLACE FUNCTION FNC_GET_TEXT_FROM_ID
    (
    pId IN VARCHAR2,
    pLanguage IN INTEGER
    )
    RETURN VARCHAR2 IS
    NUM_LANG NUMBER;
    STR_LANG VARCHAR2(250);
    STR_TEXT VARCHAR2(250);
    BEGIN

    NUM_LANG := pLanguage;
    STR_TEXT := '';

    IF NUM_LANG = 1 THEN
    SELECT LANGUAGE_1 INTO STR_LANG FROM TEXT WHERE ID = pId;
    ELSIF NUM_LANG = 2 THEN
    SELECT LANGUAGE_2 INTO STR_LANG FROM TEXT WHERE ID = pId;
    ELSE
    RAISE_APPLICATION_ERROR(-20000, 'test_err_msg');
    END IF;

    STR_TEXT := STR_LANG;
    RETURN (STR_TEXT);

    END FNC_GET_TEXT_FROM_ID;
    /


    Thanks in advance!
    Last edited by julla27; 03-31-04 at 06:20.

  2. #2
    Join Date
    Mar 2004
    Location
    Venice,Italy
    Posts
    20
    a possible solution can be this:
    CREATE TABLE TEXT
    (
    ID INTEGER,
    ID_LANG INTEGER,
    LANGUAGE VARCHAR2(100)
    CONSTRAINT PK_TEXT PRIMARY KEY (ID,ID_LANG)
    );

    CREATE OR REPLACE FORCE VIEW VIEW_TEST
    (V_ID, V_NAME, V_DESCRIPTION, V_COMMENTS)
    AS
    SELECT
    TEST.ID
    , TEST.NAME
    , TEST.DESCRIPTION
    , TEST.COMMENTS
    , nvl(TEXT.LANGUAGE,'-- no traduction --')
    FROM TEST,TEXT where TEST.id(+)=TEXT.id and TEXT.ID_LANG=1;

    P.S.
    Personally I hate tables with too many columns, especially when their name tarminates with numbers...

  3. #3
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Thank You, Zeus,

    but i think, i've not correct explained, what i really want..

    e.g.
    ===================================
    TEST
    ===================================
    ID || NAME || COMMENTS ||
    _____________________________________________
    1 || test || 100 ||
    2 || ... || 101 ||
    _____________________________________________


    ==========================================
    TEXT
    ==========================================
    ID || LANG_1(English) || LANG_2(German)|| [open-ended]
    __________________________________________________ _
    100 || one || eins ||
    101 || hello || hallo ||
    __________________________________________________ _

    i wouldn't like to do it without this structure, because:

    1) it gives the possibility to check vocabulary in many languages simultaneously

    2) it gives the possibility to display any texts in my software-application in the correct language dynamically.

    3) i have many tables with those text-columns, so i need my function,
    but don't know how to implement it

    4) so my function FNC_GET_TEXT_FROM_ID gets the accordant text in accordant language from the TEXT through TEST.COMMENT = TEXT.ID..


    Thank You!
    Last edited by julla27; 03-31-04 at 08:08.
    Regards,
    Julia

  4. #4
    Join Date
    Mar 2004
    Location
    Venice,Italy
    Posts
    20
    Originally posted by julla27
    4) so my function FNC_GET_TEXT_FROM_ID gets the accordant text in accordant language from the TEXT through TEST.COMMENT = TEXT.ID..
    Thank You!
    sorry, I missed the field name...

    well, if you want use function this ddl

    CREATE OR REPLACE FORCE VIEW VIEW_TEST
    (V_ID, V_NAME, V_DESCRIPTION, V_COMMENTS,V_TRAD)
    AS
    SELECT
    ID
    , NAME
    , DESCRIPTION
    , COMMENTS
    , FNC_GET_TEXT_FROM_ID(COMMENTS, 1)
    FROM TEST;

    works...

  5. #5
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Thank You very much!

    It works now!




    This View:

    ======================================
    view VIEW_TEST
    ======================================

    CREATE OR REPLACE FORCE VIEW VIEW_TEST
    (V_ID, V_NAME, V_DESCRIPTION, V_COMMENTS)
    AS
    SELECT
    ID
    , NAME
    , DESCRIPTION
    , COMMENTS
    /* FNC_GET_TEXT_FROM_ID(COMMENTS, 1); --??????????????*/
    FROM TEST;


    didn't function because of wrong number of columns!



    Thanks,
    Julia
    Regards,
    Julia

  6. #6
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Hi Julia,

    EMPTY YOUR INBOX!!! ITS FULL.

Posting Permissions

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