Solved

decode and sign sql ..

Posted on 2007-04-06
4
1,343 Views
Last Modified: 2009-12-27
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
0
Comment
Question by:mahjag
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 

Author Comment

by:mahjag
ID: 18865498
So i want to add my expected result - view having columns

Total unit         start date           end date
200                   01-jan-07         28-feb-07
0
 

Author Comment

by:mahjag
ID: 18865625
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..
0
 
LVL 32

Accepted Solution

by:
awking00 earned 125 total points
ID: 18866329
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;
0
 

Author Comment

by:mahjag
ID: 18867088
Hi awking00

I ran the sql and I got the row that has amount = 0, I am trying to avoid this ..
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question