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.
Highlighted
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

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch