NULL in Decode statement

[alison]

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

[komes]

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

[alison]

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

[jmodic]

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,

[alison]

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