Baanboard.com

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

User login

Frontpage Sponsor

Main

Poll
Which method you mostly follow during Infor Product implementation?
Waterfall
43%
Agile/Scrum
43%
Lean Development
0%
RAPID
14%
Total votes: 7

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 12th May 2020, 18:04
sdietrich's Avatar
sdietrich sdietrich is offline
Junior Member
 
Join Date: Feb 2020
Location: France
Posts: 7
sdietrich is on a distinguished road
Baan: B40 c4 SP12 - DB: Informix - OS: Linux
Complex SQL Query
Baan: Baan IVc4
C/S: None/Unknown

Hello everybody,

I have some issues when trying to do a complex SQL query and I would like you help on creating the following query in BaaN: SELECT a.t_csig
(select b.t_aitc from tiitm012 AS b where b.t_citt = 'PLC' and t_cntr = '' and b.t_item = a.t_item) AS Life_Cycle
(select c.t_aitc from tiitm012 AS c where c.t_citt = 'KBE' and t_cntr = '' and c.t_item = a.t_item) AS Kanban
(select d.t_sipp from ticpr170 AS d where d.t_item = d.t_item and d.t_cpcc = '001') As Simulated_Price
FROM tiitm001 AS a
1) It indeed doesn't seem to accept the SKIP keyword of the refers which is very annoying. I have tried tiitm001 refers to ticpr170.item, tiitm001.item refers to ticpr170 and tiitm001 refers to ticpr170 but either get no data or an index error (even when adding cpcc and cntr fields of the key in the WHERE)…
2) How can I make complex queries like I would do with a WITH out of BaaN in order to have two fields with JOINS with different search criteria on the same table (Life_Cycle and Kanban)?
3) How can I add more fields in than 15 fields to the query? The idea is to extract data in excel but the system complains because the form is full… Can we add some with the code or cheat the system but updating the generated report even if it complains?
4) Selection seems only possible with FROM TO functions on a field. Did you manage to do more complex things in order to add select a given list of items (flat file, etc.)?
Thanks in advance for your help because I'm really stuck.
Best regards,
Stephane
Reply With Quote
  #2  
Old 13th May 2020, 11:12
günther günther is offline
Guru
 
Join Date: Jan 2002
Location: Ehingen, Germany
Posts: 607
günther is on a distinguished road
Baan: IVc4 - DB: Informix - OS: HP-UX
Hello Stephane.

To be honest, I don't understand what you are trying to do. You don't want to write a Baan program, right?

Since I'm also on Baan IV and Informix (but HP-UX instead of Linux), maybe that might help you:

Code:
select t_aitc, t_item 
from tiitm012
where t_citt = 'PLC' and t_cntr = ''
into temp b with no log;

create index i_b1 on b (t_item); -- maybe create unique index
...
Next question: Are you using level1 or level2? I,e, do you have those "hash1", ... fields? If yes, you sould use them!

Regards
Günther
Reply With Quote
  #3  
Old 13th May 2020, 12:04
sdietrich's Avatar
sdietrich sdietrich is offline
Junior Member
 
Join Date: Feb 2020
Location: France
Posts: 7
sdietrich is on a distinguished road
Baan: B40 c4 SP12 - DB: Informix - OS: Linux
Hello Günther,
I'm sorry but it doesn't seem to work. I am using SQL queries that can be found in BaaN IV Tools/SQL Queries, not a standard BaaN script or direct SQL server. The idea is here to provide to our users predefined queries with an export in excel for some specific needs they have quite often like items with multiple fields.
I have then tried to put a second query in the tool with an INTO to define a temporary name or even a With kanban(item) AS (select aitc, item from tiitm012 where citt = "KBE" and cntr = "") but it complains on INTO or second SELECT in same Query.
Does anybody have another clue?
Thanks in advance.
Best regards,
Stephane
Reply With Quote
  #4  
Old 13th May 2020, 12:39
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,440
bhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura about
Baan: LN FP 1-9, 10.4, a little bit of Baan IV - DB: SQL Server 2008, Oracle - OS: Windows Server 2008 R2, Unix
If you are using Baan Queries, I am not sure it can handle those kind of complex queries. But, you can definitely use report script to do a lot of things. So, whatever complex logic you want to write, you can utilize report script to do that. Same goes with adding fields. In your report script, you can write logic to send data to a .CSV file directly instead of limited 255 char restricted classic report.

So, your main query will be a selection on tiitm001 and your report script will fetch values from other tables - tiitm012 and ticpr170.
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
Reply With Quote
  #5  
Old 13th May 2020, 14:59
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,433
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Personally we never did give users access to queries. But you can do a lot with the reports and report scripts like Bhushan mentioned. For example you can have different layouts in the report where you pass just the item, but then in the before layout you can get different data from other tables.

