What is wrong with SQL syntax

[dharmadam]

update prescriber_cros s_ref
set preferred_in = 'Y' where prescriber_id =
(with temp1 as
(select A.prescriber_id ,A.prescriber_d ea_nb,A.prescri ber_ama_nb,A.pr escriber_aoa_nb
from claimsa.tprscbr _prescribe1 A, prescriber_cros s_ref B where
A.prescriber_id = B.prescriber_id ),
with temp2 as

(select case when prescriber_dea_ nb <>'' and prescriber_ama_ nb <>''
and prescriber_aoa_ nb <>'' then prescriber_id
when prescriber_dea_ nb <>'' and prescriber_ama_ nb
<>'' then prescriber_id
when prescriber_ama_ nb <>'' and prescriber_aoa_ nb <>''
then prescriber_id
when prescriber_ama_ nb <>'' then prescriber_id
when prescriber_aoa_ nb <>'' then prescriber_id

else
null
end as prescriber_id

from temp1))
select temp2.prescribe r_id from temp2 where temp2.prescribe r_id is not
null)

[Serge Rielau]

DB2 does not support "Common Table Expression" (aka WITH) in a subquery.
What you can do is create an SQL Function with the WITH inside of it.

Cheers
Serge

[Tokunaga T.]

You can use nested table expression instead of common table
expression.
(not always, but it seems true in your case)
Like this:
update prescriber_cros s_ref
set preferred_in = 'Y'
where prescriber_id =
(select temp2.prescribe r_id
from (select case
when prescriber_dea_ nb <>''
and prescriber_ama_ nb <>''
and prescriber_aoa_ nb <>'' then
prescriber_id
when prescriber_dea_ nb <>''
and prescriber_ama_ nb <>'' then
prescriber_id
when prescriber_ama_ nb <>''
and prescriber_aoa_ nb <>'' then
prescriber_id
when prescriber_ama_ nb <>'' then
prescriber_id
when prescriber_aoa_ nb <>'' then
prescriber_id
else null
end as prescriber_id
from (select A.prescriber_id
, A.prescriber_de a_nb
, A.prescriber_am a_nb
, A.prescriber_ao a_nb
from claimsa.tprscbr _prescribe1 A
, prescriber_cros s_ref B
where A.prescriber_id = B.prescriber_id
) temp1
) AS temp2
where temp2.prescribe r_id is not null)

But, I am suspicious this will work. Because subselect may return
multiple rows.
I guess your intension is to correlate updating table
prescriber_cros s_ref with claimsa.tprscbr _prescribe1.
Another point is that first three when conditions of a case expression
are included in last two when conditions.
THird point is you joined with prescriber_id. So, I thought you can
use EXISTS.
As a result, my idea is as follows:
update prescriber_cros s_ref B
set preferred_in = 'Y'
where EXISTS
(select *
from (select case
when prescriber_ama_ nb <>'' then
prescriber_id
when prescriber_aoa_ nb <>'' then
prescriber_id
else null
end as prescriber_id
from claimsa.tprscbr _prescribe1 A
where A.prescriber_id = B.prescriber_id
) AS temp2
where temp2.prescribe r_id is not null
)
Furthermore, this could produce same results.
update prescriber_cros s_ref B
set preferred_in = 'Y'
where EXISTS
(select *
from claimsa.tprscbr _prescribe1 A
where A.prescriber_id = B.prescriber_id
AND (prescriber_ama _nb <>''
OR
prescriber_aoa_ nb <>''
)
)

[Tokunaga T.]

You can use IN predicate too.

update prescriber_cros s_ref
set preferred_in = 'Y'
where prescriber_id IN
(select temp2.prescribe r_id
from (select case
when prescriber_dea_ nb <>''
and prescriber_ama_ nb <>''
and prescriber_aoa_ nb <>'' then
prescriber_id
when prescriber_dea_ nb <>''
and prescriber_ama_ nb <>'' then
prescriber_id
when prescriber_ama_ nb <>''
and prescriber_aoa_ nb <>'' then
prescriber_id
when prescriber_ama_ nb <>'' then
prescriber_id
when prescriber_aoa_ nb <>'' then
prescriber_id
else null
end as prescriber_id
from (select A.prescriber_id
, A.prescriber_de a_nb
, A.prescriber_am a_nb
, A.prescriber_ao a_nb
from claimsa.tprscbr _prescribe1 A
, prescriber_cros s_ref B
where A.prescriber_id = B.prescriber_id
) temp1
) AS temp2
where temp2.prescribe r_id is not null
)

You might realize that you do not need to specify "where
temp2.prescribe r_id is not null".
Because NULL is not equal to any value(even with NULL).
But, it may be usefull to filter out unnecessary rows from subselect
and to make better performance.
OR

update prescriber_cros s_ref
set preferred_in = 'Y'
where prescriber_id IN
(select A.prescriber_id
from claimsa.tprscbr _prescribe1 A
, prescriber_cros s_ref B
where A.prescriber_id = B.prescriber_id
AND (A.prescriber_a ma_nb <>''
OR
A.prescriber_ao a_nb <>''
)
)

[Tokunaga T.]

> As a result, my idea is as follows:

update prescriber_cros s_ref B
set preferred_in = 'Y'
where EXISTS
(select *
from (select case
when prescriber_ama_ nb <>'' then
prescriber_id
when prescriber_aoa_ nb <>'' then
prescriber_id
else null
end as prescriber_id
from claimsa.tprscbr _prescribe1 A
where A.prescriber_id = B.prescriber_id
) AS temp2
where temp2.prescribe r_id is not null
)

This seems to be sql error. TABLE keyword is necessary.
update prescriber_cros s_ref B
set preferred_in = 'Y'
where EXISTS
(select *
from TABLE
(select case
when prescriber_ama_ nb <>'' then
prescriber_id
when prescriber_aoa_ nb <>'' then
prescriber_id
else null
end as prescriber_id
from claimsa.tprscbr _prescribe1 A
where A.prescriber_id = B.prescriber_id
) AS temp2
where temp2.prescribe r_id is not null
)