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 29th May 2017, 15:25
amator's Avatar
amator amator is offline
Junior Member
 
Join Date: Aug 2016
Posts: 26
amator is on a distinguished road
Baan: Baan 4 - DB: SQl Server 2012 - OS: Windows 8
SQL Query Data "Last Records"
Baan: Other/Unknown
C/S: None/Unknown

Hi Guys,

I want to select the last row in my query.

I've already tried the as set with 1 rows but it only displays 1 data only regarding if I have may types

Sample:

I have an Sales Order Type of A, B and C.

The OUPUT when I use the Order by desc is this:

C003
C002
C001
B002
B001
A004
A003
A002
A001

And then when I added as set with 1 rows it will display like this:

C003

What I need to display is like this:

A004
B002
C003

Any suggestions?

I tried the Select Max() but I'm getting an error, here is the sample query that have error:

Code:
select max(tdsls400.orno)
from tdsls400
where tdsls400.odat >= tdsls400.odat.f and
            tdsls400.odat <= tdsls400.odat.t
group by tdsls400.orno
PS: I'm using the Query Data and I'm not creating any session.

Thanks in advance,
amator
Reply With Quote
  #2  
Old 29th May 2017, 15:54
priyank29 priyank29 is offline
Junior Member
 
Join Date: Feb 2014
Posts: 7
priyank29 is on a distinguished road
Baan: ERP LN - DB: Sql Server - OS: Windows
hii...

if you are using max function use it with alias.

domain tcorno max.orno

select max(tdsls400.orno):max.orno,tdsls400.sotp
from tdsls400
where tdsls400.odat >= tdsls400.odat.f and
tdsls400.odat <= tdsls400.odat.t
group by tdsls400.sotp
selectdo
endselect
Reply With Quote
  #3  
Old 29th May 2017, 16:15
amator's Avatar
amator amator is offline
Junior Member
 
Join Date: Aug 2016
Posts: 26
amator is on a distinguished road
Baan: Baan 4 - DB: SQl Server 2012 - OS: Windows 8
I don't think that this will work

Quote:
Originally Posted by priyank29 View Post
hii...

if you are using max function use it with alias.

domain tcorno max.orno

select max(tdsls400.orno):max.orno,tdsls400.sotp
from tdsls400
where tdsls400.odat >= tdsls400.odat.f and
tdsls400.odat <= tdsls400.odat.t
group by tdsls400.sotp
selectdo
endselect
Hi I don't think this will work because I'm working with the Query Data. Only Query no declaration etc.

but thanks,
amator
Reply With Quote
  #4  
Old 30th May 2017, 23:51
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,930
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
The only thing I can think of is to do something in the report itself. Maybe create a script. Maybe something where you would sort it descending, set the first order to null in the before report event. Then on the detail before layout check to see if the first character is the same, if not print the layout and set the new first character to what ever is passed - if it is the same then set lattr.print to false. So in effect the detail layout will only print each time the first character changes - that would get you just the max number. Would probably be better in a script, but if you have to use easy sql it is possible. This is assuming you have access to the script portion of the report.
__________________
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
  #5  
Old 31st May 2017, 05:35
amator's Avatar
amator amator is offline
Junior Member
 
Join Date: Aug 2016
Posts: 26
amator is on a distinguished road
Baan: Baan 4 - DB: SQl Server 2012 - OS: Windows 8
That is right

Hi Mark,

Yes you are right, actually that is the solution for my concern "manipulate the report itself" but I'm doing the Query Data on our LIVE Server and we don't have an access to create/edit script from the back end of LIVE Server so we depend only to the auto generated report base on our Query Data. Our last resort is to do this in our 3rd Party Business Intelligence.

Thank you this is much appreciated,

Amator

Last edited by amator : 31st May 2017 at 05:36. Reason: Spelling corrections
Reply With Quote
  #6  
Old 1st June 2017, 02:03
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,930
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Yeah - we can't modify the report scripts in production either. I can't think of any other solution with just a query.
__________________
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
  #7  
Old 1st June 2017, 06:19
amator's Avatar
amator amator is offline
Junior Member
 
Join Date: Aug 2016
Posts: 26
amator is on a distinguished road
Baan: Baan 4 - DB: SQl Server 2012 - OS: Windows 8
Thumbs up Thanks

Hi Mark,

Thanks for the effort. What we are doing right now is to use our 3rd Party Business Intelligence System to provide a report.

Thank you,
amator

Last edited by amator : 1st June 2017 at 06:20. Reason: Spelling corrections
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
Debug query data Deknor Tools Development 8 30th March 2016 13:43
EASY SQL QUERY: Japanese Form Label not displayed completely. fredintaiwan Tools Development 0 4th December 2008 07:59
Strange error while importing tables: Can not create index 9999 eppesuiG Tools Administration & Installation 8 17th March 2008 05:35
Exporting SQL Query with repport hagenh Tools Administration & Installation 3 12th April 2005 14:51
Maintain Query Data query pjohns Tools Development 5 11th September 2001 09:36


All times are GMT +2. The time now is 17:24.


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