Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Informix outer joins

Status
Not open for further replies.

Kaithyn

Programmer
Nov 22, 2004
16
US
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;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top