Problems with importing iSeries DB2 data into Excel

[SuzieW]

Hello

I've been searching on the web all morning and can't find a solution to my problem.

I've got an Excel spreadsheet which imports external data from the iSeries via ODBC.

I have specified some calculated fields in my query to which I'm trying to assign meaningful column headings.

If I run my SQL statement on the iSeries, I do not need to put quotes around the alias, I use:

SELECT SUBSTR(FIELD1,1,2) AS ALIAS, ... etc

However, in the MS Query SQL window, it only accepts it if I put both single and double quotes around the alias! I've tried it without quotes and with single quotes and I get SQL0104 Token not valid....

SELECT SUBSTR(FIELD1,1,2) AS '"ALIAS"', ... etc

Then, each time you run the query, it adds another set of double quotes so you end up with stupid column headings like:

"""""""""""""ALIAS"""""""""""""""" on the resulting spreadsheet.

What on earth is going on?

Help gratefully appreciated...

 

[MarcLodge]

Suzy,
What happens if you use double quotes twice or single quotes twice eg. ""ALIAS"" or ' 'ALIAS' ' ? I think that MS Query has a default value somewhere that says 'treat consecutive delimiters as one'. Now I'm not sure whether it will treat single quote plus double quote as the same delimiter or get confused.

Are you using the Query Wizard to define or are you coding the SQL direct? In the MS Query help it has the following to say:

Change a field column heading so that it's more descriptive

1. Double-click the column heading of the field you want to change.

2. In the Column heading box, type the heading you want.

This may be of use if you are able to get into the part of MS Query where you can do this.

Marc

 

[blom0344]

I have spend years with AS400/DB2 in combination with Excel and MS query is really crap. It does not comply with regular ANSI SQL in many ways (like you found out).
My workaround was to code the SQL as basic as possible and then use a small piece of VBA within the workbook/sheet to re-adjust things like headers.

Since AS400 supports SQL objects, you may also build regular SQL-scripts into database views and use even more basic : Select * from V.
Operations Navigator allows you to easily define such objects - as if you were using a non-AS400 database.

Ties Blom

 

[SuzieW]

Marc - I originally defined the result field using MS query but I had to use quotes in the "Column Heading" box, which surprised me. It wouldn't let me just put the text in. I haven't tried the quote combinations you suggest, so I'll continue to fiddle around with it.

Ties - another case of "Bill (Gates) knows best"... thanks for your tips.