Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Google search


Poll
How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
17%
200 - 500 GB
17%
500 - 800 GB
6%
800 - 1200 GB
6%
1200 - 1500 GB
17%
1500 - 2000 GB
17%
> 2000 GB
22%
Total votes: 18

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 6th January 2006, 08:59
ganesh_kapase's Avatar
ganesh_kapase ganesh_kapase is offline
Senior Member
 
Join Date: Dec 2004
Location: India
Posts: 189
ganesh_kapase is on a distinguished road
Baan: BaaN IV - DB: SQL Server - OS: WINDOWS-2000
Error in Query

Hi

While compiling mentioned code getting 4 error messages for columns of tisfc001 table. One of the example as below.
Error SQL: Column 'tisfc001.pdno' is not used as group identifier

In the Query, table tisfc001 contains unique records for item where as table ticst001 contains multiple records for one item. I want summation of field ticst001.qucs for one item.

Required your HELP !!!

Thanx

Code:
function read.main.table.D()
{
	select	tisfc001.pdno, tisfc001.mitm, tisfc001.qdlv, tisfc001.osta,
		ticst001.pdno, ticst001.sitm, sum(ticst001.qucs):est.qty
	from	tisfc001, ticst001
	where     tisfc001._index2 inrange {:cprj.f,:item.f,:pdno.f} and
                         {:cprj.t,:item.t,:pdno.t}
	and	tisfc001.osta BETWEEN 5 and 7
	and	ticst001._index3 inrange {:rm.item.f} and {:rm.item.t}
	and	ticst001.pdno = :tisfc001.pdno
	group by ticst001.pdno, ticst001.sitm
	order by tisfc001.pdno, tisfc001.mitm
}
__________________
- Ganesh
-------------------------------------------------
TEAMWORK-Coming Together is a Beginning...Keeping Together is Progress...Working Together is A Success.

Last edited by ganesh_kapase : 6th January 2006 at 11:49.
Reply With Quote
  #2  
Old 6th January 2006, 10:18
george7a's Avatar
george7a george7a is offline
Guru
 
Join Date: May 2004
Location: Nazareth
Posts: 1,491
george7a is on a distinguished road
Baan: IVc, 5.0 b, 5.0 c, LN 6.1 - DB: MS SQL, Oracle - OS: Windows 2000, 2003 & UNIX
Hi

The field tisfc001.mit is missing a letter in the order by clause

Regards,

- George
__________________
_
George Abdo
NAZDAQ - Your Documents, Your Way.
Reply With Quote
  #3  
Old 6th January 2006, 10:28
ganesh_kapase's Avatar
ganesh_kapase ganesh_kapase is offline
Senior Member
 
Join Date: Dec 2004
Location: India
Posts: 189
ganesh_kapase is on a distinguished road
Baan: BaaN IV - DB: SQL Server - OS: WINDOWS-2000
Its typographical mistake, in the original query the command line is

order by tisfc001.pdno, tisfc001.mitm
__________________
- Ganesh
-------------------------------------------------
TEAMWORK-Coming Together is a Beginning...Keeping Together is Progress...Working Together is A Success.
Reply With Quote
  #4  
Old 6th January 2006, 11:29
csecgn's Avatar
csecgn csecgn is offline
Guru
 
Join Date: Aug 2004
Location: Cologne/Germany
Posts: 300
csecgn is on a distinguished road
Baan: IVc4 SP14 (Tools SP 21) ; LN 6.1 (Testing) - DB: Oracle - OS: Solaris
The field tisfc001.pdno is missing in the group by clause. For my experience you cannot order by a field that is not in the group clause.

Regards
csecgn
Reply With Quote
  #5  
Old 6th January 2006, 11:59
ganesh_kapase's Avatar
ganesh_kapase ganesh_kapase is offline
Senior Member
 
Join Date: Dec 2004
Location: India
Posts: 189
ganesh_kapase is on a distinguished road
Baan: BaaN IV - DB: SQL Server - OS: WINDOWS-2000
Hi csecgn

Is it reqd to use tisfc001.pdno field in Group By statement in addition to ticst001.pdno. Is it allowed ?

Thanx
__________________
- Ganesh
-------------------------------------------------
TEAMWORK-Coming Together is a Beginning...Keeping Together is Progress...Working Together is A Success.
Reply With Quote
  #6  
Old 6th January 2006, 12:04
csecgn's Avatar
csecgn csecgn is offline
Guru
 
