Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problems with Year() Function

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

1 Solution

Accepted Solutions
hector
Specialist
Specialist

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

View solution in original post

5 Replies
hector
Specialist
Specialist

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

Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Specialist
Specialist

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

Not applicable
Author

Thanks a lot for your support! Extract function works. Yes