What we did was add reports to the reports sessions. We would clone the standard baan report, then go in and modify it to only have what fields we wanted in a format acceptable to excel. So our report might just have column headings, field information before it. For excel we would only print the heading once. And if you play with the settings enough you can even eliminate blank lines.

We even took this one step farther - we created a device that would look up a report ,download it and then run a macros to format the reports, do charts, sum data, etc. But there are tools like NAZDAQ and others which will also do this for you also.
__________________
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
Sponsored Links
  #6  
Old 13th May 2020, 15:09
sdietrich's Avatar
sdietrich sdietrich is offline
Junior Member
 
Join Date: Feb 2020
Location: France
Posts: 7
sdietrich is on a distinguished road
Baan: B40 c4 SP12 - DB: Informix - OS: Linux
I created a report script but am currently not able to display the content of the records in the after.receive.data section. My idea would be to append to each record some fields from titm001, tiitm012 and ticpr170 but spool.pr.line always seems to be empty. What should I use to access data?
Regards,
Stephane
Reply With Quote
  #7  
Old 13th May 2020, 17:40
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,440
bhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura about
Baan: LN FP 1-9, 10.4, a little bit of Baan IV - DB: SQL Server 2008, Oracle - OS: Windows Server 2008 R2, Unix
A simple report will have a Header and Detail layout. Both have a # associated starting from 1.

Records will be printed in detail layout. So, you will do all the appending inside your detail.1: section

Sample report script

Code:
detail.1:
before.layout:
        do..stuff..
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
Reply With Quote
  #8  
Old 13th May 2020, 17:49
sdietrich's Avatar
sdietrich sdietrich is offline
Junior Member
 
Join Date: Feb 2020
Location: France
Posts: 7
sdietrich is on a distinguished road
Baan: B40 c4 SP12 - DB: Informix - OS: Linux
Indeed I managed this afternoon to enrich records with detail.1: before.layout: r_append_line(230, "", "|Field1|Field2" , "", REP_FIELD).
It is however not that easy with the header of the columns that are extracted. I think catch it with header.1 but if I try to put my append it modifies the date and page information that is normally not displayed. The "real" header seem to be at lattr.layout.row = 5 of the header but I can't catch it with an if, a definition of the variable or any other trick I tried…
Regards,
Stephane
Reply With Quote
  #9  
Old 14th May 2020, 14:02
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,433
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
There is a lot of information on the board - sample code i used is below for after.receive.data. Now what you probably can't see is there a "stx" character embedded in the "stx" and on the -tstx on the sort. A consultant helped me write this back in 2000. There are also posts http://www.baanboard.com/baanboard/s...ead.php?t=9090. I think Gunther found a better way to do stuff like this.
Code:
after.receive.data:
	if(first=0) then
		r.o = seq.open (r.datafile$ & ".tmp","w")
		r.reccount = 0
		while e = 0
			select tipgc001.cplb
			from tipgc001
			where tipgc001._index1 = {:tisfc001.ccot.a,:tisfc001.mitm}
			selectdo
			endselect
			l = seq.puts(concat$("",
				tipgc001.cplb,
				tisfc001.pdno,
				tisfc001.ccot.a,
				tisfc001.mitm,
				error.message,r.reccount),r.o)
			r.reccount = r.reccount + 1
			r.read.seq.file()
		endwhile
		l = seq.flush (r.o)
		l = seq.close (r.lfn)
		l = seq.close (r.o)
		l = run.baan.prog( "sort","-t +0n -1 +1n -2 " & r.datafile$ & ".tmp -o " & r.datafile$ & ".tmp", RP_WAIT)
		r.lfn = seq.open (r.datafile$ & ".tmp" ,"a+")
		r.read.seq.file()
		first = 1
	endif

after.program:
	l = seq.unlink(r.datafile$ & ".tmp")
__________________
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
  #10  
Old 14th May 2020, 15:28
sdietrich's Avatar
sdietrich sdietrich is offline
Junior Member
 
Join Date: Feb 2020
Location: France
Posts: 7
sdietrich is on a distinguished road
Baan: B40 c4 SP12 - DB: Informix - OS: Linux
Thanks a lot for all your feedbacks, here is finally the easier way I found to replace simply my header:

header.1:
before.layout:
lattr.print = 0
r_append_line(1, "", "Article|Libelle|…..", "", REP_SPECIAL)
r_spool_line()
I however keep in mind for a newt time the very good way to replace a function by another but sql queries didn't seem to have a very usable function to replace.
Regards,
Stephane
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
baan C4 SQL Query Procedure brgarg Tools Development 1 16th August 2012 12:53
sql query srikarthy Tools Development 6 25th August 2010 09:46
Tips for Multi-company / Calculated items in MS SQL query Hitesh Shah Blogs and Web links 0 23rd May 2009 12:18
Strange error while importing tables: Can not create index 9999 eppesuiG Tools Administration & Installation 8 17th March 2008 04:35


All times are GMT +2. The time now is 16:07.


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