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 24th May 2017, 05:45
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,287
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
Appending a string to column value in qptool
Baan: Other/Unknown
C/S: None/Unknown

Hi,

Is it possible to append a string to a column value in qptool query.

e.g

Code:
select jsta from ttaad500
The above query will output as -

Code:
1
The intended output is -

Code:
Status 1
I want to append the jsta value with a string "Status".

I am not sure if it's possible using qptool. If yes, any ideas?
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
Reply With Quote
Sponsored Links
  #2  
Old 24th May 2017, 08:50
Ajesh's Avatar
Ajesh Ajesh is offline
Guru
 
Join Date: Feb 2009
Posts: 444
Ajesh is on a distinguished road
Baan: LN 10.4 - DB: Oracle - OS: HP-Unix
What OS this is based on? If it is a Unix server then you can write a shell script which calls the qptool and then you can append its output with status and produce the final output
Reply With Quote
  #3  
Old 24th May 2017, 09:24
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,287
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
Hi Ajesh,

I have a couple of ideas to achieve it including this one i.e. appending the string after the final output is generated. The second idea is to create another table with the index field same as ttaad500 and another field with description where the value will be "Status", joining the two tables in the query.

These will work, but just wanted to check if there is another way to simply append the value in query. I haven't found it yet and not sure if there is any way.
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
Reply With Quote
  #4  
Old 24th May 2017, 10:04
JaapJD's Avatar
JaapJD JaapJD is offline
Guru
 
Join Date: Sep 2002
Location: Netherlands
Posts: 728
JaapJD will become famous soon enoughJaapJD will become famous soon enough
Baan: BaanIV, BaanERP, ERP LN 6.1 - DB: Oracle, SQL Server - OS: Unix, Windows
Use bsql or bsql6.2:
Code:
bsql[6.2] -q "select 'Status ' & cast(jsta as varchar(1)) from ttaad500" -c<company>
Reply With Quote
  #5  
Old 24th May 2017, 18:14
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,287
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
Hi Jaap,

I tried it but gives this -

Quote:
SqlState=42I00, 'Unexpected token 'varchar'', NativeError=302, Line=1
Any ideas?
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
Reply With Quote
  #6  
Old 25th May 2017, 10:12
oirfeodent's Avatar
oirfeodent oirfeodent is offline
Member
 
Join Date: Jun 2016
Posts: 48
oirfeodent is on a distinguished road
Baan: Baan - DB: DB - OS: OS
Quote:
Originally Posted by bhushanchanda View Post
Hi Jaap,

I tried it but gives this -



Any ideas?
The bsql6.2 works without any issues with Oracle DB.
May be you are working with SQL server;

The cast functions could be specific to the DB.

Regards,
Reply With Quote
  #7  
Old 25th May 2017, 11:08
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,287
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
Hi oirfeodent,

I suspected that. What could be the other possibility? Using char? Or SQL server has a completely different syntax? I've checked the syntax and I guess, it should have worked on SQL server as well.
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
Reply With Quote
  #8  
Old 26th May 2017, 08:31
oirfeodent's Avatar
oirfeodent oirfeodent is offline
Member
 
Join Date: Jun 2016
Posts: 48
oirfeodent is on a distinguished road
Baan: Baan - DB: DB - OS: OS
Quote:
Originally Posted by bhushanchanda View Post
Hi oirfeodent,

I suspected that. What could be the other possibility? Using char? Or SQL server has a completely different syntax? I've checked the syntax and I guess, it should have worked on SQL server as well.
Hi Bhushan,
I believe the cast function itself has no issues, as they should be part of some standards all the DB's follow. I am more concerned about the concatenation part, as they would not be part of any PL/SQL standards. (I am making contradicting statement here than to my previous post... as I did not think on this lines yesterday... Sorry).

I have no experience with SQL server and dont have the Baan + SQL server system combination to try this.
However the below link talks about concatenation operation with a '+' operator.
https://docs.microsoft.com/en-us/sql...n-transact-sql
Can you try this and feedback the results?

Code:
bsql[6.2] -q "select 'Status ' + cast(jsta as varchar(1)) from ttaad500" -c<company>
Regards,
Reply With Quote
  #9  
Old 29th May 2017, 11:36
JaapJD's Avatar
JaapJD JaapJD is offline
Guru
 
Join Date: Sep 2002
Location: Netherlands
Posts: 728
JaapJD will become famous soon enoughJaapJD will become famous soon enough
Baan: BaanIV, BaanERP, ERP LN 6.1 - DB: Oracle, SQL Server - OS: Unix, Windows
Bhushan, are you sure you did not forget the 'as' in your statement?
Reply With Quote
  #10  
Old 29th May 2017, 12:32
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,287
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
Yeah, pretty sure I used the "as" clause. Double checked again.
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
Reply With Quote
  #11  
Old 30th May 2017, 11:34
oirfeodent's Avatar
oirfeodent oirfeodent is offline
Member
 
Join Date: Jun 2016
Posts: 48
oirfeodent is on a distinguished road
Baan: Baan - DB: DB - OS: OS
Quote:
Originally Posted by bhushanchanda View Post
Hi oirfeodent,

I suspected that. What could be the other possibility? Using char? Or SQL server has a completely different syntax? I've checked the syntax and I guess, it should have worked on SQL server as well.
I got a sample query run on a temp SQL Server DB.
The + is the correct concat symbol. The below command works for SQL server.
Quote:
bsql[6.2] -q "select 'Status ' + cast(jsta as varchar(1)) from ttaad500" -c<company>

Last edited by oirfeodent : 30th May 2017 at 11:42.
Reply With Quote
  #12  
Old 30th May 2017, 15:34
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,287
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
Seems quite odd. It throws the same error yet.

May be due to the Porting Set version?
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
Reply With Quote
  #13  
Old 6th June 2017, 07:56
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,287
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
So, all the following have been failed, which I assume is due to some missing solution or porting set version,

Code:
bsql[6.2] -q "select 'Status ' + cast(jsta as varchar(1)) from ttaad500" -c<company>

Code:
bsql[6.2] -q "select 'Status ' & cast(jsta as varchar(1)) from ttaad500" -c<company>

Code:
bsql[6.2] -q "select 'Status ' & cast(jsta as nvarchar(1)) from ttaad500" -c<company>

Code:
bsql[6.2] -q "select 'Status ' & cast(jsta as char(1)) from ttaad500" -c<company>

Code:
bsql[6.2] -q "select 'Status ' & cast(jsta as string) from ttaad500" -c<company>

I am going ahead with the following command, which works but return's ENUM description instead of value.

Code:
bsql[6.2] -q "select 'Status ' + ENUM_DESCRIPTION(jsta) from ttaad500" -c<company>

In the meantime, if someone or myself comes up with an alternative please post the solution, till then this will work.
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
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 14:00
Writing Export Conditions ian_j_albert Tools Development 4 22nd July 2010 07:30
Help regarding tools session rahul.kolhe22 Tools Development 4 22nd January 2010 13:31
problems with stpapi.zoom.option Finnigan AFS/DDC/OLE: Function servers 5 3rd November 2009 14:04
File Browser Bogdan Tools Development 52 7th January 2004 08:17


All times are GMT +2. The time now is 18:26.


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