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
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 8th August 2002, 18:06
zelman's Avatar
zelman zelman is offline
Newbie
 
Join Date: Aug 2002
Location: Watauga, TX
Posts: 2
zelman is on a distinguished road
Baan: 5b - DB: oracle - OS: nt
Question Easy SQL Query Question

I've created an easy SQL query that has a form with from/to required inputs. Does anybody know how I can retain these from/to inputs so that they can be printed on the header of the report?

This is my first posting, so I hope everything come out alright!
Reply With Quote
  #2  
Old 8th August 2002, 18:59
ssellens's Avatar
ssellens ssellens is offline
Member
 
Join Date: Oct 2001
Posts: 44
ssellens is on a distinguished road
Baan: BaanIVc4 - DB: Oracle8i Oracle 9i - OS: Solaris 8,9 & 10
Hi,

I don't think you can, we have tried here but have never got it to work. Seems once the report starts the input fields are lost.

We have re-written all our queries as sessions to do this.


Steve
__________________
Stephen J. Sellens
Unix/Baan/Oracle Administrator
Reply With Quote
  #3  
Old 8th August 2002, 19:23
steveauckly steveauckly is offline
Senior Member
 
Join Date: Sep 2001
Location: Kansas City, MO, USA
Posts: 168
steveauckly is on a distinguished road
Baan: Baan IVc4 SP26 - DB: Oracle 10g - OS: Windows XP Pro
yes you can

All you have to do is put the form field names in the "Input Fields" of the report. You then have access to them in the report layouts. Since they are on your form, they are already declared in your script as external variables, so you don't even need access to the code if you don't have it.
Reply With Quote
  #4  
Old 9th August 2002, 11:50
ssellens's Avatar
ssellens ssellens is offline
Member
 
Join Date: Oct 2001
Posts: 44
ssellens is on a distinguished road
Baan: BaanIVc4 - DB: Oracle8i Oracle 9i - OS: Solaris 8,9 & 10
Hi,

If you get steveauckly's advice to work could you let me know, our programmer tried that and it did not work for him, would be nice to know if it was a problem with his coding or not.

Steve
__________________
Stephen J. Sellens
Unix/Baan/Oracle Administrator
Reply With Quote
  #5  
Old 14th August 2002, 13:42
pansa90's Avatar
pansa90 pansa90 is offline
Unconfirmed User
 
Join Date: Mar 2002
Location: U.K
Posts: 10
pansa90 is on a distinguished road
Thumbs down

Hi,

When i tried adding the input fields in the reports for my form fields, i got an error message "Field Not Defined".

On just declaring the field e.g tcfin100.trdt.f as extern in my report script, i did not get any values which i had enterd at the time of running the query.

Steve would appreciate if you could send us the exact syntax

Regards

Saurabh

my sample SQL:
Code:
select
tcfin100.fitr,         | Financial Transaction
tcfin100.fprd,         | Period
tcfin100.fyer,         | Year
tcfin100.item,         | Item
tcfin100.nuni,         | Number of Units
tcfin100.orno,         | Order Number
tcfin100.pono,         | Position
tcfin100.sern,         | Sequence No.
tcfin100.trdt,         | Transaction Date
tcfin100.tror          | Transaction Origin
from
tcfin100              | WIP and Inventory Transactions (PCS/SFC)
where
tcfin100.trdt >= tcfin100.trdt.f and
tcfin100.trdt <= tcfin100.trdt.t and
tcfin100.fitr = tcfitr.receipt and
tcfin100.tror = tctror.prd
Reply With Quote
  #6  
Old 14th August 2002, 14:21
FransG's Avatar
FransG FransG is offline
Member
 
Join Date: May 2002
Posts: 67
FransG is on a distinguished road
Baan: BaanERP - DB: Oracle - OS: Unix
Exclamation Check your report Input Fields

Hi Saurabh,

Have you defined both fields (i.e. tcfin100.trdt.f and tcfin100.trdt.t) as input fields at your report?

Check your defined Input fields...
__________________
Regards,
Frans
Reply With Quote
  #7  
Old 14th August 2002, 14:27
pansa90's Avatar
pansa90 pansa90 is offline
Unconfirmed User
 
Join Date: Mar 2002
Location: U.K
Posts: 10
pansa90 is on a distinguished road
Unable to define Input Field

Hi Frans

When i try to define the field "tcfin100.trdt.f" as an Input field for the report, it comes back with an error "Field Not Found". This i think is because its trying to
find a field called trdt.f
in the table tcfin100


I have already defined the field tcfinn100.trdt as a report field

regards

Saurabh
Reply With Quote
  #8  
Old 14th August 2002, 14:48
FransG's Avatar
FransG FransG is offline
Member
 
Join Date: May 2002
Posts: 67
FransG is on a distinguished road
Baan: BaanERP - DB: Oracle - OS: Unix
You're right!

Hi,

After checking a B50b system I see your point. Apparently the tools check on table fields. Because tcfin100.trdt.f looks like a table field it is not accepted by the tools. That's why you get the blocking error message.
If you rename your form fields to trans.date.f and trans.date.t then after modifying your query and adding them to the report you'll succeed.
I don't know why the tools fire this blocking message instead of just a warning to prevent typo's.
__________________
Regards,
Frans
Reply With Quote
  #9  
