Baanboard.com

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

User login

Frontpage Sponsor

Main

Google search


Poll
How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
17%
200 - 500 GB
30%
500 - 800 GB
3%
800 - 1200 GB
7%
1200 - 1500 GB
10%
1500 - 2000 GB
13%
> 2000 GB
20%
Total votes: 30

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 27th September 2018, 07:07
tmannais's Avatar
tmannais tmannais is offline
Member
 
Join Date: Jul 2017
Posts: 98
tmannais is on a distinguished road
Baan: LN 10.5 - DB: MySQL - OS: Windows
Convert String to Long in Select before using Group By
Baan: ERP LN 6.1 FP11 (Infor ERP 10.5)
C/S: Both

Hi,

I want to query some data using Select like so:

Code:
select	tfgld102.leac, tfgld102.dbcr
from	tfgld102
where	tfgld102.cono between :cono.f and :cono.t
and		tfgld102.btno between :btno.f and :btno.t
and		tfgld102.ttyp between :ttyp.f and :ttyp.t
and		tfgld102.year = :year.f
group by	tfgld102.leac, tfgld102.dbcr
order by	tfgld102.dbcr, tfgld102.leac
selectdo
	message(enum.descr$("tfgld.dbcr", tfgld102.dbcr ) & " " & tfgld102.leac)
endselect
The problem is that while I'm sorting "tfgld102.leac", its domain is basically String, which I want to sort it as Long because it is a string containing numbers.

The result is now something like this:
1
10
11
115
118
1196
2
20
21
211
2123

I want it to be like:
1
2
10
11
20
21
. . .

I tried putting
val(tfgld102.leac) in the Order By but it gives me
Code:
Unexpected token '(' (error 302)
on compile.

How can I achieve this?

Regards,
Thana

Last edited by tmannais : 27th September 2018 at 07:24.
Reply With Quote
  #2  
Old 27th September 2018, 08:51
giggty's Avatar
giggty giggty is offline
Senior Member
 
Join Date: May 2017
Posts: 107
giggty is on a distinguished road
Baan: Infor ERP 10.6 - DB: MSSQL Server 2008 R2 - OS: Windows Server 2012
Try to add "val(tfgld102.leac) as vleac" to the select list and order by vleac.
Reply With Quote
Sponsored Links
  #3  
Old 27th September 2018, 09:01
tmannais's Avatar
tmannais tmannais is offline
Member
 
Join Date: Jul 2017
Posts: 98
tmannais is on a distinguished road
Baan: LN 10.5 - DB: MySQL - OS: Windows
I tried changing it like what you suggested.

Code:
select	tfgld102.dbcr, val(tfgld102.leac) as vleac
from	tfgld102
where	tfgld102.cono between :cono.f and :cono.t
and		tfgld102.btno between :btno.f and :btno.t
and		tfgld102.ttyp between :ttyp.f and :ttyp.t
and		tfgld102.year = :year.f
group by	tfgld102.leac, tfgld102.dbcr
order by	tfgld102.dbcr, vleac
doesn't work. It gives me this error on compile.
"Error SQL: SQLState 42I03: Unknown column 'val' (error 302)"

I also tried changing the same field in Group By but it seems like it doesn't recognize the vleac.
Reply With Quote
  #4  
Old 27th September 2018, 09:27
giggty's Avatar
giggty giggty is offline
Senior Member
 
Join Date: May 2017
Posts: 107
giggty is on a distinguished road
Baan: Infor ERP 10.6 - DB: MSSQL Server 2008 R2 - OS: Windows Server 2012
Haha, right, I forgot Infor SQL does not support it. Try this:
Code:
long vleac
select	tfgld102.leac, tfgld102.dbcr, CAST(tfgld102.leac AS INTEGER) :vleac
from	tfgld102
where	tfgld102.cono between :cono.f and :cono.t
and		tfgld102.btno between :btno.f and :btno.t
and		tfgld102.ttyp between :ttyp.f and :ttyp.t
and		tfgld102.year = :year.f
group by	tfgld102.leac, tfgld102.dbcr
order by	tfgld102.dbcr, 3
selectdo
	message(enum.descr$("tfgld.dbcr", tfgld102.dbcr ) & " " & tfgld102.leac)
endselect
Reply With Quote
  #5  
Old 27th September 2018, 09:37
tmannais's Avatar
tmannais tmannais is offline
Member
 
Join Date: Jul 2017
Posts: 98
tmannais is on a distinguished road
Baan: LN 10.5 - DB: MySQL - OS: Windows
Thank you giggty. Your solution works like a charm!
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
DEV: Export any Baan table to (proper) Excel Francesco Code & Utilities 27 2nd September 2011 15:00
Date casting issue niralibaan Tools Development 2 22nd August 2010 19:26
Help regarding tools session rahul.kolhe22 Tools Development 4 22nd January 2010 14:31
xml question steveauckly Tools Development 4 27th July 2004 11:41
File Browser Bogdan Tools Development 52 7th January 2004 09:17


All times are GMT +2. The time now is 21:49.


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