I am trying to run a procedure which needs to access several tables, however when I try to use more than two OUTERS I get 395: The where clause contains an outer Cartesian Product. Anyone have an idea how to get around this? The problem I am having which I am trying to resolve with the outers is that the procedure will not actually select a specified service type nor condition code.
The procedure is below. Thanks!
create procedure sp_yard(From_loc CHAR(10), To_Loc CHAR(10), Trailer_Number CHAR(15), SCAC_Code CHAR(15), Service_Type CHAR(15), Condition_Code CHAR(15))
RETURNING
CHAR(10),
CHAR(15),
CHAR(15),
CHAR(15),
CHAR(15),
CHAR(10),
DATETIME YEAR TO SECOND,
CHAR(15);
DEFINE l_loc LIKE lc_f.loc;
DEFINE l_trailer LIKE trailer_t.trailer;
DEFINE l_scac LIKE trailer_t.carrier;
DEFINE l_svc_type LIKE svc_t.svc_type;
DEFINE l_cond_code LIKE trailer_cond_t.cond_code;
DEFINE l_seal LIKE trailer_t.seal;
DEFINE l_arv_date LIKE trailer_t.dt_ckin;
DEFINE l_contents LIKE trailer_t.content_type;
SET ISOLATION TO DIRTY READ;
SELECT lc_f.loc,
trailer_t.trailer,
trailer_t.carrier,
trailer_t.seal,
' ' as svc_type,
' ' as cond_code,
trailer_t.dt_ckin,
trailer_t.content_type,
trailer_t.appt
FROM lc_f, outer trailer_t
WHERE lc_f.loc != " "
AND lc_f.loc >= upper(From_Loc)
AND lc_f.loc <= upper(To_Loc)
AND lc_f.loc = trailer_t.loc
AND trailer_t.trailer LIKE Trailer_Number
AND trailer_t.carrier LIKE SCAC_Code
AND lc_f.loc_type in ('DOOR', 'HA')
INTO TEMP loc_tmp;
UPDATE loc_tmp
SET loc_tmp.cond_code = (SELECT trailer_cond_t.cond_code
FROM trailer_cond_t
WHERE trailer_cond_t.trailer = loc_tmp.trailer)
;
FOREACH
SELECT loc_tmp.loc,
loc_tmp.trailer,
loc_tmp.carrier,
loc_tmp.seal,
svc_t.svc_type,
loc_tmp.cond_code,
loc_tmp.dt_ckin,
loc_tmp.content_type
INTO l_loc,
l_trailer,
l_scac,
l_seal,
l_svc_type,
l_cond_code,
l_arv_date,
l_contents
FROM loc_tmp, outer svc_t, OUTER trailer_cond_t
WHERE svc_t.appt = loc_tmp.appt
AND svc_t.svc_type LIKE upper(Service_Type)
AND loc_tmp.cond_code LIKE Condition_Code
RETURN l_loc,
l_trailer,
l_scac,
l_seal,
l_svc_type,
l_cond_code,
l_arv_date,
l_contents
WITH RESUME;
END FOREACH;
END PROCEDURE;
The procedure is below. Thanks!
create procedure sp_yard(From_loc CHAR(10), To_Loc CHAR(10), Trailer_Number CHAR(15), SCAC_Code CHAR(15), Service_Type CHAR(15), Condition_Code CHAR(15))
RETURNING
CHAR(10),
CHAR(15),
CHAR(15),
CHAR(15),
CHAR(15),
CHAR(10),
DATETIME YEAR TO SECOND,
CHAR(15);
DEFINE l_loc LIKE lc_f.loc;
DEFINE l_trailer LIKE trailer_t.trailer;
DEFINE l_scac LIKE trailer_t.carrier;
DEFINE l_svc_type LIKE svc_t.svc_type;
DEFINE l_cond_code LIKE trailer_cond_t.cond_code;
DEFINE l_seal LIKE trailer_t.seal;
DEFINE l_arv_date LIKE trailer_t.dt_ckin;
DEFINE l_contents LIKE trailer_t.content_type;
SET ISOLATION TO DIRTY READ;
SELECT lc_f.loc,
trailer_t.trailer,
trailer_t.carrier,
trailer_t.seal,
' ' as svc_type,
' ' as cond_code,
trailer_t.dt_ckin,
trailer_t.content_type,
trailer_t.appt
FROM lc_f, outer trailer_t
WHERE lc_f.loc != " "
AND lc_f.loc >= upper(From_Loc)
AND lc_f.loc <= upper(To_Loc)
AND lc_f.loc = trailer_t.loc
AND trailer_t.trailer LIKE Trailer_Number
AND trailer_t.carrier LIKE SCAC_Code
AND lc_f.loc_type in ('DOOR', 'HA')
INTO TEMP loc_tmp;
UPDATE loc_tmp
SET loc_tmp.cond_code = (SELECT trailer_cond_t.cond_code
FROM trailer_cond_t
WHERE trailer_cond_t.trailer = loc_tmp.trailer)
;
FOREACH
SELECT loc_tmp.loc,
loc_tmp.trailer,
loc_tmp.carrier,
loc_tmp.seal,
svc_t.svc_type,
loc_tmp.cond_code,
loc_tmp.dt_ckin,
loc_tmp.content_type
INTO l_loc,
l_trailer,
l_scac,
l_seal,
l_svc_type,
l_cond_code,
l_arv_date,
l_contents
FROM loc_tmp, outer svc_t, OUTER trailer_cond_t
WHERE svc_t.appt = loc_tmp.appt
AND svc_t.svc_type LIKE upper(Service_Type)
AND loc_tmp.cond_code LIKE Condition_Code
RETURN l_loc,
l_trailer,
l_scac,
l_seal,
l_svc_type,
l_cond_code,
l_arv_date,
l_contents
WITH RESUME;
END FOREACH;
END PROCEDURE;