decode in oracle (ver 8.1) - check the value

I want to write a sql to check the value of a field greater than zero and then do min (start date) if the value (of unit ) greater than zero and also do max(end date) for the same condition if the units is greater than zero
I am inserting the start and end to another table so for start date I should check
if unit >0 then
min (start date)

similarly for end date I should check
if unit >0 then
max(end date)
mahjagAsked:
Who is Participating?
 
SujithData ArchitectCommented:
You havent mentioned what columns are expected in the output. And what should be the output if unit = 0?

Use this query:

select unit, decode(sign(unit), 1, min(start_date) over(), NULL),
             decode(sign(unit), 1, max(end_date) over(), NULL)
from <your table>
/

An alternative is :

select X.unit, decode(sign(unit),1,st_dt, NULL), decode(sign(unit),1,end_dt, NULL)
from <your table> X,
(select min(start_date) st_dt, max(end_date) end_dt from <your table> ) Y
/
0
 
mukhtar2tCommented:
select
case  when unit>0 then min(start_date)  end start_date,
case  when unit>0 then max(end_date) end start_date
from your_table
 
0
 
mukhtar2tCommented:
select
case  when unit>0 then min(start_date)  end start_date,
case  when unit>0 then max(end_date) end end_date
from your_table
 
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
mahjagAuthor Commented:
is case supported in 8i?
0
 
mahjagAuthor Commented:
I tried the solution you gave and I got error ORA-00937 - not a single group function
0
 
MohanKNairCommented:
select
case  when b.unit>0 then a.start_date  end start_date,
case  when b.unit>0 then a.end_date end end_date
FROM
(select id, min(start_date) start_date, max(end_date) end_date from mytable group by id) a,
mytable b
WHERE
a.id=b.id;
0
 
mukhtar2tCommented:
is the unit field on separate table and contains one row?
0
 
mahjagAuthor Commented:
Hi Sujith80 - need your help

unfortunately I need to reopen this as I wanted to incorporate this to already existing view and the select statement is giving problems, my view statement looks like this

select sum(wamount), sum(ramount),
          max(st_dt), min(end_dt),....
from (select col1 from table b where customer id = <idno>) h1,tableA, tableB..
where h1.col1 = tableA.col1
and ...

I want to remove the max(st_dt) and min (end_dt) with your solution .. any ideas how to incorporate this when there is already a in line sql using from clause as table name
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.