Join Date: Aug 2004
Location: Cologne/Germany
Posts: 300
csecgn is on a distinguished road
Baan: IVc4 SP14 (Tools SP 21) ; LN 6.1 (Testing) - DB: Oracle - OS: Solaris
I Don't know and I've never tried it. But is it necessary?
If your relations are OK, I think you only need tisfc001.pdno (or the other one. I would try both and look if there is a difference in the result and the performance).

Regards
csecgn
Reply With Quote
  #7  
Old 6th January 2006, 15:39
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,122
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
This should work:

Code:
select  tisfc001.pdno, tisfc001.mitm, tisfc001.qdlv, 
	tisfc001.osta, ticst001.sitm, sum(ticst001.qucs):est.qty
from    tisfc001, ticst001
where     tisfc001._index2 inrange {:cprj.f,:item.f,:pdno.f} and
                         {:cprj.t,:item.t,:pdno.t}
and	tisfc001.osta BETWEEN 5 and 7
and	ticst001._index3 inrange {:rm.item.f} and {:rm.item.t}
and	ticst001.pdno = tisfc001.pdno
group by tisfc001.pdno, tisfc001.mitm, tisfc001.qdlv, 
tisfc001.osta, ticst001.sitm

Why include the ticst001.pdno? You already have the tisfc001.pdno. As far as I know when you to queries with sums you have to group by all other fields. You can also always separate the queries and do the sum of ticst001 in a query inside the selectdo on tisfc001. Also notice I removed the : infront of the tisfc001.pdno in the where clause.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.

Last edited by mark_h : 6th January 2006 at 15:53. Reason: turn off smiles
Reply With Quote
  #8  
Old 7th January 2006, 04:39
ganesh_kapase's Avatar
ganesh_kapase ganesh_kapase is offline
Senior Member
 
Join Date: Dec 2004
Location: India
Posts: 189
ganesh_kapase is on a distinguished road
Baan: BaaN IV - DB: SQL Server - OS: WINDOWS-2000
Hi Mark

Thanx I will check the results by applying your suggesstions. As mentioned by you, plz tell me the sub-query syntax by considering my posted SQL. It will be helpful to me.

Quote:
Why include the ticst001.pdno? You already have the tisfc001.pdno. As far as I know when you to queries with sums you have to group by all other fields. You can also always separate the queries and do the sum of ticst001 in a query inside the selectdo on tisfc001. Also notice I removed the : infront of the tisfc001.pdno in the where clause.
__________________
- Ganesh
-------------------------------------------------
TEAMWORK-Coming Together is a Beginning...Keeping Together is Progress...Working Together is A Success.
Reply With Quote
  #9  
Old 7th January 2006, 10:21
ganesh_kapase's Avatar
ganesh_kapase ganesh_kapase is offline
Senior Member
 
Join Date: Dec 2004
Location: India
Posts: 189
ganesh_kapase is on a distinguished road
Baan: BaaN IV - DB: SQL Server - OS: WINDOWS-2000
HI Mark

The code suggested by you is working fine. Plz let me know the sub-query logic for the same code so that I can check the performance also.

Thanx
__________________
- Ganesh
-------------------------------------------------
TEAMWORK-Coming Together is a Beginning...Keeping Together is Progress...Working Together is A Success.
Reply With Quote
Sponsored Links
  #10  
Old 7th January 2006, 18:17
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,122
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Sometimes I find separating queries to be quicker and sometimes I separate queries just to make it easier for me to understand what is happening. In this case I am not sure separating the queries would increase performance. Reading the performance guide can help in determining when to separate queries.

Code:
select  tisfc001.pdno, tisfc001.mitm, tisfc001.qdlv, 
	tisfc001.osta
from    tisfc001
where   tisfc001._index2 inrange {:cprj.f,:item.f,:pdno.f} and
				{:cprj.t,:item.t,:pdno.t}
and	tisfc001.osta BETWEEN 5 and 7
selectdo
	select 	ticst001.sitm, sum(ticst001.qucs):est.qty
	from	ticst001
	where	ticst001._index1 = {:tisfc001.pdno}
	and	ticst001.sitm inrange {:rm.item.f} and {:rm.item.t}
	group by ticst001.sitm
	selectdo
	endselect
endselect
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't query the ttadv380 table. walter01 Tools Development 23 17th September 2006 01:18
Desktop Query eshirley Tools Administration & Installation 0 10th September 2002 02:41


All times are GMT +2. The time now is 18:42.


©2001-2018 - Baanboard.com - Baanforums.com