CODEWITHSUNDEEP
sqloracle-databasebiztalkbiztalk-2010
Andy
Andy

Reputation: 2318

Does BizTalk support sub-queries?

The below query works in Oracle but not in BizTalk. A quick google has informed me that

"Multiple statements do not work with Oracle (and other databases might prevent this as well). Oracle prevents what is called SQL injection, a mechanism that a hacker would use to get the database to run code within the database."

Is this the same as sub-queries? Would it be possible to write this query without a sub-query?

update order_header oh set uploaded = 'Y'
where oh.user_def_chk_3 = 'N' 
and oh.status in('Complete','Shipped') 
and oh.client_id in (select c.client_id from client c 
    where upper(c.notes) like 'BERGEN%');

Upvotes: 0

Views: 185

Answers (2)

Alex Poole
Alex Poole

Reputation: 191580

(Based on feedback from wild-guess comment): BizTalk is apparently interpreting the trailing ; as a statement separator, so it's falling foul of the multiple-statement restriction even though it's really only one. Removing the semicolon will prevent this.

See this answer for background on why it is often used in SQL*Plus and SQL Developer etc. even for single statements. Things are different through other clients and adapters.

Upvotes: 2

Jay
Jay

Reputation: 14481

You might place your query within a stored procedure or view. You didn't say how you're executing the query. Are you using a WCF custom adapter?

Upvotes: 1

Related Questions