Re: another sql problem
Try this:
update validrefills set (last_rfdate,
rf_days,
next_rf_date,
calc_type,
rx_expiration_date,
rx_refill_left,
cardholder_id,
last_refill_nbr
)=
((select max(n.rx_last_disp_date),
max(n.rf_days),
max(n.next_rf_date),
max(n.calc_type),
min(n.rx_expiration_date),
min(n.rx_refill_left),
max(n.rf_bm_cardhldr_id),
max(n.rx_refill_nbr-rx_refill_left)
from ndc_data_in n
where n.ndc_store_nbr = validrefills.store_nbr and
n.rx_number = validrefills.rxnum and
n.new = 'N'
))
where exists (select * from ndc_data_in n2
where n2.ndc_store_nbr = validrefills.store_nbr and
n2.rx_number = validrefills.rxnum and
n2.new = 'N');
Hope this help
Cristian Bucur
----- Original Message -----
From: Ty O'Kelly <tokelly@spammers.suck.tcac.net>
To: <informix-list@iiug.org>
Sent: Tuesday, February 22, 2000 1:52 PM
Subject: another sql problem
> following is a sql statement i am trying to convert from oracle to
> informix. the sql statement that ran fine on in oracle, but it is
> giving me trouble on informix. from what i can tell from documentation
> and other posts to this newsgroup, the statement is written correctly.
> when i run the statment i get an error about trying to insert a null
> value into validrefills.last_rfdate. there are no nulls in the source
> table (ndc_data_in), so it seems that informix is trying to update the
> table with a non-existent record. it is like it isn't paying any
> attention to the where clause and trying to update every record in the
> table. do any of you informix gurus see any glaring mistakes in this
> sql?
>
> thanks,
> ty o'kelly
> tokelly@tcac.net
>
> update validrefills set (last_rfdate,
> rf_days,
> next_rf_date,
> calc_type,
> rx_expiration_date,
> rx_refill_left,
> cardholder_id,
> last_refill_nbr
> )=
> ((select max(rx_last_disp_date),
> max(rf_days),
> max(next_rf_date),
> max(calc_type),
> min(rx_expiration_date),
> min(rx_refill_left),
> max(rf_bm_cardhldr_id),
> max(rx_refill_nbr-rx_refill_left)
> from ndc_data_in n
> where n.ndc_store_nbr = validrefills.store_nbr and
> n.rx_number = validrefills.rxnum and
> n.new = 'N'
> ))
> where exists (select * from ndc_data_in n
> where n.ndc_store_nbr = validrefills.store_nbr and
> n.rx_number = validrefills.rxnum and
> n.new = 'N');>
>
>
>
>