query to add default values if the tables column are null

Question

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

Fabian Barney · Accepted Answer · 2013-08-01 11:49:19Z

2

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.

edited Aug 1, 2013 at 11:49

answered Aug 1, 2013 at 11:15

Fabian Barney

14.6k6 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Govind Mathur Over a year ago

getting this error upon execution.. ORA-00933: SQL command not properly ended

Govind Mathur Over a year ago

also please let me know default name I am putting inside single quotes lik 'AAA' and similiarly date also

Jan Spurny · Accepted Answer · 2013-08-01 10:59:27Z

0

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;

edited Aug 1, 2013 at 10:59

answered Aug 1, 2013 at 10:27

Jan Spurny

5,5971 gold badge36 silver badges52 bronze badges

4 Comments

Govind Mathur Over a year ago

ORA-01791: not a SELECTed expression..GETTING THIS ERROR

Jan Spurny Over a year ago

@GovindMathur sorry, my bad. I dont' know if you want to repeat every row which was defaulted or not, so i added both variants

Govind Mathur Over a year ago

Thanks a lot for the great help but upon execution getting this error this time..ORA-00933: SQL command not properly ended

Jan Spurny Over a year ago

@GovindMathur as you're getting the same error as in case of Fabian Barney's answer, could you paste here how does it look like in sqlplus (query AND the error) ?

Collectives™ on Stack Overflow

query to add default values if the tables column are null

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related