CODEWITHSUNDEEP
sqldatabasenullcase
Teddy
Teddy

Reputation: 9

removing null value from string

I want to show the translated name and description with language_id 'us' and 'ru'. My question is how can I remove the null values? 

SELECT DISTINCT CASE WHEN LANGUAGE_ID = 'US' THEN COALESCE(TO_CHAR(TRANSLATED_NAME), ' ')
       END AS PRODUCT_NAME_US,
       CASE WHEN LANGUAGE_ID = 'US' THEN INITCAP(CONCAT(SUBSTR(TRANSLATED_DESCRIPTION, 1, 30), '...'))
       END AS PRODUCT_DESC_US,
       CASE WHEN LANGUAGE_ID = 'RU' THEN COALESCE(TO_CHAR(TRANSLATED_NAME), ' ')
       END AS PRODUCT_NAME_RU,
       CASE WHEN LANGUAGE_ID = 'RU' THEN INITCAP(CONCAT(SUBSTR(TRANSLATED_DESCRIPTION, 1, 30), '...'))
       END AS PRODUCT_DESC_RU
FROM PRODUCT_DESCRIPTIONS;

Upvotes: 0

Views: 89

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

I suspect you want something like this:

SELECT PRODUCT_ID,
       MAX(CASE WHEN LANGUAGE_ID = 'US' THEN COALESCE(TO_CHAR(TRANSLATED_NAME), ' ')
           END) AS PRODUCT_NAME_US,
       MAX(CASE WHEN LANGUAGE_ID = 'US' THEN INITCAP(CONCAT(SUBSTR(TRANSLATED_DESCRIPTION, 1, 30), '...'))
           END) AS PRODUCT_DESC_US,
       MAX(CASE WHEN LANGUAGE_ID = 'RU' THEN COALESCE(TO_CHAR(TRANSLATED_NAME), ' ')
           END) AS PRODUCT_NAME_RU,
       MAX(CASE WHEN LANGUAGE_ID = 'RU' THEN INITCAP(CONCAT(SUBSTR(TRANSLATED_DESCRIPTION, 1, 30), '...'))
           END) AS PRODUCT_DESC_RU
FROM PRODUCT_DESCRIPTIONS
GROUP BY PRODUCT_ID; -- whatever each row defines

This does an explicit aggregation for each product (use the appropriate id of course), putting the English and Russian translations in the appropriate columns.

Upvotes: 1

Related Questions