Avatar of ringroselaw
ringroselaw 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

DatabasesMicrosoft Excel

Avatar of undefined
Last Comment
ringroselaw

8/22/2022 - Mon
ASKER
ringroselaw

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
ringroselaw

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck