mahjag
asked on
decode and sign sql ..
I had to re-open an already closed question where sujith80 gave a solution for decode problem
Problem description
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)
Solution proposed
select X.unit, decode(sign(unit),1,st_dt,
from <your table> X,
(select min(start_date) st_dt, max(end_date) end_dt from <your table> ) Y
/
I applied this solution and having a problem, in the above decode if the unit is >0 then the dates min and max worked, in the data there are units <0 rows that are existing and it shows up as null start date (st_dt) and null end date (end_dt), since I am writing a view statement from this query I do not wan them to appear in the view and also I want only for the date sake to fetch the min and max dates for unit >0, but if the rows has units <0 I dont want them to show up and the total amount should have included the rows having units <0
for example if I have 3 rows as data set and let us they are
unit start date end date
100 01-jan-07 31-jan-07
200 01-feb-07 28-feb-07
-100 01-mar-07 31-mar-07
then for the start and end dates I need to get min (start date) and max ( end date) for unit >0
01-jan-07 and end date 28-feb-07
and the total should be 300-100 = 200
Problem description
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)
Solution proposed
select X.unit, decode(sign(unit),1,st_dt,
from <your table> X,
(select min(start_date) st_dt, max(end_date) end_dt from <your table> ) Y
/
I applied this solution and having a problem, in the above decode if the unit is >0 then the dates min and max worked, in the data there are units <0 rows that are existing and it shows up as null start date (st_dt) and null end date (end_dt), since I am writing a view statement from this query I do not wan them to appear in the view and also I want only for the date sake to fetch the min and max dates for unit >0, but if the rows has units <0 I dont want them to show up and the total amount should have included the rows having units <0
for example if I have 3 rows as data set and let us they are
unit start date end date
100 01-jan-07 31-jan-07
200 01-feb-07 28-feb-07
-100 01-mar-07 31-mar-07
then for the start and end dates I need to get min (start date) and max ( end date) for unit >0
01-jan-07 and end date 28-feb-07
and the total should be 300-100 = 200
ASKER
Looks like I got away with the problem by doing this
select max(decode(sign(unit),1,st
to avoid group by the whole decode statement and now I got what I want - but it looks to me as pseudo solution by wrapping the aggregate max over the decode and sign as it means nothing.. I will wait for folks to jump in on the solution - nevertheless I found the above work around..
select max(decode(sign(unit),1,st
to avoid group by the whole decode statement and now I got what I want - but it looks to me as pseudo solution by wrapping the aggregate max over the decode and sign as it means nothing.. I will wait for folks to jump in on the solution - nevertheless I found the above work around..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi awking00
I ran the sql and I got the row that has amount = 0, I am trying to avoid this ..
I ran the sql and I got the row that has amount = 0, I am trying to avoid this ..
ASKER
Total unit start date end date
200 01-jan-07 28-feb-07