Hello. I'm trying to concatenate two values together. I'm querying
Pervasive SQL 2000i SP4 as supplied with Timberline 8.3 through the
supplied ODBC interface.

I've tried:

SELECT Cost_Code + Description AS col1 from MASTER_JCM_COST_CODE

but was given the error:

DBD::ODBC::db prepare failed: [TimberlineODBC][TimberlineODBCEngine
ODBC Driver] Collate Sequence Mismatch. (SQL-HY000)
[TimberlineODBC][TimberlineODBCEngine ODBC Driver]Incompatible types in
expression. (SQL-HY000) [TimberlineODBC][TimberlineODBCEngine ODBC
Driver]Error in expression: %sCost_Code + Description (SQL-HY000)(DBD:
st_prepare/SQLPrepare err=-1) at csvgen.pl line 67.

I've alse tried:

SELECT CONVERT(Cost_Code, SQL_VARCHAR) + CONVERT(Description,
SQL_VARCHAR) AS col1 from MASTER_JCM_COST_CODE

Oddly enough, concatenation works fine when using the same column
twice:

SELECT Cost_Code + Cost_Code AS col1 from MASTER_JCM_COST_CODE
SELECT Description + Description AS col1 from MASTER_JCM_COST_CODE

What am I doing wrong? Is it even possible to concatenate two
different columns together as you would in MS SQL Server?

Thanks.
- Eli Tucker

It should be possible to do what you want, and you are indeed using the
correct code to do so. However, you are probably seeing an error with
these two fields specifically.

The database has likely been defined with something called an
"Alternate Collating Sequence". The ACS can be used to change the sort
order of bytes in the field. Apparently, the two fields that you are
trying to access use two different ACS'es, and you cannot join them
together, since the system will have no way to properly handle the sort.

My suggestion would have been to convert the values to VARCHAR -- which
you have already tried.

I'm afraid that the problem is therefore squarely on Timberline's
shoulders -- either they need to address this problem in their ODBC
driver, or they need to alter the database ACS information to use the
same ACS for each field in their datbase.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Coming Soon: Pervasive DataExchange Training!





Eli wrote:

Thanks, Bill. Unfortunately I don't see this going very far with
Timberline Customer Support, so I'm reluctant to put the effort in to
try. It will probably be easier for me to create a program that
concatenates the two fields together.

Do you know of any free 3rd party software that will connect with
Pervasive and allows you to do this kind of thing and allows you to
output your results to a CSV (comma separated values) file?

Thanks again for your time. Much appreciated.

- Eli


Bill Bach wrote:

Not free, unfortunately.

Out SQLExec command-line based SQL command processor is fairly
inexpensive, though. It can output data in a variety of formats,
including CSV.

A more powerful utility would be Pervasive's Data Integrator, but this
would be cost-effective only if you had several other integration
projects than the one you've mentioned...
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive.SQL Service & Support - July 19-22 ***
*** Chicago: Pervasive DataExchange Class - July 27-29 ***

Eli wrote: