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

[vireshkolagimat]

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

[tresesco]

Please post the script snippet where you use this variable.

[vireshkolagimat]

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)

[tresesco]

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

....

$(vTableName):

  LOAD * ;

SQL SELECT *

FROM table_name

$(vIncrementalExpression) ;

[vireshkolagimat]

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

Is this error something related to to formatting?

Thanks

Viresh

[vireshkolagimat]

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

[Kushal_Chawda]

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;

[vireshkolagimat]

Hi, still getting the error as missing right parenthesis.

[Kushal_Chawda]

try this

$(vTableName):

  LOAD * ;

SQL SELECT *

FROM table_name

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

[vireshkolagimat]

Hi, Nope still getting error.