asked on

Using more than one join on an Informix Database

Hi Experts

I am currently working on an Excel sheet which, with the aid of VBA populates a sheet with data from an Informix database.

My problem is that when I try to use more than one join statement, on execution Excel locks up.

The following SQL line returns any amount of results happily: -

SELECT * FROM actlup ORDER BY actlup.activity_code

But the following lines always struggle: -

SELECT * FROM actlup FULL OUTER JOIN doctyp ON actlup.activity_code = doctyp.type_code WHERE doctyp.wp_code = 'WORD' ORDER BY actlup.activity_code

SELECT * FROM actlup FULL OUTER JOIN doctyp ON actlup.activity_code = doctyp.type_code FULL OUTER JOIN casegrp ON actlup.activity_code = casegrp.id_code WHERE doctyp.wp_code = 'WORD' AND casegrp.main_group = 'MADV' ORDER BY actlup.activity_code

It could well be my syntax, I'm a rookie at this kind of thing, so any help would be much appreciated.

Dim AdoCon3 As ADODB.Connection
Dim Rs3 As ADODB.Recordset
Dim strSQL3 As String
Dim strSQL3p1 As String
Dim strSQL3p2 As String
Dim conditval As Integer
Dim conditval2 As Integer
 
conditval = 0
conditval2 = 0
 
Set AdoCon3 = New ADODB.Connection
    AdoCon3.CursorLocation = adUseClient
    AdoCon3.Open "DSN=arista32;Uid=informix;Pwd=Axxia;"
 
    
    strSQL3p1 = "SELECT first 100 * FROM actlup "
    
    
    If cmbDocType.Value <> "All Doc Types" Then
        
    strSQL3p1 = strSQL3p1 & "FULL OUTER JOIN doctyp ON actlup.activity_code = doctyp.type_code "
        
    End If
    
    If cmbGroup.Value <> "All Groups" Then
    
    strSQL3p1 = strSQL3p1 & "FULL OUTER JOIN casegrp ON actlup.activity_code = casegrp.id_code "
    
    End If
    
    
 
        If txtCode.Value <> "" Then
    
            If conditval = 0 Then
        
                strSQL3p2 = strSQL3p2 & "WHERE actlup.activity_code LIKE '%" & txtCode.Value & "%'"
                conditval = conditval + 1
        
            ElseIf conditval > 0 Then
                    
                strSQL3p2 = strSQL3p2 & " AND actlup.activity_code LIKE '%" & txtCode.Value & "%'"
                conditval = conditval + 1
                    
             End If
                 
            
        End If
    
    
        If txtDesc.Value <> "" Then
    
            If conditval = 0 Then
        
                strSQL3p2 = strSQL3p2 & "WHERE actlup.activity_desc LIKE '%" & txtDesc.Value & "%'"
                conditval = conditval + 1
        
            ElseIf conditval > 0 Then
                    
                strSQL3p2 = strSQL3p2 & " AND actlup.activity_desc LIKE '%" & txtDesc.Value & "%'"
                conditval = conditval + 1
                    
            End If
                 
        End If
 
    
        If cmbDocs.Value <> "All Activities" Then
    
            If cmbDocs.Value = "Documents Only" Then
       
                If conditval = 0 Then
        
                    strSQL3p2 = strSQL3p2 & "WHERE actlup.doc_flag = 'y'"
                    conditval = conditval + 1
            
                ElseIf conditval > 0 Then
                    
                    strSQL3p2 = strSQL3p2 & " AND actlup.doc_flag = 'y'"
                    conditval = conditval + 1
                        
                End If
       
            End If
       
    
            If cmbDocs.Value = "Activities Only" Then
       
                If conditval = 0 Then
        
                    strSQL3p2 = strSQL3p2 & "WHERE actlup.doc_flag = 'n'"
                    conditval = conditval + 1
            
                ElseIf conditval > 0 Then
                    
                    strSQL3p2 = strSQL3p2 & " AND actlup.doc_flag = 'n'"
                    conditval = conditval + 1
                        
                End If
       
            End If
        
        End If
    
        
        If cmbDocType.Value <> "All Doc Types" Then
        
            If conditval = 0 Then
                
                strSQL3p2 = strSQL3p2 & "WHERE doctyp.wp_code = '" & cmbDocType.Value & "'"
                conditval = conditval + 1
                
            ElseIf conditval > 0 Then
                    
                strSQL3p2 = strSQL3p2 & " AND doctyp.wp_code = '" & cmbDocType.Value & "'"
                conditval = conditval + 1
                        
            End If
    
        End If
            
        
        If cmbGroup.Value <> "All Groups" Then
        
           If conditval = 0 Then
        
                strSQL3p2 = strSQL3p2 & "WHERE casegrp.main_group = '" & cmbGroup.Value & "'"
                conditval = conditval + 1
                
            ElseIf conditval > 0 Then
                    
                strSQL3p2 = strSQL3p2 & " AND casegrp.main_group = '" & cmbGroup.Value & "'"
                conditval = conditval + 1
                        
            End If
    
        End If
        
    
    
    strSQL3 = strSQL3p1 & strSQL3p2 & " ORDER BY actlup.activity_code"
    
    
    Set Rs3 = New ADODB.Recordset
    Rs3.Open strSQL3, AdoCon3, adOpenDynamic, adLockPessimistic
        
    Dim loopval As Long
    loopval = 9
     
    Do Until Rs3.EOF
 
   Sheet2.Cells(loopval, "a").Value = Rs3!activity_code
   Sheet2.Cells(loopval, "c").Value = Rs3!activity_desc
    
    
    loopval = loopval + 1
    Rs3.MoveNext
    Loop
MsgBox strSQL3
AdoCon3.Close
Set Rs3 = Nothing
Set AdoCon3 = Nothing
 
Open in New Window Tags: 
Microsoft, Excel, 2003, VBA

Open in new window

ringroselaw

ASKER

After doing some more testing, I've found the following line will populate the spreadsheet, but only if I limit it to return 150 rows (max).

SELECT FIRST 150 * FROM actlup FULL OUTER JOIN doctyp ON actlup.activity_code = doctyp.type_code FULL OUTER JOIN casegrp ON actlup.activity_code = casegrp.id_code WHERE doctyp.wp_code = 'WORD' AND casegrp.main_group = 'MADV' ORDER BY actlup.activity_code

gheist

Thats client limitation. Informix is happoer with WHERE a=b and c=d joins - they tend to use common column indices.

ASKER CERTIFIED SOLUTION

TheLastStraw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ringroselaw

ASKER

I have changed the joins as suggested, this appears to have resolved the issue. Although the Form used to set the conditions did go blank on an attempt to pull 3000 records. But hey, it's working.

Thanks for your help.