What is wrong with SQL syntax

[dharmadam]

update prescriber_cross_ref
set preferred_in = 'Y' where prescriber_id =
(with temp1 as
(select A.prescriber_id,A.prescriber_dea_nb,A.prescriber_a ma_nb,A.prescriber_aoa_nb
from claimsa.tprscbr_prescribe1 A, prescriber_cross_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.prescriber_id from temp2 where temp2.prescriber_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_cross_ref
set preferred_in = 'Y'
where prescriber_id =
(select temp2.prescriber_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_dea_nb
, A.prescriber_ama_nb
, A.prescriber_aoa_nb
from claimsa.tprscbr_prescribe1 A
, prescriber_cross_ref B
where A.prescriber_id = B.prescriber_id
) temp1
) AS temp2
where temp2.prescriber_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_cross_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_cross_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.prescriber_id is not null
)
Furthermore, this could produce same results.
update prescriber_cross_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_cross_ref
set preferred_in = 'Y'
where prescriber_id IN
(select temp2.prescriber_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_dea_nb
, A.prescriber_ama_nb
, A.prescriber_aoa_nb
from claimsa.tprscbr_prescribe1 A
, prescriber_cross_ref B
where A.prescriber_id = B.prescriber_id
) temp1
) AS temp2
where temp2.prescriber_id is not null
)

You might realize that you do not need to specify "where
temp2.prescriber_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_cross_ref
set preferred_in = 'Y'
where prescriber_id IN
(select A.prescriber_id
from claimsa.tprscbr_prescribe1 A
, prescriber_cross_ref B
where A.prescriber_id = B.prescriber_id
AND (A.prescriber_ama_nb <>''
OR
A.prescriber_aoa_nb <>''
)
)

[Tokunaga T.]

> As a result, my idea is as follows:

update prescriber_cross_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.prescriber_id is not null
)

This seems to be sql error. TABLE keyword is necessary.
update prescriber_cross_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.prescriber_id is not null
)