Old 14th August 2002, 16:19
pansa90's Avatar
pansa90 pansa90 is offline
Unconfirmed User
 
Join Date: Mar 2002
Location: U.K
Posts: 10
pansa90 is on a distinguished road
Thumbs down Sorry, no can do

Hi,

You are not allowed to change the fields to any other name aprt from the <field_name>.f and <field_name>.t, as Easy SQL does not recognise it.

If i change the field name i get an SQL execution error "field not found". I guess this is becuase when you use EASY SQL, query by field, and define it to be on the form, it put the two fields on its own.

e;g field tcfin100.trdt -- In Form YES
then the SQL puts

tcfin100.trdt >= tcfin100.trdt.f and
tcfin100.trdt <= tcfin100.trdt.t


Regards

Saurabh
Reply With Quote
  #10  
Old 4th December 2008, 08:25
BaaNovva's Avatar
BaaNovva BaaNovva is offline
Member
 
Join Date: Mar 2003
Location: Bangalore
Posts: 48
BaaNovva is on a distinguished road
Baan: BaanIV, BaanV - DB: Oracle,Sybase - OS: Win2K
Offcourse you can print EasySQL generated form fields in report...

Easy SQL generates form fields given as ranges(ex."tcfin100.trdt.f")
and calculated fields. Just pick up this field name and place it in your report.
Do not attempt to define this generated field anywhere because it
could be internally defined when the form gets generated while running
the query.

I know this is ok because in SQL query one of my fields was
to be by sum(). The output was directed to generated form
variable "float0". I placed "float0" in the report and its working
perfectly. So, just place the generated names in the report
and compile. This must work.
Reply With Quote
  #11  
Old 6th October 2009, 19:05
abaninas abaninas is offline
Member
 
Join Date: Nov 2008
Posts: 45
abaninas is on a distinguished road
Baan: BaanIV, ERPLN - DB: SQL Database - OS: Windows server 2003
Can I understand from the above that there is no a sloution to get the form fields printed.

Please advice
Reply With Quote
  #12  
Old 6th October 2009, 19:53
shah_bs's Avatar
shah_bs shah_bs is offline
Guru
 
Join Date: Jan 2002
Location: Lewisville, Texas
Posts: 387
shah_bs is on a distinguished road
Baan: BAAN IVc3 with A&D2.2b - DB: ORACLE 9 - OS: HPUX
I have not tried this myself, but here is an idea at a conceptual level that you can try.

Because of the way EasySQL works, it does not pass any form information to the report. So, maybe, the only way to get what you want is in the AFTER.REPORT layout.

- First, make sure the sort sequence in the report is according to what you require for the from and to fields
- Next, in the report, create a script to declare the from and to variables that you want to print.
- Also declare a record count.
- Increment the record count in the detail layout. In the detail layout, if the record count is 1, save the 'From' values from the table fields. This should give you all the 'lowest' values - the 'From' field values on the form.
- in the after.report, save the 'To' values in the before.layout - at this point you are at the last record being printed, so this should represent the 'To' values.
- Assuming you have created the after.report layout and put all the 'From' and 'To' fields in, you should get the output you need.

Note again that the output values of the 'From' and 'To' can change if you change the sort order of the report (I do not think the sort order of the select statement will matter).
Reply With Quote
  #13  
Old 6th October 2009, 19:56
abaninas abaninas is offline
Member
 
Join Date: Nov 2008
Posts: 45
abaninas is on a distinguished road
Baan: BaanIV, ERPLN - DB: SQL Database - OS: Windows server 2003
Can you please give me the code.

You know, I cant define the form fileds as they defined in the SQL query.

Please advice
Reply With Quote
  #14  
Old 12th August 2010, 21:41
GabrielVA's Avatar
GabrielVA GabrielVA is offline
Member
 
Join Date: Aug 2010
Location: Smith Mountain Lake, VA
Posts: 66
GabrielVA is on a distinguished road
Baan: iv - DB: n/a - OS: n/a
Easy SQL Question

Hi Guys,

Newbie for Baan here so bear with me. Im trying to do a EasySql Query and Im tyring to show the specific vaule for a field withint a table. Example

Select

tfcmg100.bank, Bank Relation
tfcmg100.chst Check Status

From

tfcmg100 Check master

Where

tfcmg100.chst = 'PAID'


This is not correct when running the query. Whats the correct syntax for this??

Thanks

GabrielVA

Last edited by GabrielVA : 12th August 2010 at 21:51.
Reply With Quote
  #15  
Old 13th August 2010, 06:07
lorry.lu lorry.lu is offline
Junior Member
 
Join Date: Dec 2004
Location: Shanghai, China
Posts: 19
lorry.lu is on a distinguished road
Baan: BaaNIVc4 - DB: Oracle - OS: Unix
Hello,

Please try this:


Select

tfcmg100.bank, |Bank Relation
tfcmg100.chst |Check Status

From

tfcmg100 |Check master

Where

tfcmg100.chst = tfcmg.chst.paid
Reply With Quote
Sponsored Links
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
sql query srikarthy Tools Development 6 25th August 2010 10:46


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


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