Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    10

    Question Unanswered: display conditionally column values

    I have two tables. One is main table and other holds the localized translation of eligible columns of main table (Product Name in this case) for different supported locales:

    PRODUCT
    Code:
    ID | DEFAULT_NAME
    1      Bag
    2      Mobile
    PRODUCT_TRANSLATION
    Code:
    ID | P_ID  |  LOCALE  |  LOCALIZED_NAME
    1       1           DE              Bag_in_German
    2       1           ZH              Bag_in_Chines
    3       2           DE              Mobile_in_German
    I can have following query to get the localized product details
    Code:
    select p.id,PT.LOCALIZED_NAME from product p, PRODUCT_TRANSLATION(+) PT
    where p.id = PT.P_ID and LOCALE = 'DE'
    But not all product will have Product translation for every locale.

    What SQL to write to get the default Product name value if localized product name doesn't exists for required locale. For e.g.: the 2 column output of SQL statement for Product "Mobile" should be:
    Code:
    Mobile_in_German     DE
    Mobile                    ZH

    Note: the SQL statement should be SQL92 type (compatible with other database).

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Note: the SQL statement should be SQL92 type (compatible with other database).
    post list of "compatible" RDBMS
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    As you didn't tell us what the "default" is, I assume 'ZH' means "default":

    Code:
    SELECT p.id,
           pt.localized_name
    FROM product p 
      JOIN product_translation pt ON p.id = pt.p_id AND pt.locale = 'DE'
    UNION ALL
    SELECT p.id,
           pt.localized_name
    FROM product p 
      JOIN product_translation pt ON p.id = pt.p_id AND pt.locale = 'ZH'
    WHERE NOT EXISTS (SELECT 42
                      FROM product_translation pt2
                      WHERE pt2.locale = 'DE'
                        AND pt2.p_id = p.id)
    Last edited by shammat; 09-06-11 at 17:26.

  4. #4
    Join Date
    Aug 2011
    Posts
    10
    anacedent, I am an Application Developer and my intention is to make SQL statements used in application are compatible with SQL92 database. I don't have the complete list of sql92 databases, but I want to make it work with at least Oracle and Mysql.

    shammat, sorry for the confusion, "DEFAULT_NAME" column of PRODUCT table should derive the default value. Thanks for the SQL, I have modified it to come up with required SQL statement:

    Code:
    SELECT p.id, pt.product_name FROM product p JOIN product_translation pt ON p.id = pt.p_id AND pt.locale = 'ZH' 
    UNION ALL
    SELECT p.id, p.default_name FROM product p
    WHERE NOT EXISTS (SELECT 42 FROM product_translation pt2 WHERE pt2.locale = 'ZH' AND pt2.p_id = p.id)
    I hope it's OK if the order is not same in both SELECT Statement - I was not able to put ORDER BY Clause in SELECT Statements and get the output.
    I Guess, I have to make "id" of Product and "p_id" of product_translation as index columns to make above sql work faster.

    Questions:
    1. is this statement compatible with all SQL92 type databases ?
    2. is it OK if I use "UNION" instead of "UNION ALL" ? - what will be the difference in this case ?
    Last edited by sahanand; 09-07-11 at 03:10.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by sahanand View Post
    I was not able to put ORDER BY Clause in SELECT Statements and get the output.
    You can only order the overall result of the UNION not of each individual part. Just put a single ORDER BY after everything

    I Guess, I have to make "id" of Product and "p_id" of product_translation as index columns to make above sql work faster.
    Look at the execution plan to find that out.

    1. is this statement compatible with all SQL92 type databases ?
    All databases that comply with the standard, yes. A lot of DBMS completely ignore some essential things. But this will most probably work on all major DBMS.

    2. is it OK if I use "UNION" instead of "UNION ALL" ? - what will be the difference in this case ?
    UNION removes duplicates between the two SELECTs which makes it a bit slower. UNION ALL does not remove duplicates. Read the manual it's all in there.

  6. #6
    Join Date
    Aug 2011
    Posts
    10

    Thumbs up

    shammat, Thanks a lot for your help.

Tags for this Thread

Posting Permissions

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