cancel
Showing results for 
Search instead for 
Did you mean: 

SqlSyntaxeError while using INTERSECT

Highlighted
Occasional Visitor

SqlSyntaxeError while using INTERSECT

Hi Community,

I have some issues running SQL query on a JDBC Step when I use the INTERSECT statement, the querty run just fine at SQL Developper.

here a sample :

 

select
p.party_uid
,addr.addr_name
,rand() as r
from
s_party p
,s_addr_per addr
where 1=1
and p.row_id = addr.par_row_id
and addr.x_nat_coord in ('1200','1300')
and p.party_uid in (select
p.party_uid
from
s_party p
s_addr_per addr
where 1=1
and p.row_id = addr.par_row_id
and addr.x_nat_coord = '1200'
group by p.party_uid
having count(addr.row_id) = 1
INTERSECT
select
p.party_uid
from
s_party p
,s_addr_per addr
where 1=1
and p.row_id = addr.par_row_id
and addr.x_nat_coord = '1300'
group by p.party_uid
having count(addr.row_id) = 1
)
order by p.party_uid,r
FETCH FIRST 2 ROWS ONLY
;

the query is running without problem on SQL Developper, but when I run it on t a JDBC Step on SOAP UI I got the error : 

Thu Dec 20 12:02:34 CET 2018:INFO:Error getting response for [Step 214]; com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;;EXCEPT MINUS UNION QUERYNO SKIP FOR OPTIMIZE <END-OF-STATEMENT>, DRIVER=3.71.22

Thanks a lot for your help

 

New Here?
Join us and watch the welcome video:
Top Kudoed Authors