0

When I try following code it returns attached error code

select acct_key, (case when acct_key in ( select acct_key from table_A ) then 1 else 0 end) as acct_indicator from table_B; 

Executed as Single statement. Failed [804 : 08S01] Socket communication failure for Packet transmit Wed May 25 12:01:18 CDT 2022 socket orig=VTDSSBP.rw.discoverfinancial.com local=/10.2.181.125:61593 remote=VTDSSBPcop2.rw.discoverfinancial.com/170.217.201.43:1025 keepalive=false nodelay=false receive=65536 send=64512 linger=10 traffic=0 concurrent=3 contimeout=10000 conwait=1000 connecttime=38 connecttotaltime=38 connectattempts=1 connectfailures=0 reconnectattempts=0 recoverable=false redrive=false failurecache={} cid=1221d607 sess=106395528 java.net.SocketException: Software caused connection abort: socket write error at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(Unknown Source) at java.net.SocketOutputStream.write(Unknown Source) at ...

Share
1

1 Answer 1

0

attempt 1:

select a.acct_key, case when exists ( select acct_key from table b where a.acct_key = b.acct_key ) then 1 else 0 end as acct_indicator from table a; 

failed as it returns Illegal expression in When Clause of case expression

attempt 2:

select a.acct_key, case when b.acct_key is not null then 'yes' else 'no' end as acct_ind from table a left join table b on a.acct_key = b.acct_key; 

succeed

Share
1
  • 1
    Scalar Correlated Subqueries are hard to optimize, query #2 should be preferred, it probably shows a better plan/execution.
    – dnoeth
    Jun 15 at 7:57

Not the answer you're looking for? Browse other questions tagged or ask your own question.