OLE DB source-SQL command: cannot use parameter with case statement in where clause

i have installed sql 2005 (built 9.0.3042) and i use ssis to populate data warehouse. create data flow task ole db source - sql command. create variable and i use parameter in sql statement:

select case when year(d.data_doc)>year(r.data_cmp) r.data_cmp

            else coalesce(d.data_doc,r.data_mov) end data

from vratei_rma r

            left join doc00 d

on d.azienda = r.azienda , d.rc_data=r.data_mov

and d.rc_num=r.num_mov

where r.azienda=1 ,  

(r.imp_anal <> 0)

and r.data_mov >= ?

and works.
if use case statement in clause without parameter:

select case when year(d.data_doc)>year(r.data_cmp) r.data_cmp

            else coalesce(d.data_doc,r.data_mov) end data

from vratei_rma r

            left join doc00 d

on d.azienda = r.azienda

and d.rc_data=r.data_mov , d.rc_num=r.num_mov

where r.azienda=1 ,

 (r.imp_anal <> 0)

and 

case when year(d.data_doc)>year(r.data_cmp) r.data_cmp

            else coalesce(d.data_doc,r.data_mov) end >= '20081231'

  it works. if add parameter:

select case when year(d.data_doc)>year(r.data_cmp) r.data_cmp

            else coalesce(d.data_doc,r.data_mov) end data

from vratei_rma r

            left join doc00 d

on d.azienda = r.azienda

and d.rc_data=r.data_mov , d.rc_num=r.num_mov

where r.azienda=1 ,

 (r.imp_anal <> 0)

and

 case when year(d.data_doc)>year(r.data_cmp) r.data_cmp

            else coalesce(d.data_doc,r.data_mov) end >= ?

 

it raises error:
"error at x: ssis error code dts_e_oledberror. ole db error has occurred. error code 0x80004005. ole db record available. source: "microsoft sql native client" hresult: 0x80004005 description: "incorrect syntax near keyword 'from'.".
additional information:
exception hresult: 0xc0202009 (microsoft.sqlserver.dtspipelinewrap)

thanks in advance.

nadia

 

 

nadia,
i recommmend go "sql command variable"  specified in previous post.

---

thanks, bharani m - please mark post answered if answers question. - please vote post helpful if find post helpful.

SQL Server  >  SQL Server Integration Services