DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: NULL in Decode statement

  1. #1
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Hi there,

    Could anyone tell me how to get the else part of a DECODE statement to do nothing.

    e.g.

    select
    country, town,
    MAX(DECODE(trunc(Sysdate)-Check_Date , 1 , number, 2, number, 3, number, 4, number, 5, number, 6, number, 7, number , NULL)) ThisWeek
    from table
    group by country, town

    This statement will display the number if the sysdate - the check_date (date of input) = 1,2,3,4,5,6,7 (this week's data) else it will show nothing in the ThisWeek column,

    What I want is - if the criteria isn't true not to display the line atoll.

    Any help would be great.

    Many thanks

    Alison

  2. #2
    Join Date
    Feb 2001
    Posts
    9

    decode problem

    I cant understand ur problem...
    any way for ur query just add having conditions
    whatever u want..
    ex:
    having (/* ur decode stmt */) is not null;

    i hope this will solve ur problem
    bye
    komes

  3. #3
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Sorry I didn't really understand your reply..
    I was trying to simplify the problem the actual code I am using is

    select TS,
    Seg_Owner,
    Seg_Name,
    Seg_Type,
    Blocks,
    MAX_EXTENTS,
    lastweek,
    today,
    today-lastweek change
    from ( select TS,
    Seg_Owner,
    Seg_Name,
    Seg_Type,
    Blocks,
    MAX_EXTENTS,
    MAX(DECODE( trunc(sysdate)- Check_Date,7,
    extents, 8, extents, 9, extents, 10, extents, 11,
    extents, 12, extents, 13, extents, NULL)) LastWeek,
    MAX(DECODE( trunc(sysdate)-Check_Date, 0, extents,
    1, extents, 2, extents, 3, extents, 4, extents, 5,
    extents, 6, extents, NULL)) Today
    from &tablename
    where extents >= 50
    group by TS, Seg_Owner, Seg_Name, Seg_Type, Blocks, MAX_EXTENTS)

    The data in the table is input on a weekly basis (pulled from the data dictionary, I want to find the stats for "This week, Last week and the difference.

    When I run this script I get the following output

    TS SEG_OWNER SEG_NAME SEG_TYPE BLOCKS MAX_EXTENTS LASTWEEK TODAY CHANGE
    ---------- ---------- ---------------------- -------- -------- -------------- -------- ---------- --
    USER_DATA BSEC1 AUDIT_MARKET_TRACKING TABLE 17,420 2,147,483,645
    USER_DATA BSEC1 AUDIT_MARKET_TRACKING TABLE 17,810 2,147,483,645
    USER_DATA BSEC1 AUDIT_MARKET_TRACKING TABLE 18,850 2,147,483,645
    USER_DATA BSEC1 AUDIT_MARKET_TRACKING TABLE 19,370 2,147,483,645
    USER_DATA BSEC1 AUDIT_MARKET_TRACKING TABLE 19,630 2,147,483,645
    USER_DATA BSEC1 AUDIT_MARKET_TRACKING TABLE 20,150 2,147,483,645 152 152 0

    The only information I want is the bottom row - the rest is historical data from previous week's and months.

    Hope this clears up what I'm looking for.

    Many thanks for your reply

    Alison

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Forget about HAVING, use a simple WHERE condition. In your initial example it would be something like:

    select
    country, town,
    MAX(DECODE(trunc(Sysdate)-Check_Date , 1 , number, 2, number, 3, number, 4, number, 5, number, 6, number, 7, number )) ThisWeek
    from table
    WHERE Check_Date IS NOT NULL
    group by country, town;


    The condition "WHERE Check_Date IS NOT NULL" is sufficient in this case, because only if this is true your expression inside the DECODE ("trunc(Sysdate)-Check_Date") would return NULL.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Many thanks,

    As always your forums are a great source of information and advice, I look forward to being able to provide help to others in the future.

    Alison

Posting Permissions

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


Click Here to Expand Forum to Full Width