| How to Make This Query [message #507609] |
Tue, 17 May 2011 06:40  |
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
|
Senior Member |
|
|
Dear i have followning code sequence
24 Main A/c.
24-01 Sub A/c.
24-01-01 Detail A/c.
24-01-01-0001 Ledger A/c.
So i want that query calculate Ledger A/c. and Result Shown in 24 Main A/c. For Examle there are four salesman who sale in sal_master and sal_detail table so i want that query gives us four rows in main A/c like abc sales =20000,xyz sales = 50000 etc.
regards faheem
|
|
|
|
| Re: How to Make This Query [message #507649 is a reply to message #507609] |
Tue, 17 May 2011 07:56  |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Your question is very vague.
What is the structure of sal_master?
What is the strucuture of sal_detail?
What is the relationship between those tables?
What is the relationship between those tables and the code sequence?
What is the relationship between Ledger A/c and 24 Main A/c?
|
|
|
|
| Re: How to Make This Query [message #507657 is a reply to message #507609] |
Tue, 17 May 2011 08:05 |
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
|
Senior Member |
|
|
select a.account_no,a.account_nm from account.chart,
(select item_code,sum(nvl(net_amount)) ant from account.sal_mastr,account.sal_detail where sal_detail.voc_no=sal_master.voc_no group by item_code)
for example item_code=2401010001','2501010002' etc. this query working well and display two rows but i want this query return only one row because my master account style is
24 Marbles
25 Tiles
26 Accessories etc.
so i want that sum of all net_amount which start with 24 return a single row the code may be up '24-01-01-9999'
thanks
|
|
|
|
|
|
| Re: How to Make This Query [message #507664 is a reply to message #507659] |
Tue, 17 May 2011 08:15 |
qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN
|
Senior Member |
|
|
Now i Paste Query from final report and it works well
select a.account_no,substr(a.account_no,1,2) main,substr(a.account_no,1,4) sub,substr(a.account_no,1,6) detail, a.account_nm,(nvl(amt,0)+nvl(sale,0)) sales,(nvl(amtr,0)+nvl(return,0)) sales_ret,
(nvl(amt,0)+nvl(sale,0))-(nvl(amtr,0)+nvl(return,0)) net_sales
from account.chart a,
(select item_code,sum(nvl(net_amount,0)) amt from account.delivery_master,account.delivery_detail where (delivery_detail.voc_no=delivery_master.voc_no) and delivery_master.voc_date between :from_date and :end_date and delivery_master.voc_type='MS' group by item_code) b,
(select item_code,sum(nvl(net_amount,0)) amtr from account.delivery_master,account.delivery_detail where (delivery_detail.voc_no=delivery_master.voc_no) and delivery_master.voc_date between :from_date and :end_date and delivery_master.voc_type='MR' group by item_code) c,
(select item_code,sum(nvl(net_amount,0)) sale from account.sal_master,account.sal_detail where (sal_detail.voc_no=sal_master.voc_no) and sal_master.voc_date between :from_date and :end_date and
sal_master.voc_type='S' group by item_code) d,
(select item_code,sum(nvl(net_amount,0)) return from account.sal_master,account.sal_detail where (sal_detail.voc_no=sal_master.voc_no) and sal_master.voc_date between :from_date and :end_date and sal_master.voc_type='SR' group by item_code) e
where a.account_no=b.item_code(+)
and a.account_no=c.item_code(+)
and a.account_no=d.item_code(+)
and a.account_no=e.item_code(+)
and a.unit is not null
and (a.account_no between :acc_code and :acc_code2 ) and trans_type='T'
and nvl(amt,0)+nvl(sale,0)+nvl(amtr,0)+nvl(return,0)>0
|
|
|
|
| Re: How to Make This Query [message #507673 is a reply to message #507649] |
Tue, 17 May 2011 08:32 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
cookiemonster wrote on Tue, 17 May 2011 14:09That doesn't answer any of my questions.
cookiemonster wrote on Tue, 17 May 2011 13:56
What is the structure of sal_master?
What is the strucuture of sal_detail?
What is the relationship between those tables?
What is the relationship between those tables and the code sequence?
What is the relationship between Ledger A/c and 24 Main A/c?
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
