tMSSQLRow Error: Incorrect syntax near '@P0'

One Star

tMSSQLRow Error: Incorrect syntax near '@P0'

Hello,
Coming from Ab Initio, I was very glad when I read about tMSSQLRow component where we can create SQL queries on the run with parameters from preceding input. Following is my job.
Input File --> tMSSQLRow --> tParseRecordSet --> Output File
The query in the tMSSQLRow is as follows,
"select memberid from listid? where active=1".
The database contains tables such as,
list123
list3522
list400
The ? parameter picks up numbers from a listid field on the Input file. The job compiled fine but is aborting with the error,
connecting to socket on port 3926
connected
Incorrect syntax near '@P0'.Incorrect syntax near '@P0'.Incorrect syntax near '@P0'.Incorrect syntax near '@P0'.Incorrect syntax near '@P0'.Incorrect syntax near '@P0'.Incorrect syntax near '@P0'.Incorrect syntax near '@P0'.Incorrect syntax near '@P0'.Incorrect syntax near '@P0'.
disconnected
Does anyone have any clue on how to resolve this? Much appreciated, thanks for reading.
Community Manager

Re: tMSSQLRow Error: Incorrect syntax near '@P0'

Hello
Your job should looks like:
input file--row1---tFlowToIterate--iterate--tMssqlInput---tOutputFile
The query in the tMssqlInput is as follows:
"select memberid from "+row1.tableName+" where active=1"
tableName is one column on the schema of input file.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tMSSQLRow Error: Incorrect syntax near '@P0'

The query in the tMssqlInput is as follows:
"select memberid from "+list.row1+" where active=1"
which I hope will resolve into following queries,
select memberid from list100 where active=1
select memberid from list200 where active=1
select memberid from list300 where active=1
where 100, 200 and 300 are the values in Input File. However, I get an error "list cannot be resolved" and the job is unsuccessful. Any help is appreciated. Thanks for reading again.
One Star

Re: tMSSQLRow Error: Incorrect syntax near '@P0'

After several failed attempts, I finally arrived at the following query in the tMssqlInput
"select memberid from list"+((String)globalMap.get("row1.list"))+" where active=1"
And it worked. Smiley Happy