Error in sql script

[system]

WHEN i try to run this query data integrator

SQL(‘CM_DS’,'DELETE from DWH_HROLES_FULL '||
'WHERE MANDT = $G_MANDNT ’ ||
‘AND MONTH = TO_CHAR(TO_DATE($G_DATE_INI,‘YYYY.MM.DD’),‘MM’)’ ||
‘AND YEAR = $G_YEAR_FIN’);

SHOW THIS ERROR

1466476 1 RUN-050304 01/06/2011 10:03:42 a.m. TO_CHAR($G_DATE_INI,‘MM’)AND YEAR = $G_YEAR_FIN ) > failed, due to error <70301>: <Oracle <BO_EBD> error message for operation
1466476 1 RUN-050304 01/06/2011 10:03:42 a.m. : <ORA-00911: invalid character

---

jgalvanhdz (BOB member since 2010-12-16)

[system]

Have you used the square brackets?

‘AND MONTH = TO_CHAR(TO_DATE([$G_DATE_INI],‘YYYY.MM.DD’),‘MM’)’

---

rbowie (BOB member since 2005-10-03)

[system]

Hi can you try this:

SQL(‘CM_DS’,'DELETE from DWH_HROLES_FULL '||
'WHERE MANDT = $G_MANDNT ’ ||
‘AND MONTH = TO_CHAR(TO_DATE($G_DATE_INI,‘YYYY.MM.DD’),‘MM’)’ ||
‘AND YEAR = $G_YEAR_FIN’);

---

chaithu23 (BOB member since 2011-03-25)

[system]

Things you should never do - a collection and discussion

Approach: I want to use a date inside the sql() function Solution: sql(‘DataStore’, 'DELETE FROM table WHERE date_col = [$start_date] '); Problem with that: is actually multi-fold. First is the parameter substitution. In all strings DataServices does see, it will check for either [] or {} combinations. Each text inside these brackets will be put into the expression parser and replaced by its value. So the string ‘1+1=[1+1]’ will result in the string ‘1+1=2’. And with {} extra quotes will be p…

---

Werner Daehn (BOB member since 2004-12-17)

[system]

thanks for your help

the problem was fixed

thanks

---

jgalvanhdz (BOB member since 2010-12-16)