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 27th May 2017, 04:56
amator's Avatar
amator amator is offline
Junior Member
 
Join Date: Aug 2016
Posts: 20
amator is on a distinguished road
Baan: Baan 4 - DB: SQl Server 2012 - OS: Windows 8
SQL Query Table Fields Concatenation
Baan: Baan IVa
C/S: None/Unknown

Hi Guys,

How do I concatenate 2 table fields?

This is my sample query

select tdsls400.*, tcmcs050.seri, tcmcs050.ffno
from tdsls400, tcmcs050.seri
where tdsls400.odat <= tdsls400.odat.t
and tdsls400.orno = tcmcs050.seri + tcmcs050.ffno

The fields that is in bold is the one that I want to combine/concatenate.

Any suggestion how to do it properly?

OR I want to trim a field how should I do it?

For example the field tdsls400.orno = "MLF1000001" how to trim this into like this "MLF1"?

Thank you in advance,

Last edited by amator : 27th May 2017 at 05:01. Reason: Additional inquiry
Reply With Quote
Sponsored Links
  #2  
Old 27th May 2017, 10:41
bdittmar's Avatar
bdittmar bdittmar is offline
Guru
 
Join Date: Apr 2002
Location: Germany, 50.584097,8.544078
Posts: 1,639
bdittmar will become famous soon enough
Baan: 2.2/3.1/4c4/LN6.1 FP6/FP9/HiDox - DB: tbase, ms-sql7, oracle10gV1, 11g - OS: HP-UX, W2K3, SLES
Biv ? -> Ln

Hollo,
don't know what you wanna achieve.

Your profile says BaaN 4. In BIV the sales order header ist tdsls040.
You talk about tdsls400, thats sales order header in LN.
---------------------------------------------------------------------
Concat should be done with "&", but your sample query never will give a result, becaus the first free number in tcmcs050 will not be used in an existing sales order. It's the NEXT-FREE-Ordernumber used by the next maintained sales order in that numbergroup.

TRIM can be done with trim$(). Or specific values with e.g. tdsls400.orno(1;4)

Code:
Why not:

tdsls400.orno >= tdsls400.orno.f
and tdsls400.orno <= tdsls400.orno.t

tdsls400.orno.f input is "MLF1     "
tdsls400.orno.t input is "MLF1ZZZZZ"

or

tdsls400.orno(1;4) = "MLF1"
This is wrong :

from tdsls400, tcmcs050.seri
from clause -> only table

select <tablefields>
from tdsls400, tcmcs050 [<tables]
where <condition>
__________________
//Bernd

Last edited by bdittmar : 27th May 2017 at 13:24. Reason: Correct from clause
Reply With Quote
  #3  
Old 29th May 2017, 14:17
amator's Avatar
amator amator is offline
Junior Member
 
Join Date: Aug 2016
Posts: 20
amator is on a distinguished road
Baan: Baan 4 - DB: SQl Server 2012 - OS: Windows 8
Working properly

Hi,

Thanks for the reply.

I use your advice and it works now "tdsls400.orno(1;4)"

Thank you so much,
Reply With Quote
  #4  
Old 29th May 2017, 14:25
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
hiii,

You can also try this..

select tdsls400.*, tcmcs050.seri, tcmcs050.ffno
from tdsls400, tcmcs050.seri
where tdsls400.odat <= tdsls400.odat.t
and tdsls400.orno = :1
wherebind(1,tcmcs050.seri &"0000" & str$(tcmcs050.ffno))
Reply With Quote
  #5  
Old 29th May 2017, 14:28
amator's Avatar
amator amator is offline
Junior Member
 
Join Date: Aug 2016
Posts: 20
amator is on a distinguished road
Baan: Baan 4 - DB: SQl Server 2012 - OS: Windows 8
Thumbs up Thanks for the effort

Hi,

Thanks for the effort I will try this too. I'm in the middle of my work.

Thanks,
Reply With Quote
  #6  
Old 29th May 2017, 15:58
bdittmar's Avatar
bdittmar bdittmar is offline
Guru
 
Join Date: Apr 2002
Location: Germany, 50.584097,8.544078
Posts: 1,639
bdittmar will become famous soon enough
Baan: 2.2/3.1/4c4/LN6.1 FP6/FP9/HiDox - DB: tbase, ms-sql7, oracle10gV1, 11g - OS: HP-UX, W2K3, SLES
AFAIK not in easySQL !

Hello,
threater is using EasySQL !

Regards
__________________
//Bernd
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
Audit file structure baan_user Tools Development 8 4th May 2007 14:32
Can't query the ttadv380 table. walter01 Tools Development 23 17th September 2006 01:18
Tables used for MRP and PRP. BurghMan Tools Development 1 10th February 2005 14:08
CODE: Table Loader ~Vamsi Code & Utilities 3 26th February 2003 13:05
Logical Table Query pjohns Tools Administration & Installation 6 14th February 2002 11:06


All times are GMT +2. The time now is 19:00.


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