Problems with Year() Function

[#1]

Hello to everybody,

I'm new with qilikview and trying to make my first project.

I import data from an firebird database via ODBC. When adding the Year function (as stated below) the script makes an error:

SQL##f - SqlState: HY000, ErrorCode: 4294967192, ErrorMsg: [ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 3, column 5
Year
SQL SELECT ID,
DATUM,
Year(Datum) as Jahr,
WAEHRUNG,
"WAEHRUNG_KURS",
"MWST_SATZ",
"GESAMTSUMME_NETTO",
HEADER,
"HAUPT_ID",
"BEZAHLT_BRUTTO"
FROM "V_RECHNUNG"

I'm afraid I make a very trivial mistake but I don't know where and why? Datum is a timestamp format and without the year function the script works!

Thanks for every help!!!

Christoph

[hector]

Hi, i don't know Firebird SQL, but it depends on every DB Engine how to extract this values, for example in MSSQL is Year(Field) as Year, but in Oracle is Extract(Year From Field) as Year

If you don't know how is the function try this method

Load
*,
 Year(DATUM) as Jahr
;
 SQL SELECT ID,
 DATUM,
 WAEHRUNG,
 "WAEHRUNG_KURS",
 "MWST_SATZ",
 "GESAMTSUMME_NETTO",
 HEADER,
 "HAUPT_ID",
 "BEZAHLT_BRUTTO"
FROM "V_RECHNUNG"

So, you are using the QlikView's year() function

Rgds

[#3]

QlikView field names ARE case-sensitive, so you must be consistent. If your field is spelled DATUM in all capital letters, your year function should be Year(DATUM).

[rwunderlich]

I don't think Year() is a function defined to Firebird. If you are trying to use the Qlikview Year() function, is should be used in a preceeding load like this:

LOAD *,
Year(DATUM) as Jahr
;
SQL SELECT ID,
DATUM,
WAEHRUNG,

"WAEHRUNG_KURS",
"MWST_SATZ",
"GESAMTSUMME_NETTO",
HEADER,
"HAUPT_ID",
"BEZAHLT_BRUTTO"
FROM "V_RECHNUNG"
;

-Rob

[hector · Accepted solution]

Hi, as i said it's a SQL's DML problem

http://www.firebirdsql.org/refdocs/langrefupd20-extract.html

Use Extract from instead of Year() OR, put the year() function between the Load and ";" before the SQL Select Statement

Rgds

[#6]

Thanks a lot for your support! Extract function works.