topic Re: SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "YEAR": invalid identifier in QlikView

SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "YEAR": invalid identifier

vireshkolagimat — Tue, 29 Aug 2017 05:40:58 GMT

Hi All,

I am trying to load the below script but getting the error message as

" SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "YEAR": invalid identifier".

script: LET vIncrementalExpression = 'Where a.customer not in (''A'',''B'',''C'') and YEAR (salhist.dated) >= Year (GETDATE ()) - 3';

Thank You.

Regards,

Viresh

Re: SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "YEAR": invalid identifier

tresB — Tue, 29 Aug 2017 05:48:17 GMT

Please post the script snippet where you use this variable.

Re: SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "YEAR": invalid identifier

vireshkolagimat — Tue, 29 Aug 2017 06:05:26 GMT

Hi, below is the script.

SET vQvdFile='C:\Users\test.qvd';

SET vTableName='Sales';

LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);

IF $(vQvdExists) THEN

maxdateTab:

LOAD max(INVOICEDATE) as maxdate //?? Set the name of the date or datetime field

FROM $(vQvdFile) (qvd);

LET vMaxdate = Date(Peek('maxdate',0,'maxdateTab'),'YYYY-MM-DD');

LET vLast30date = Date(Peek('maxdate',0,'maxdateTab')-30,'YYYY-MM-DD');

IF IsNull(vMaxdate) then

LET vIncrementalExpression = '';

ELSE

LET vIncrementalExpression = 'Where CUSTOMERCODE not in (''A'',''B'',''C'') and INVOICEDATE >=' & Chr(39)& '$(vLast30date)' &Chr(39) &

' and salhist.dated <=' &Chr(39)& date(vReloadTime,'YYYY-MM-DD') &Chr(39);

ENDIF;

DROP table maxdateTab;

ELSE // QVD does not exist

LET vIncrementalExpression = 'Where CUSTOMERCODE not in (''A'',''B'',''C'') and YEAR (INVOICEDATE) >= Year (GETDATE ()) - 3'; // No QVD. Force full reload

END IF

$(vTableName):

LOAD *

SQL SELECT *

FROM table_name

$(vIncrementalExpression)

Re: SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "YEAR": invalid identifier

tresB — Tue, 29 Aug 2017 06:15:25 GMT

One quick catch: try putting ';' at the end of load statement like:

....

$(vTableName):

LOAD * ;

SQL SELECT *

FROM table_name

$(vIncrementalExpression) ;

Re: SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "YEAR": invalid identifier

vireshkolagimat — Tue, 29 Aug 2017 06:45:16 GMT

Hi, i tried but still getting error as "YEAR": invalid identifier .

Is this error something related to to formatting?

Thanks

Viresh

Re: SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "YEAR": invalid identifier

vireshkolagimat — Tue, 29 Aug 2017 07:08:51 GMT

Hi,

I tried YEAR(TO_DATE(INVOICEDATE)) but now getting missing parenthesis error.

I checked the script, it looks fine but not sure why i am getting the above error.

Regards,

Viresh

Re: SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "YEAR": invalid identifier

Kushal_Chawda — Tue, 29 Aug 2017 07:13:46 GMT

try below

instead of using where clause in variable, directly use the conditions in where clause.

$(vTableName):

LOAD * ;

SQL SELECT *

FROM table_name

Where CUSTOMERCODE not in (''A'',''B'',''C'') and YEAR (INVOICEDATE) >= Year (GETDATE ()) - 3;

Re: SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "YEAR": invalid identifier

vireshkolagimat — Tue, 29 Aug 2017 07:49:23 GMT

Hi, still getting the error as missing right parenthesis.

Re: SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "YEAR": invalid identifier

Kushal_Chawda — Tue, 29 Aug 2017 07:52:37 GMT

try this

$(vTableName):

LOAD * ;

SQL SELECT *

FROM table_name

Where CUSTOMERCODE not in ('A','B','C') and YEAR (INVOICEDATE) >= (Year (GETDATE ()) - 3);

Re: SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "YEAR": invalid identifier

vireshkolagimat — Tue, 29 Aug 2017 08:02:26 GMT

Hi, Nope still getting error.

Re: SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "YEAR": invalid identifier

vireshkolagimat — Tue, 29 Aug 2017 08:29:18 GMT

Hi All,

I got the answer. when you query YEAR (INVOCIEDATE) is not valid in oracle. so i tried EXTRACT (YEAR from INVOICEDATE) and its working fine.

Thank you all.

Regards,

Viresh