Home » Developer & Programmer » Reports & Discoverer » How to Make This Query (Reports 6i)
How to Make This Query [message #507609] Tue, 17 May 2011 06:40 Go to next message
qanita786
Messages: 152
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 Go to previous messageGo to next message
cookiemonster
Messages: 10842
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 Go to previous messageGo to next message
qanita786
Messages: 152
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 #507659 is a reply to message #507657] Tue, 17 May 2011 08:09 Go to previous messageGo to next message
cookiemonster
Messages: 10842
Registered: September 2008
Location: Rainy Manchester
Senior Member
That doesn't answer any of my questions.
And the query you've posted can't possibly work well since you've missed out the 2nd parameter for nvl.
Re: How to Make This Query [message #507664 is a reply to message #507659] Tue, 17 May 2011 08:15 Go to previous messageGo to next message
qanita786
Messages: 152
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 Go to previous messageGo to next message
cookiemonster
Messages: 10842
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Tue, 17 May 2011 14:09
That 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?

Re: How to Make This Query [message #507675 is a reply to message #507673] Tue, 17 May 2011 08:36 Go to previous messageGo to next message
qanita786
Messages: 152
Registered: May 2007
Location: PAKISTAN
Senior Member
Structur is clearly defined in a query if you read carefully
Re: How to Make This Query [message #507678 is a reply to message #507675] Tue, 17 May 2011 08:44 Go to previous messageGo to next message
cookiemonster
Messages: 10842
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you don't want help then?
Structure is most clearly defined by create table statements. and that still leaves questions 3, 4 and 5.
Re: How to Make This Query [message #507800 is a reply to message #507678] Wed, 18 May 2011 01:09 Go to previous message
Littlefoot
Messages: 19299
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Cookiemonster
So you don't want help then?

I guess not, because
qanita786
Now i Paste Query from final report and it works well
Previous Topic: Graph Wizard
Next Topic: sorting order
Goto Forum:
  


Current Time: Wed Jul 23 03:08:48 CDT 2014

Total time taken to generate the page: 0.11017 seconds