Oracle 9i

Hi Team,

Need an help on the below query , used to show the balance amount on the invoice based on the pay dates age.
Account_no    Inv_no    Paydue_date   balance_amt
10                          1         12/01/2015        200
11                          2          12/02/2015       300
13                          3          12/03/2015       600
14                         4           12/05/2015      120

Inv_no     Invoice_date       status          
1              01/01/2015                1
2             01/02/2015                1
3             01/03/2015                1
4              01/05/2015               1

I need to display the output as below

Account_no  |Inv_no| balance 0-30 days old| balance 30-60 days old | balance 60-90 days old|balance 90-120 days old| balance 120-150 days old  |balance 180 days old

10 |1| 0|0| 0| 0|0|200

Below is the query used

Select account_no ||'|'||inv_no||'|'||'bal 0-30days'||'|'||'bal 30-60days'||'|'||'bal 60-90days'||'|'||'bal90-120days'||'|'||'bal 120-150days'||'|'||'bal 150-180days'||'|'||'bal >180days'
union all

Select account_no ,
sum(decode(sign(round((Sysdate-paydue_date)+30/30)),1,balance_due,0)) "0-30days",
sum(decode(sign(round((Sysdate-paydue_date)+30/60)),1,balance_due,0)) "30-60days",
sum(decode(sign(round((Sysdate-paydue_date)+30/90)),1,balance_due,0)) "60-90days",
sum(decode(sign(round((Sysdate-paydue_date)+30/120)),1,balance_due,0)) "90-120days",
sum(decode(sign(round((Sysdate-paydue_date)+30/150)),1,balance_due,0)) "120-150days",
sum(decode(sign(round((Sysdate-paydue_date)+30/180)),1,balance_due,0)) ">180days"

from account_balance
where balance_due>0
group by account_no , inv_no;

Iam not  getting the correct results .  Any help in this regard is really appreciated.  I need to check what is the age of the invoice as of today by subtracting the paydue_date -sysdate , if it is between 0-30 days , i need to show in the balance amout in this column else into the bucket as shown above.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
What has an invoice to do with your account balance table?

Why don't you have a account transaction table?
sam_2012Author Commented:
for each account , we generate the invoice  for orders he has placed , the customer makes an payment before the paydue date and the balance after the payment is maintained in account_balance table .  We need to pull the account , invoice , whose balance is 0-30 days old , 30-60 days old , 60-90 days old , 90-120 days old , 120-150 days old , > 180 days
slightwv (䄆 Netminder) Commented:
Your sample data doesn't seem to match the expected results using sysdate (or my dates are all messed up) but try a case statement.

Something like:
select account_no || '|' || inv_no || '|' ||
	case when
		trunc(sysdate)-paydue_date <= 30 then balance_amt else 0 end || '|' || 
	case when
		trunc(sysdate)-paydue_date >= 31 and trunc(sysdate)-paydue_date <= 60 then balance_amt else 0 end || '|' || 
	case when
		trunc(sysdate)-paydue_date >= 61 and trunc(sysdate)-paydue_date <= 90 then balance_amt else 0 end || '|' || 
	case when
		trunc(sysdate)-paydue_date >= 91 and trunc(sysdate)-paydue_date <= 120 then balance_amt else 0 end || '|' || 
	case when
		trunc(sysdate)-paydue_date <= 121 and trunc(sysdate)-paydue_date <= 150 then balance_amt else 0 end || '|' || 
	case when
		trunc(sysdate)-paydue_date <= 151and trunc(sysdate)-paydue_date <= 180 then balance_amt else 0 end || '|' || 
	case when
		trunc(sysdate)-paydue_date > 180 then balance_amt else 0 end 

Open in new window

Also, on your UNION to get the headers:  The header isn't guaranteed to be the first row.  The only way to guarantee ORDER of rows is to use an ORDER BY.

To get the header with a union, I add a sort_order column.  1 for the header and 2 for the main query then order by sort_order.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sam_2012Author Commented:
How does the case statement performance , when we 100 million records ?
sam_2012Author Commented:
can it not be achieved using decode statement
slightwv (䄆 Netminder) Commented:
>>How does the case statement performance

Never run a performance comparison between case and decode.

Come up with a test decode can do and run CASE versus decode on 1 million rows.  See if there is a noticeable difference.

>>can it not be achieved using decode statement

I don't see how.  Decode is pretty simple if/then/else with simple true/false ability.  I don't think I've ever seen decode with a less than/greater than and "and" contitions.  

You might be able to come up with some 'tricks' but why bother when CASE does what you need?
sam_2012Author Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.