Avatar of jamessa
jamessa

asked on 

Pervasive SQL Union throwing Error

Here's a union statement I wrote:

SELECT  convert(' Type or Select from Dropdown List', sql_varchar) AS DisplayLocNo,
                convert(' Type or Select from Dropdown List', sql_varchar) AS DisplayName,
                convert(' Type or Select from Dropdown List', sql_varchar) AS DisplayDbaName,
                CONVERT(- 1, sql_integer) AS Loc_No, CONVERT(20, sql_integer) AS Dba
FROM CL_MAST
UNION
SELECT  ltrim(rtrim(CONVERT(Loc_No, sql_varchar) + ' - ' + LTRIM(RTRIM(Name)))) AS DisplayLocNo,
                ltrim(rtrim(LTRIM(RTRIM(Name)) + ' - ' + CONVERT(Loc_No, sql_varchar))) AS DisplayName,
                ltrim(rtrim(LTRIM(RTRIM(Dba_Name)) + ' - ' + CONVERT(Loc_No, sql_varchar))) AS DisplayDbaName,
                CONVERT(Loc_No, sql_integer) as Loc_No, LENGTH(Dba_Name) AS Dba
FROM CL_MAST CL_MAST_1
WHERE (Delete_Date IS NULL)
order by 4

It runs fine under version 8 after I added convert around the literal strings in the top part of the union.  In version 10, I get "[LNA}[Pervasive][ODBC Engine Interface] More than one unlimited size column in Distinct, Order By, Group By, or Union."
I've tried changing to cast and setting the size, but that didn't work.  I'm open to suggestions.
Thank you in advance for any help you can provide.

I'm new to Pervasive, but I'm an old pro when it comes to Oracle or SQL Server.
Sincerely,
Bruce Edgar
email: bedgar@aeshr.com
Databases

Avatar of undefined
Last Comment
jamessa
ASKER CERTIFIED SOLUTION
Avatar of Bill Bach
Bill Bach
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of jamessa
jamessa

ASKER

Thanks for the suggestion BillBach.
I just finished capturing the data into a datatable and added a new row to the table before binding it to the datasource property.  It works like I wanted.

While I admit, this is some ugly looking code, I'm hoping to figure out what caused the error message and how to resolve it, just so I'd know.

You don't have these problems with real databases like Oracle and SQL Server :?)==)
Avatar of jamessa
jamessa

ASKER

This is a good work-around but doesn't explain the error message that was generated using the union statement.  Individually, each SQL statement runs fine until the union is added.
Avatar of Bill Bach
Bill Bach
Flag of United States of America image

Obviously, the error is referring to the presence of the UNION.  My guess is that internally, Pervasive is building a temp table anyway, and just doesn;'t know how to handle the possibility of multiple long fields.  Might be possible to force the fields to VARCHAR(200) and see if that works, but it's purely speculation now.

As for the problem, I ONLY work on the Pervasive databases -- like PSQLv10, PSQLv9, PSQLV8, PSQL2000i, PSQL7, and Btrieve 6.15.  As such, I can't offer much about how those expensive systems handle it.
Avatar of jamessa
jamessa

ASKER

Only thing, This exact code ran fine uder Pervasive v8.  My best guess is one of the fields is returning padded characters and I haven't figured out where one yet.

Did try using cast as varchar(10), to just truncate the data, but that didn't work.

Thanks again Bill for the quick response and suggestions.
Databases
Databases

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

62K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo