anumoses
asked on
oracle query question
DROP TABLE DSS.TAB1 CASCADE CONSTRAINTS;
CREATE TABLE DSS.TAB1
(
ID NUMBER(38) NOT NULL,
REG_ID_FROM NUMBER(38) NOT NULL,
REG_ID_TO NUMBER(38) NOT NULL,
WEIGHT_MAXIMUM NUMBER(6) NOT NULL,
ESTIMATED_FREIGHT NUMBER(10,2),
PER_POUND_MULTIPLIER NUMBER(6,2),
CARRMETH_ID NUMBER(38),
PROFS_ID_CMETHTYPE NUMBER(38)
)
TABLESPACE MISC
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;
SET DEFINE OFF;
Insert into DSS.TAB1
(ID, REG_ID_FROM, REG_ID_TO, WEIGHT_MAXIMUM, ESTIMATED_FREIGHT,
PER_POUND_MULTIPLIER, CARRMETH_ID, PROFS_ID_CMETHTYPE)
Values
(13219125811, 13218967894, 13218967894, 100, NULL,
10, 1534, NULL);
Insert into DSS.TAB1
(ID, REG_ID_FROM, REG_ID_TO, WEIGHT_MAXIMUM, ESTIMATED_FREIGHT,
PER_POUND_MULTIPLIER, CARRMETH_ID, PROFS_ID_CMETHTYPE)
Values
(13219112356, 13218967894, 13218967894, 100, 150,
NULL, NULL, 14258);
Insert into DSS.TAB1
(ID, REG_ID_FROM, REG_ID_TO, WEIGHT_MAXIMUM, ESTIMATED_FREIGHT,
PER_POUND_MULTIPLIER, CARRMETH_ID, PROFS_ID_CMETHTYPE)
Values
(13219042831, 13219042603, 13218967894, 100, 900,
NULL, 1592, NULL);
Insert into DSS.TAB1
(ID, REG_ID_FROM, REG_ID_TO, WEIGHT_MAXIMUM, ESTIMATED_FREIGHT,
PER_POUND_MULTIPLIER, CARRMETH_ID, PROFS_ID_CMETHTYPE)
Values
(13219042759, 13219042603, 13218967894, 100, 276,
NULL, NULL, 14264);
Insert into DSS.TAB1
(ID, REG_ID_FROM, REG_ID_TO, WEIGHT_MAXIMUM, ESTIMATED_FREIGHT,
PER_POUND_MULTIPLIER, CARRMETH_ID, PROFS_ID_CMETHTYPE)
Values
(13218990529, 13218979268, 13218979264, 100, 276,
NULL, NULL, 14260);
COMMIT;
I have a code that check for few conditions in inserting data
v_region_count number;
v_region_count1 number;
Begin
select count(*) into v_region_count
from dss.freight_estimates
where weight_maximum = :freight_estimates.weight_maximum
AND reg_id_from = :freight_estimates.reg_id_from
and reg_id_to = :freight_estimates.reg_id_to
and carrmeth_id = :freight_estimates.carrmeth_id
and profs_id_cmethtype is null;
select count(*) into v_region_count1
from dss.freight_estimates
where weight_maximum = :freight_estimates.weight_maximum
AND reg_id_from = :freight_estimates.reg_id_from
and reg_id_to = :freight_estimates.reg_id_to
and carrmeth_id is null
and profs_id_cmethtype = :freight_estimates.profs_id_cmethtype;
End;
If v_region_count > 0 then
msg_alert('The combination of Maximum Weight,Region From, Region To and Carrier Method already exists','E',true);
ElsIf v_region_count1 > 0 then
msg_alert('The combination of Maximum Weight,Region From, Region To and Carrier Type already exists','E',true);
End if;
I want the same thing on update. How do I check?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER