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.
Table
Account_balance
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



Invoice
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
11|2|0|0|300|0|0|0
13|3|0|600|0|0|0|0
14|4|1200|0|0|0|0|0|0


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 ,
inv_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.
sam_2012Asked:
Who is Participating?
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?
0
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
0
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.
0

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sam_2012Author Commented:
How does the case statement performance , when we 100 million records ?
0
sam_2012Author Commented:
can it not be achieved using decode statement
0
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?
0
sam_2012Author Commented:
awesome
0
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.

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.