-
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
-
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
-
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
-
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,
-
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