Odd Union Query error

[lespaul]

I am writing this SQL statement from a Delphi program to our AS400. We have an internal Delphi program that works similar to 'Query Analyzer' that allows us to see the results of the query. I have this query:

SELECT JURNUM AS JURNUM, LASTNAME, FIRSTNAME, SSN, 
STATUSCD, DOB, 'JMS' AS TYPE 
FROM JMPMAIN WHERE TERMDATE >= '19990911'
UNION
SELECT 0 AS JURNUM, SUBSTRING(NAME, 1, POSITION(',' IN NAME) - 1) AS LASTNAME, SUBSTRING(NAME, POSITION(',' IN NAME) + 1) AS FIRSTNAME, SSN, 
STATUS AS STATUSCD, '' AS DOB, 'AS4' AS TYPE 
FROM CMLIB/CMPJURY WHERE APPRDAT >= 19990911
ORDER BY LASTNAME[code]

When I run each query individually, I get the expected results, but when I try to run the UNION query I get an error:

"Invalid Expression.  SQL System Error."

Does anyone have any idea why this query won't work together when it works separately?

Thanks for any insight!

Leslie

 

[iSeriesCodePoet]

Do the fields come out to be the same length?

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[lespaul]

I think I have figured out that for some reason it doesn't like the -1 and +1 that I need for the position. The deal is that there's the OLD jury information and the NEW jury information. Our database has been poorly designed over the years and the juror's name in the old table is
BLOW, JOE

In the new tables that I created for my program, I did the (relationally) correct thing and split the first and last name.

Previously I just took the whole "NAME" field and used that as the lastname and left the firstname field blank for the older records.

So, I'm trying to split the old database name into FirstName and LastName. I don't understand why the +/- 1 works alone, but not in the union!

I'm thinking about just creating a new table with the information I need in the correct format and using that table instead of the "real" old table.

In order to determine if a person has served on jury duty in the last 3 years, I currently need the information from both tables (since my new tables have only been in existence for about 1 year).

If you have a clue why the function doesn't work correctly in a union, I'd be thrilled to hear about it and a possible solution!!

Thanks for your time,

Leslie

 

[iSeriesCodePoet]

Why not have an RPG program parse that for you? Or is this a one time deal and don't want to mess with it?

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[iSeriesCodePoet]

Sorry, I didn't see the language you where working in.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[lespaul]

I may take your advice and have one of our RPG programmers "fix" the old table for me since I'm the only one using it now, it "shouldn't" break anything else by changing the structure.

Thanks!

Leslie

 

[iSeriesCodePoet]

It should be really easy. I have some sample code to give them an idea on how to find the comma if they want it.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net