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 26th February 2004, 05:04
ltannous's Avatar
ltannous ltannous is offline
Senior Member
 
Join Date: Sep 2002
Location: Canada
Posts: 280
ltannous is on a distinguished road
Baan: 4c4 - DB: Oracle - OS: UNIX
How to create a select statment for the following

I have a table containing a project number / purchase order number, and I have a project number in the purchase order detail lines.
Code:
select tdpur041.* and wtpur099.*, tdpur040.*
from tdpur041, tdpur040, wtpur099
where tdpur041.dim4 inrange :proj.f and :proj.t
or wtpur099.porj inrange :proj.f and :proj.t
and wtpur099.orno = tdpur040.orno
and tdpur040.orno inrange :orno.f and :orno.t
and tdpur041.orno refers to tdpur040
This does not work using the "or"
Either statment may be true, or both may be true.
Any suggestions.
Reply With Quote
Sponsored Links
  #2  
Old 26th February 2004, 10:06
en@frrom en@frrom is offline
Guru
 
Join Date: Aug 2003
Location: Belgium
Posts: 1,219
en@frrom will become famous soon enough
Baan: B50B - DB: Oracle 8.0 - OS: Win2k AS
Hi,

Put the following part of the query in brackets.

where (tdpur041.dim4 inrange :roj.f and :roj.t
or wtpur099.porj inrange :roj.f and :roj.t)
Reply With Quote
  #3  
Old 26th February 2004, 15:24
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,904
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Posting Hint

When posting code do not forget to turn smiles off otherwise you get a lot of in the code. Just a friendly reminder. Saves on editing time.

Thanks

Mark
Reply With Quote
  #4  
Old 26th February 2004, 15:37
ltannous's Avatar
ltannous ltannous is offline
Senior Member
 
Join Date: Sep 2002
Location: Canada
Posts: 280
ltannous is on a distinguished road
Baan: 4c4 - DB: Oracle - OS: UNIX
That works, but I am only getting the data on the second option

This is what I have. I need both results to display all orders having a value that are in the range of proj.f and proj.t. Currently it is displaying the orders that are only related to the wtpur099 table.

functions:
Code:
function read.main.table()
{
	
	select	tdpur040.*, tsuno.nama:suno.nama, tccom000.*, tcmcs042.*, 
		tfacp302.*, tcmcs013.*, tcmcs002.*, tcmcs003.*, tccom001.*, 
		tcmcs011.*, tcmcs080.*, tcmcs034.*, tcmcs010.*, tcmcs046.*, 
		tpspr.nama:pspr.nama, tpstx.nama:pstx.nama, tcmcs041.*, 
		tfacp001.*, tcmcs031.*, tcmcs045.*, tdpur300.*, trtoc600.*,tdpur041.*, wtpur099.*
	from	tdpur040, tccom020 tsuno, tccom000, tcmcs042, tfacp302, 
		tcmcs013, tcmcs002, tcmcs003, tccom001, tcmcs011, tcmcs080, 
		tcmcs034, tcmcs010, tcmcs046, tccom020 tpspr, tccom020 tpstx, 
		tcmcs041, tfacp001, tcmcs031, tcmcs045, tdpur300, trtoc600, tdpur041, wtpur099
	where (tdpur040.orno inrange :orno.f and :orno.t 
		or wtpur099.ponumber inrange :orno.f and :orno.t)
	and (tdpur041.dim4 inrange :proj.f and :proj.t or 
				wtpur099.project inrange :proj.f and :proj.t)
	and 	tdpur040.suno refers to tsuno
	and	tdpur040.comp refers to tccom000
	and	tdpur040.cotp refers to tcmcs042
	and	tdpur040.ragr refers to tfacp302
	and	tdpur040.cpay refers to tcmcs013
	and	tdpur040.ccur refers to tcmcs002
	and	tdpur040.cwar refers to tcmcs003
	and	tdpur040.ccon refers to tccom001
	and	tdpur040.ccrs refers to tcmcs011
	and	tdpur040.cfrw refers to tcmcs080
	and	tdpur040.cplp refers to tcmcs034
	and	tdpur040.ccty refers to tcmcs010
	and	tdpur040.clan refers to tcmcs046
	and 	tdpur040.pspr refers to tpspr
	and 	tdpur040.pstx refers to tpstx
	and	tdpur040.cdec refers to tcmcs041
	and	tdpur040.cfsg refers to tfacp001
	and	tdpur040.cbrn refers to tcmcs031
	and	tdpur040.creg refers to tcmcs045
	and	tdpur040.prno refers to tdpur300
	and	tdpur040.ctrj refers to trtoc600
	and     tdpur041.orno refers to tdpur040 
	and     wtpur099.ponumber = tdpur041.orno **When I leave this statment out, it displays every purchase order in the system ***
	order by tdpur040._index1
	selectdo
			if tdpur040.ccur <> "CAD"
			then tdpur041.amta = tdpur041.amta * tdpur040.ratp
			endif
		rprt_send()
	endselect
}
Reply With Quote
  #5  
