topic Re: ODBC field name/table join in QlikView

ODBC field name/table join

Fri, 13 Jul 2012 10:34:11 GMT

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

Re: ODBC field name/table join

Fri, 13 Jul 2012 10:38:37 GMT

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];

Re: ODBC field name/table join

Fri, 13 Jul 2012 10:42:38 GMT

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]

Re: ODBC field name/table join

Fri, 13 Jul 2012 10:44:05 GMT

how big is you database?

can't you write sql select * ?

Re: ODBC field name/table join

Fri, 13 Jul 2012 11:03:38 GMT

Try this:

Load DDWEEK as [WEEK NO],

TOTAL;

SQL Select DDWEEK, sum(DDTOTL) as TOTAL

FROM ls3dta100a/ddp

GROUP BY DDWEEK;

Re: ODBC field name/table join

Fri, 13 Jul 2012 11:08:23 GMT

That worked.

Thanks