CODEWITHSUNDEEP
sqldatabaseoracle-database
Govind Mathur
Govind Mathur

Reputation: 31

query to add default values if the tables column are null

I have a table in which boa_user, which return three user as..

SELECT DISTINCT u.name, u.updated_by, u.updateddate
FROM YTR_USER u, YTR_user_group ug, YTR_group g
WHERE u.id = ug.user_id
  AND ug.group_id = g.id
  AND u.ACTIVE_FLAG='Y'
  AND g.id not in (13,11,15)
ORDER BY u.name

below is the format ..

NAME    UPDATED_BY  UPDATEDDATE
AAA     edfef      03.03.03 14:29:34.000
BBB     ferer      12.12.12 15:13:23.756

Now coulmn are of type

name        VARCHAR2(30)
updateddate TIMESTAMP(6)

Now rite now the query return the result but in between there are some columns in which name value is null and updateddate value is null.

My query is to please advise a query so that if name is null and updated date is null then it's defalut value should be like "AFGT" AND updateddate default value should be '05.03.13 05:29:34', 'MM.DD.YY HH:MI'

please advise query to add these default values if name and updated date is null

Upvotes: 1

Views: 673

Answers (2)

Fabian Barney
Fabian Barney

Reputation: 14549

Use NVL-Function in the select part.

Something like that:

SELECT DISTINCT 
    nvl(u.name, <defaultname>), 
    u.updated_by, 
    nvl(u.updateddate, <deafultdate>)
FROM YTR_USER u, YTR_user_group ug, YTR_group g
WHERE u.id = ug.user_id
  AND ug.group_id = g.id
  AND u.ACTIVE_FLAG='Y'
  AND g.id not in (13,11,15)
ORDER BY u.name

Be aware that I expect that you want the default name-value when name is null or the default updateddate-value when updateddate is null. Your question is a bit unclear here.

So the above statement for example will also return the default-name when name is null and updateddate is not null. It just returns default values whenever the corresponding field is null - no matter other fields are null or not.

Upvotes: 2

Jan Spurny
Jan Spurny

Reputation: 5527

If I understand you correctly, when name IS NULL AND updateddate IS NULL you want to use:

name = 'AFGT'
updateddate = TO_TIMESTAMP('05.03.13 05:29:34', 'MM.DD.YY HH:MI')

If that's correct, what you need is just a CASE.

It depends however if you want DISTINCT to apply to defaulted rows or not. If you do, then use:

SELECT DISTINCT x.corrected_name, x.corrected_updateddate
FROM (
    SELECT
        CASE WHEN u.name IS NULL AND u.updateddate IS NULL
            THEN 'AFGT'
            ELSE u.name
        END AS corrected_name,
        u.updated_by,
        CASE WHEN u.name IS NULL AND u.updateddate IS NULL
            THEN TO_TIMESTAMP('05.03.13 05:29:34', 'MM.DD.YY HH:MI')
            ELSE u.updateddate
        END AS corrected_updateddate
    FROM YTR_USER u, YTR_user_group ug, YTR_group g
    WHERE u.id = ug.user_id
      AND ug.group_id = g.id
      AND u.ACTIVE_FLAG='Y'
      AND g.id not in (13,11,15)
    ORDER BY u.name
) AS x;

of if you want all defaulted rows in the result:

SELECT x.corrected_name, x.corrected_updateddate
FROM (
    SELECT DISTINCT
        CASE WHEN u.name IS NULL AND u.updateddate IS NULL
            THEN 'AFGT'
            ELSE u.name
        END AS corrected_name,
        u.updated_by,
        CASE WHEN u.name IS NULL AND u.updateddate IS NULL
            THEN TO_TIMESTAMP('05.03.13 05:29:34', 'MM.DD.YY HH:MI')
            ELSE u.updateddate
        END AS corrected_updateddate,
        u.name,
        u.updateddate
    FROM YTR_USER u, YTR_user_group ug, YTR_group g
    WHERE u.id = ug.user_id
      AND ug.group_id = g.id
      AND u.ACTIVE_FLAG='Y'
      AND g.id not in (13,11,15)
    ORDER BY u.name
) AS x;

Upvotes: 0

Related Questions