Try switching the two tables:
SELECT LINE.cCode, SUM(ROLLS.nWeight_Roll) AS nAccepted ;
FROM ROLLS ;
LEFT OUTER JOIN LINE ;
ON LINE.ID=ROLLS.clineid ;
WHERE UPPER(ALLTRIM(CSTATUS))=="ACCEPTED" ;
GROUP BY LINE.cCode ;
Another cause may be the WHERE clause? What table is CSTATUS coming from? If it's the LINE table, which the record doesn't exist for all records in ROLLS, this would eliminate that record in ROLLS. Change the WHERE to:
WHERE ISNULL(LINE.CSTATUS) OR UPPER(ALLTRIM(CSTATUS))=="ACCEPTED"
>I am uing the following SQL statement:
>
>SELECT LINE.cCode, SUM(ROLLS.nWeight_Roll) AS nAccepted ;
>FROM LINE ;
>LEFT JOIN ROLLS ;
>ON LINE.ID=ROLLS.clineid ;
>WHERE UPPER(ALLTRIM(CSTATUS))=="ACCEPTED" ;
>GROUP BY LINE.cCode ;
>INTO CURSOR tmpProduction
>
>In my LINE table, I have 11 records. cCode={101,102,103....,111}.
>ROLLS table is the rolls produced on a particulare line/machine-line.
>
>I would like to have 11 records after my SQL statement executes in the temporary cursor, however I only get 5. This is because 6 of the lines have no ROLL records associated with them since no production was ever performed on those lines yet.
>
>How can I request that all lines are given, even if the SUM returns 0.00?
>
>Thanks in advance,
