Terrific! So, we just need to add OR IsNull(ccategory_description) part.

Haven't thought about it.

Thanks again for your sharp brain < g >

>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.