Baanboard.com

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

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 27th November 2003, 16:39
bernhard_frei bernhard_frei is offline
Junior Member
 
Join Date: Oct 2003
Posts: 5
bernhard_frei is on a distinguished road
Baan: B40c4 - DB: Oracle 8i - OS: Windows 2000
Angry Inner join not working as expected

Hi

I want to do an inner join on tables tdsls704 and tdsls045 (primary key is {orno, pono, srnb}). Since there is no reference defined between the two, I tried using the standard SQL join syntax. But all records from tdsls704 keep showing up in the result (as I would expect from an outer join).
Code:
function read.main.table()
{
	select tdsls704.*, tdsls045.*, tccom010.*
	from tdsls704, tdsls045, tccom010
	where tdsls704.akcd = "NL"
	and tdsls704 refers to tccom010 UNREF SETUNREF
	and tdsls045.orno = tdsls704.orno and tdsls045.pono = tdsls704.pono and tdsls045.srnb = 0
	selectdo
		days.to.del = tdsls045.ddat - tdsls704.date
		rprt_send()
	endselect
}
Any ideas?

Thanks
Bernhard
Reply With Quote
  #2  
Old 1st December 2003, 14:40
evesely's Avatar
evesely evesely is offline
Guru
 
Join Date: Dec 2001
Location: Lisle, Illinois USA
Posts: 411
evesely is on a distinguished road
If you want to use refers to, try unref skip.

However, it is simpler and quicker to just do:
Code:
...
and tccom010._index1 = {tdsls704.cuno}
...

BTW, you could also change your tdsls045 part of the WHERE clause to use its index:
Code:
and tdsls045._index1 = {tdsls704.orno, tdsls704.pono, 0}
Reply With Quote
  #3  
Old 1st December 2003, 15:14
bernhard_frei bernhard_frei is offline
Junior Member
 
Join Date: Oct 2003
Posts: 5
bernhard_frei is on a distinguished road
Baan: B40c4 - DB: Oracle 8i - OS: Windows 2000
Hi Ed

>If you want to use refers to, try unref skip.<

My intention was *not* to skip records in the unlikely case a reference to tccom010 is not found (in the sense of an outer join), that's why I used SETUNREF. I thought your suggestion tccom010._index1 = {tdsls704.cuno} would rather work like an inner join?

However, my original problem, the join between tdsls704 and tdsls045, still persists, I also tried your suggestion

>and tdsls045._index1 = {tdsls704.orno, tdsls704.pono, 0}<

and it makes no difference, i.e. records from tdsls045 are selected even if there's no matching record in tdsls704.

Regards,
Bernhard
Reply With Quote
  #4  
Old 1st December 2003, 15:44
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,903
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
How I would attack it...

The way I would attack this is to first take the tccom010 table out of the select. Then run it to make sure you get only the records you expect to get from the tdsls045 and tdsls704 tables. This may show that the problem is caused in the tccom010 part of the select. If not eliminate the tdsls045 table next. Build the select one statement at a time to see what causes the problem. There is a possiblity that an index is corrupt.

I would probably put the tccom010 select inside the selectdo anyway with "as set with 1 rows" on the query. I would refer to the optimization guide(post somewhere on the board, and I think in this forum) on breaking select statements up.

Not sure if this helps, but I thought I would post it anyway.

Mark
Reply With Quote
  #5  
Old 1st December 2003, 16:26
bernhard_frei bernhard_frei is offline
Junior Member
 
Join Date: Oct 2003
Posts: 5
bernhard_frei is on a distinguished road
Baan: B40c4 - DB: Oracle 8i - OS: Windows 2000
Mark

Thanks for your advice. I actually did it exactly this way and the result is still the same, i.e. all records from tdsls704, whether I join the table with tdsls045 or not. I do not think that the index of tdsls045 is corrupt, because the resulting report shows only those fields from tdsls045 filled with (the correct) values where actually a record exists that matches tdsls704.

Bernhard
Reply With Quote
  #6  
Old 1st December 2003, 17:14
BaanForever BaanForever is offline
Member
 
Join Date: Nov 2003
Location: Moscow, Russia
Posts: 43
BaanForever is on a distinguished road
Baan: IVc4 - DB: Oracle 8 - OS: Win NT
try this

If {tdsls704.orno, tdsls704.pono} - primary key try this
Code:
	select tdsls045.*, tdsls704.* 
	from tdsls045, tdsls704
	where tdsls045.cmba refers to tdsls704 UNREF SKIP
                and tdsls045.srnb = 0
	and tdsls704.akcd = "NL"
	selectdo
		days.to.del = tdsls045.ddat - tdsls704.date
		rprt_send()
	endselect
__________________
Sorry for my English.
Reply With Quote
Sponsored Links
  #7  
Old 1st December 2003, 17:19
bernhard_frei bernhard_frei is offline
Junior Member
 
Join Date: Oct 2003
Posts: 5
bernhard_frei is on a distinguished road
Baan: B40c4 - DB: Oracle 8i - OS: Windows 2000
BaanForever

Thanks for the suggestion but unfortunately the primary key of sls704 is different (it's orno, date, time, pono).

Bernhard
Reply With Quote
  #8  
Old 1st December 2003, 19:27
ulrich.fuchs's Avatar
ulrich.fuchs ulrich.fuchs is offline
Guru
 
Join Date: Aug 2001
Location: Germany
Posts: 514
ulrich.fuchs is on a distinguished road
Baan: IV, LN6.1 - DB: --- - OS: ---
I never learned what's the difference between inner joins and outer joins. Anyway, I understand you want to get the records of tdsls045 only for records in tdsls704, so if there is nothing ind sls704, nothing should be printed for an order, right?

So why not trying it this way:
Code:

select tdsls704.*
from tdsls704
where tdsls704.akcd = "NL"
selectdo
	select 	tdsls045.*,
		tccom010.*
	from 	tdsls045,
		tdsls010
	where	tdsls045._index1 = {:tdsls704.orno, :tdsls704.pono, 0}
	and	tdsls045.cuno referes to tccom010
	selectdo
		| do calculations
		rprt_send()
	endselect
endselect
__________________

Dipl. Ing. Ulrich Fuchs
- ERP consulting and software development -
Widumestra├če 18 - 44787 Bochum - Germany
mail@ulrich-fuchs.de - www.ulrich-fuchs.de
http://all-in-for-erp.com (Blog zu Infor LN))
Reply With Quote
  #9  
Old 3rd December 2003, 09:42
bernhard_frei bernhard_frei is offline
Junior Member
 
Join Date: Oct 2003
Posts: 5
bernhard_frei is on a distinguished road
Baan: B40c4 - DB: Oracle 8i - OS: Windows 2000
Ulrich

Inner joins only select the records that match the join criteria from both joined tables, outer joins select all the records from one of the joined tables, whether there's a matching record in the other table or not.

But you're right, your solution works (it's actually the other way round, so I would have to swap sls704 and sls045). But I still wonder if my problem is really a problem or Baan standard behaviour?

Bernhard
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
currencies differences not working properly trisulo Finance, Invoicing and Integration 4 5th February 2004 00:12
label not expected error heidihunter Tools Development 1 21st August 2003 11:33
Find not working in AFS pinakin AFS/DDC/OLE: Function servers 2 29th November 2002 06:43
Left/Right Outer Join in Query rvp5679 Tools Development 2 17th December 2001 20:40


All times are GMT +2. The time now is 20:52.


©2001-2017 - Baanboard.com - Baanforums.com