ODBC field name/table join

[#1]

Hi,

I want to use the following in qlikview with an odbc link to our iSeries database.

sql SELECT DDWEEK, sum(DDTOTL) FROM ls3dta100a/ddp GROUP BY DDWEEK;

but this doesn't create join to another table with a field called[WEEK NO], so I tried:

load DDWEEK

as [WEEK NO],

DDTOTL as [TOTAL DESPATCHED];

sql SELECT [WEEK NO], sum([TOTAL AS DESPATCHED]) FROM ls3dta100a/ddp GROUP BY [WEEK NO];

But this creates a ODBC error when connecting to the table on the iSeries. How can I achieve the renaming of the fields so that I can get a join?

Many thanks in advance

Shaun

[#2]

load is ok but sql select should be withous "sum" expression:

sql SELECT [WEEK NO], [TOTAL AS DESPATCHED] FROM ls3dta100a/ddp GROUP BY [WEEK NO];

[#3]

Thanks, but using the brackets on ODBC for the iSeries throws up an error

SQL##f - SqlState: 37000, ErrorCode: 4294967192, ErrorMsg: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token [ was not valid. Valid tokens: ( + * - ? : DAY INF NAN RID ROW RRN CASE CAST CHAR DATE DAYS HASH.

sql SELECT [WEEK NO], [TOTAL AS DESPATCHED] FROM ls3dta100a/ddp GROUP BY [WEEK NO]

[#4]

how big is you database?

can't you write sql select * ?

[#5 · Accepted solution]

Try this:

Load DDWEEK as [WEEK NO],

        TOTAL;

SQL Select DDWEEK, sum(DDTOTL) as TOTAL

     FROM ls3dta100a/ddp

     GROUP BY DDWEEK;

[#6]

That worked.

Thanks