>>Hi everybody,
>>
>>Here is the select statement, that after I removed two left joins from it, returns result:
>>
>>

>>SELECT Batch_Imports.cBatch_Imports_pk, Batch_Imports.iBatch_Number, ;
>>Batch_Imports.cFile_Type, Batch_Imports.cFile_Control_Info, ;
>>Batch_Imports.cInput_Source, ;
>>Batch_Imports.cClient_Code, Batch_Imports.cDEPARTMENT_CODE, ;
>>Departments.cDepartment_Name, ;
>>TTOD(Batch_Imports.TBATCH_CREATION_DATE) as tBATCH_CREATION_DATE, ;
>>TTOD(Batch_Imports.TBATCH_PROCESSED_DATE) as tBATCH_PROCESSED_DATE, ;
>>TTOD(Batch_Imports.TBATCH_IMPORTED_DATE) as tBATCH_IMPORTED_DATE, ;
>>TTOD(Batch_Imports.TBATCH_RECEIVED_DATE) as tBATCH_RECEIVED_DATE, ;
>>TTOD(Batch_Imports.TBATCH_Open_DATE) as tBATCH_Open_DATE, ;
>>Batch_Imports.iNumber_OF_VISITS, ;
>>Batch_Imports.iBillable_visits, ;
>>Batch_Imports.yDOLLAR_AMOUNT, ;
>>Batch_Imports.iBATCH_STATUS, Batch_Imports.cFile_Name, ;
>>PADR(ICASE(Batch_Imports.iBATCH_STATUS = 0,"", ;
>>Batch_Imports.iBATCH_STATUS = 1, "POSTED SUCCESSFULLY", ;
>>Batch_Imports.iBATCH_STATUS = -1,"POSTING FAILED", ;
>>"POSTED PARTIALLY"),25) as cBatch_Status ;
>>FROM Batch_Imports ;
>>LEFT JOIN Departments on ;
>>Batch_Imports.cDepartment_Code = Departments.cDepartment_Code ;
>>WHERE Batch_Imports.cFile_Type LIKE '%PAYMENT%' ORDER BY 2 DESCENDING
>>

>>
>>Here is the code for the original select statement that returned 0 records:
>>

>>text TO lcSQLStr TEXTMERGE NOSHOW PRETEXT 7
>>	SELECT Batch_Imports.cBatch_Imports_pk, Batch_Imports.iBatch_Number, ;
>>	Batch_Imports.cFile_Type, Batch_Imports.cFile_Control_Info, ;
>>	Batch_Imports.cInput_Source, ;
>>	Batch_Imports.cClient_Code, Batch_Imports.cDEPARTMENT_CODE, ;
>>	Departments.cDepartment_Name, ;
>>	TTOD(Batch_Imports.TBATCH_CREATION_DATE) as tBATCH_CREATION_DATE, ;
>>	TTOD(Batch_Imports.TBATCH_PROCESSED_DATE) as tBATCH_PROCESSED_DATE, ;
>>	TTOD(Batch_Imports.TBATCH_IMPORTED_DATE) as tBATCH_IMPORTED_DATE, ;
>>	TTOD(Batch_Imports.TBATCH_RECEIVED_DATE) as tBATCH_RECEIVED_DATE, ;
>>	TTOD(Batch_Imports.TBATCH_Open_DATE) as tBATCH_Open_DATE, ;
>>	Batch_Imports.iNumber_OF_VISITS, ;
>>	Batch_Imports.iBillable_visits, ;
>>	Batch_Imports.yDOLLAR_AMOUNT,
>>	Batch_Imports.iBATCH_STATUS, Batch_Imports.cFile_Name,
>>	VisCodes.cCode_Description AS cPRODUCT_LINE,
>>	Client_Hospitals.cClient_hospital_Name,
>>	PADR(ICASE(Batch_Imports.iBATCH_STATUS = 0,"<<laStatuses[2]>>", ;
>>	Batch_Imports.iBATCH_STATUS = 1, "<<laStatuses[3]>>",
>>	Batch_Imports.iBATCH_STATUS = -1,"<<laStatuses[1]>>", ;
>>	"<<laStatuses[4]>>"),25) as cBatch_Status ;
>>	FROM Batch_Imports
>        LEFT JOIN VisCodes ON Batch_Imports.cPRODUCT_LINE_CODE = VisCodes.cCode_Value AND ;
>                                                                VisCodes.cCategory_Description LIKE 'PRODUCT_LINE %'
>>       LEFT JOIN Client_Hospitals ON Batch_Imports.cClient_Code = Client_Hospitals.cClient_Hospital_id
>>       LEFT JOIN Departments on  Batch_Imports.cDepartment_Code = Departments.cDepartment_Code
>>   WHERE <<m.lcFilter>> ORDER BY 2 DESCENDING
>>ENDTEXT

>>
>>Now, my problem is that cProduct_Line_Code and cClient_Code could be empty in Batch_Imports table. This is the case not returning records. How should I fix my SQL to return records always and if my Client_Code is empty or cProduct_line_Code is empty produce an empty string for cClient_Hospital_Name, etc.?
>>
>>Should I switch to JOIN? Having a brain lapse, hope you can help me.
>
>No metter what the fields in Batch_Imports have or not have SELECT should return all records from Batch_Imports that match WHERE clause if all your joins are LEFT JOINs
>
>About how should you fixed the code:
>

>SELECT ....
>   IIF(EMPTY(Client_Code) OR EMPTY(cProduct_line_Code),;
>                          SPACE(xxx),;
>                          Client_Hospitals.cClient_hospital_Name) AS  cClient_Hospital_Name
>

>
>where xxx is the LEN(Client_Hospitals.cClient_hospital_Name)

Yes, I was thinking that it's weird: I have 2 LEFT JOINS and there are no results. As long as I remove these two joins I see records. Almost like Dragan's mystery. I don't understand, what's wrong. The only WHERE condition is Batch_Imports.cFile_Type LIKE '%PAYMENT%'

I'll try again tomorrow...