Subject | Help w/ SQL |
---|---|

Author | Kevin Stanton |

Post date | 2000-12-13T16:03:37Z |

Greetings All,

The following SQL works as long as the join to the ORD_HDR table is

commented out.

As soon as I reinstate this table, the result set is empty.

I thought a LEFT OUTER JOIN would include all records matched or unmatched.

TIA,

Kevin

=============

SELECT S.ID, S.COMPANY, S.DIVISION, S.INVLOC, P.INVNAME, S.PRODID,

(SELECT PRODDESC FROM SP_GET_PRODDESC(S.PRODID)) as PRODDESC,

S.UNITSIZE, S.WRAPCODE, S.SALESID, S.YARDLOC, S.INBORDERNO, S.INBSUFFIX,

S.MILLNO,

V.VNDNAME, S.PIECES, S.FOOTAGE, S.WOODCOST, S.FRTCOST,

S.PROCESSCOST, S.INTEREST, X.PRICINGPER /* , H.INVRECD_SHPDDATE */

FROM PI_SUM S

LEFT OUTER JOIN PROD_XREF X ON (S.PRODID = X.PRODID)

LEFT OUTER JOIN PI_MASTER P ON (S.INVLOC = P.INVLOC)

LEFT OUTER JOIN VENDORS V ON (S.MILLNO = V.VNDNO)

/* LEFT OUTER JOIN ORD_HDR H ON (S.COMPANY = H.COMPANY AND S.DIVISION =

H.DIVISION AND

S.INBORDERNO = H.ORDERNO AND S.INBSUFFIX =

H.SUFFIX ) */

WHERE

(COMPANY = '01') AND (DIVISION = '0') AND

((F_FIXEDPOINT( S.PIECES,0 ) <> 0) OR (F_FIXEDPOINT( S.FOOTAGE,0 ) <> 0)

OR (F_FIXEDPOINT( S.WOODCOST,2 ) <> 0) OR

(F_FIXEDPOINT( S.FRTCOST,2 ) <> 0) OR (F_FIXEDPOINT( S.PROCESSCOST,2 ) <>

0) OR (F_FIXEDPOINT( S.INTEREST,2 ) <> 0))

AND (INVLOC = '1404')

ORDER BY

S.INVLOC, X.XRF_SORT, S.PRODID, V.VNDNAME, S.INBORDERNO, S.INBSUFFIX

The following SQL works as long as the join to the ORD_HDR table is

commented out.

As soon as I reinstate this table, the result set is empty.

I thought a LEFT OUTER JOIN would include all records matched or unmatched.

TIA,

Kevin

=============

SELECT S.ID, S.COMPANY, S.DIVISION, S.INVLOC, P.INVNAME, S.PRODID,

(SELECT PRODDESC FROM SP_GET_PRODDESC(S.PRODID)) as PRODDESC,

S.UNITSIZE, S.WRAPCODE, S.SALESID, S.YARDLOC, S.INBORDERNO, S.INBSUFFIX,

S.MILLNO,

V.VNDNAME, S.PIECES, S.FOOTAGE, S.WOODCOST, S.FRTCOST,

S.PROCESSCOST, S.INTEREST, X.PRICINGPER /* , H.INVRECD_SHPDDATE */

FROM PI_SUM S

LEFT OUTER JOIN PROD_XREF X ON (S.PRODID = X.PRODID)

LEFT OUTER JOIN PI_MASTER P ON (S.INVLOC = P.INVLOC)

LEFT OUTER JOIN VENDORS V ON (S.MILLNO = V.VNDNO)

/* LEFT OUTER JOIN ORD_HDR H ON (S.COMPANY = H.COMPANY AND S.DIVISION =

H.DIVISION AND

S.INBORDERNO = H.ORDERNO AND S.INBSUFFIX =

H.SUFFIX ) */

WHERE

(COMPANY = '01') AND (DIVISION = '0') AND

((F_FIXEDPOINT( S.PIECES,0 ) <> 0) OR (F_FIXEDPOINT( S.FOOTAGE,0 ) <> 0)

OR (F_FIXEDPOINT( S.WOODCOST,2 ) <> 0) OR

(F_FIXEDPOINT( S.FRTCOST,2 ) <> 0) OR (F_FIXEDPOINT( S.PROCESSCOST,2 ) <>

0) OR (F_FIXEDPOINT( S.INTEREST,2 ) <> 0))

AND (INVLOC = '1404')

ORDER BY

S.INVLOC, X.XRF_SORT, S.PRODID, V.VNDNAME, S.INBORDERNO, S.INBSUFFIX