Old 26th February 2004, 16:19
en@frrom en@frrom is offline
Guru
 
Join Date: Aug 2003
Location: Belgium
Posts: 1,219
en@frrom will become famous soon enough
Baan: B50B - DB: Oracle 8.0 - OS: Win2k AS
Quote:
This is what I have. I need both results to display all orders having a value that are in the range of proj.f and proj.t. Currently it is displaying the orders that are only related to the wtpur099 table.
I'm not sure if I understand you well. But of course the line 'and wtpur099.ponumber = tdpur041.orno' causes you to get only those records from tdpur041 that exist also in wtpur099.

Please explain clearly what you want to achieve, and what the problems are.


Regards,

En.
Reply With Quote
  #6  
Old 27th February 2004, 05:39
ltannous's Avatar
ltannous ltannous is offline
Senior Member
 
Join Date: Sep 2002
Location: Canada
Posts: 280
ltannous is on a distinguished road
Baan: 4c4 - DB: Oracle - OS: UNIX
I needed the data based on both scenerios

I needed to get data from purchase table where tdpur041.dim4 is in a range of a value entered on the form (proj.f and proj,t). Then I also need to get data from purchase table based on the values in my other table wtpur099, which contains a purchase order number and project number(proj.f and proj.t) which is also on the form as a range
Reply With Quote
  #7  
Old 27th February 2004, 10:56
en@frrom en@frrom is offline
Guru
 
Join Date: Aug 2003
Location: Belgium
Posts: 1,219
en@frrom will become famous soon enough
Baan: B50B - DB: Oracle 8.0 - OS: Win2k AS
Oh ok, I think now I get it. You want to select 2 ranges from tdpur041. 1) all records from tdpur041 where tdpur041.dim4 inrange :proj.f and :proj.t and 2) all records also from tdpur041 where for which there is a record available in wtpur099. Is this correct?

Then you should try using the EXISTS condition, something like this:
Code:
where	((tdpur040.orno inrange :orno.f and :orno.t or wtpur099.ponumber inrange :orno.f and :orno.t)
	or (EXISTS (select	wtpur099.* 
		    from	wtpur099 
		    where	wtpur099.project inrange :proj.f and :proj.t)))

You will have to complete the syntax according to your needs, but for the basic idea, I hope this helps...

Good luck!

En.
Reply With Quote
  #8  
Old 27th February 2004, 20:02
ltannous's Avatar
ltannous ltannous is offline
Senior Member
 
Join Date: Sep 2002
Location: Canada
Posts: 280
ltannous is on a distinguished road
Baan: 4c4 - DB: Oracle - OS: UNIX
Thanks for your help

Thanks for your help
Reply With Quote
  #9  
Old 1st March 2004, 07:53
en@frrom en@frrom is offline
Guru
 
Join Date: Aug 2003
Location: Belgium
Posts: 1,219
en@frrom will become famous soon enough
Baan: B50B - DB: Oracle 8.0 - OS: Win2k AS
Did you manage witht he code I suggested? Does everything work now as desired?
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
Select problem.question (like statement) ssbaan Tools Development 9 17th October 2003 16:24
problem with select statement in c4 or portingset ? PV Ramone Tools Development 1 19th April 2002 00:20
? on select statement bmoore Tools Development 1 9th January 2002 17:19


All times are GMT +2. The time now is 09:33.


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