You're killing LEFT JOIN with WHERE condition on Viscodes table
Viscodes.ccategory_description LIKE ( "REVENUE_CODES%" )
>
>We have two tables: Billing_Charges and VisCodes. VisCodes is a big lookup table with all possible code values for different purpose. So, if we want to select certain records we use cCategory_Description field.
>In Billing_Charges we have cRevenue_Code field. This field is 4 characters long. In VisCodes the generic cCode_Value field is 10 chars long. When the Billing_Charges table was designed it was decided to put code field rather than PK field from VisCodes, because there could be codes that don't exist in VisCodes.
>
>So, we're trying the following SQL:
>
>SELECT Billing_charges.*, Viscodes.ccode_description;
> FROM ;
> Billing_charges ;
> LEFT OUTER JOIN VisCodes ;
> ON Viscodes.ccode_value LIKE ( Billing_charges.crevenue_code+"%" );
> WHERE Billing_charges.cbilling_main_fk = ( ?vp_cBilling_main_fk );
> AND Viscodes.ccategory_description LIKE ( "REVENUE_CODES%" )
>
>However, it doesn't work if we put non-existant value in cRevenue_Code field.
>
>How does LEFT JOIN work? Does it work based on the order of the tables in SQL or based on the JOIN expression itself? Is there a way to fix this SQL without using SUBSTR or PADR function? The VisCodes table is relatively small, the Billing_Charges table is currently small (just few records), but has potential to grow...
>
>Thanks in advance.
--sb--