Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: Major DB2 SQL headache - suggestions welcomed.

    I have set up the following DB2 table, which is intended to store codes and their corresponding descriptions, and to allow for multiple languages and/or countries.

    DECLARE CODES TABLE (
    CATEGORY INTEGER NOT NULL,
    CODE CHAR(08) NOT NULL,
    LANGUAGE CHAR(03) NOT NULL,
    COUNTRY CHAR(03) NOT NULL,
    DESCRIPTION VARCHAR(64) NOT NULL)

    I added a unique primary key to this table as follows:

    CATEGORY ASC,
    CODE ASC,
    LANGUAGE DESC,
    COUNTRY DESC

    OK, Fine so far. Now I complicate things by specifying that a description might be specific to a language and a country, a language only or it may be a defaulted American English description.

    My problem is this. I want to obtain a complete list of codes for a given category, along with their descriptions. The following SQL:

    SELECT CODE, DESCRIPTION
    FROM CODES
    WHERE CATEGORY = :CATEGORY
    AND LANGUAGE IN (:LANGUAGE, ' ')
    AND COUNTRY IN (:COUNTRY, ' ')

    Could, in theory, return up to four rows per code. The requirement is to return one row per code, using the maximum language/country sub-key to select the appropriate description. The most efficient way to do this would be to get DB2 to somehow perform a skip-sequential read of the primary index, taking the first index per code. However, all my solutions so far have involved multiple levels of subselects and liberal use of the MAX() function, and don't strike me as efficient by any stretch of the imagination.

    So can anyone point me towards a better solution.

  2. #2
    Join Date
    Dec 2002
    Posts
    134

    Re: Major DB2 SQL headache - suggestions welcomed.

    SELECT CODE, DESCRIPTION
    FROM CODES
    WHERE CATEGORY = :CATEGORY
    AND LANGUAGE IN (:LANGUAGE, ' ')
    AND COUNTRY IN (:COUNTRY, ' ')
    order by language, country
    fetch first 1 row only

  3. #3
    Join Date
    Jan 2004
    Posts
    2
    FETCH FIRST ROW ONLY would work if I want the first code/description only, but I want to return a result set containing all codes in the code category.

    Right now I'm having to subselect MAX(LANGUAGE) for each category/code, then subselect MAX(COUNTRY) for each category/code/MAX(LANGUAGE). Once I have the complete key, I can read the description for that code. It looks ugly, at least I can draw some comfort in the fact that the two inner subselects access the index only.

  4. #4
    Join Date
    Dec 2002
    Posts
    134
    Ok, If I now understand your problem correctly, you can do something like this :

    select code, description from
    (
    SELECT CODE, DESCRIPTION, row_number() over(partition by code order by language desc, country desc)
    FROM CODES
    WHERE CATEGORY = :CATEGORY
    AND LANGUAGE IN (:LANGUAGE, ' ')
    AND COUNTRY IN (:COUNTRY, ' ')
    ) a
    where rn = 1

Posting Permissions

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