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.
If it's not broken, fix it until it is.
My Blog