Advertisement

05.23.2008 at 04:02AM PDT, ID: 23427143
[x]
Attachment Details

Using more than one join on an Informix Database

Asked by ringroselaw in Informix Database, Databases Miscellaneous, Microsoft Excel Spreadsheet Software

Tags:

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.





Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:

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
 
Keywords: Using more than one join on an Informix…
Title
1 Excel struggles to return more than 1…
2 Struggling with joins!
3 Using the Join function in Excel vba
4 Band Aid
5 Joins
6 How to migrate informix database to M…
 
Loading Advertisement...
 
[+][-]05.23.2008 at 04:43AM PDT, ID: 21631128

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.23.2008 at 11:23AM PDT, ID: 21634470

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.27.2008 at 05:45AM PDT, ID: 21651348

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Informix Database, Databases Miscellaneous, Microsoft Excel Spreadsheet Software
Tags: VBA SQL Informix
Sign Up Now!
Solution Provided By: TheLastStraw
Participating Experts: 2
Solution Grade: A
 
 
[+][-]05.29.2008 at 03:21AM PDT, ID: 21667666

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628