• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1358
  • Last Modified:

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

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
  • 3
1 Solution
mahjagAuthor Commented:
So i want to add my expected result - view having columns

Total unit         start date           end date
200                   01-jan-07         28-feb-07
mahjagAuthor Commented:
Looks like I got away with the problem by doing this

select max(decode(sign(unit),1,st_dt, NULL), decode(sign(unit),1,end_dt, NULL))
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 sum(u.unit), x.st_dt, x.end_dt
from your_tbl u,
(select min(st_dt) st_dt
       ,max(end_dt) end_dt
 from your_tbl
 where unit > 0) x
group by x.st_dt, x.end_dt;
mahjagAuthor Commented:
Hi awking00

I ran the sql and I got the row that has amount = 0, I am trying to avoid this ..
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.

Join & Write a Comment

Featured Post

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now