populating a dynamic array 1

[emozley]

Hi,

I am trying to execute a query that and then load it into an array so I can re-format/tabulate the results.

I want the array to have 8 columns although I'm not sure how many rows there will be

0 - FEE-EARNER;
1 - FEETR_0.CHARGE-RATE;
2 - NAME
3 - FEBANDS_0.CHARGE-RATE If BAND = 'COM1'
4 - FEBANDS_0.CHARGE-RATE If BAND = 'COMM'
5 - FEBANDS_0.CHARGE-RATE If BAND = 'CHAR1'
6 - FEBANDS_0.CHARGE-RATE If BAND = 'CHAR'
7 - FEBANDS_0.CHARGE-RATE If BAND = 'CONY'

Ultimately I want to loop through the recordset and in pseudo-code do the following

1. Populate columns 0-2 in the array
2. Look at the 'band' and the 'febands_0.charge-rate' values and populate columns 3-7 accordingly for that row in the array
3. Move onto the next row in the recordset and repeat step 2 as long as the fee-earner value is the same as the last row in the recordset.
4. Start a new row in the array and go back to step 1.

Any help much appreaciated - I am really struggling with arrays!

cheers

Ed

<%

Set DB = Server.CreateObject("ADODB.Connection")
Set TBL = Server.CreateObject("ADODB.RecordSet")

DB.Mode = adModeReadWrite
DB.Open "SOS", "myusername", "mypassword"

Response.Write("Connection established!")

TBL.Open "SELECT FEETR_0.""FEE-EARNER"", FEETR_0.""CHARGE-RATE"", FEETR_0.NAME, FEBANDS_0.BAND, FEBANDS_0.""CHARGE-RATE"" FROM PUB.FEBANDS FEBANDS_0, PUB.FEETR FEETR_0 WHERE FEBANDS_0.""FEE-EARNER"" = FEETR_0.""FEE-EARNER"" AND ((FEBANDS_0.BAND='comm') OR (FEBANDS_0.BAND='com1') OR (FEBANDS_0.BAND='char') OR (FEBANDS_0.BAND='cha1') OR (FEBANDS_0.BAND='cony')) ORDER BY FEETR_0.""FEE-EARNER""", DB

Dim ChargeRates()
counter=0

Do While Not TBL.EOF


TBL.MoveNext
counter=counter+1

Loop
TBL.Close

Set TBL=Nothing
Set DB=Nothing

%>

 

[Abraxus]

You can take a look at faq333-6473 for some help with visualizing multidimensional arrays. I'd like to help you with this and maybe I can expand that faq to include examples specific to this kind of situation too.

Then again, since you're already performing a query that seems to return the data in essentially the format you're looking for, you can use getRows() to automatically dump your recordset into a multidimensional array.

 

[emozley]

Unfortunately I don't think the GetRows statement will help me as things need to be re-arranged too much. The first 10 rows in the recordset look like this

0002 295.00 Hugh Craig cony 180.000
0002 295.00 Hugh Craig comm 295.000
0002 295.00 Hugh Craig com1 300.000
0002 295.00 Hugh Craig char 260.000
0002 295.00 Hugh Craig cha1 240.000
0003 275.00 John M Trotter cony 200.000
0003 275.00 John M Trotter comm 275.000
0003 275.00 John M Trotter com1 300.000
0003 275.00 John M Trotter char 260.000
0003 275.00 John M Trotter cha1 240.000

For the first 10 rows both people have 5 values for band but this may not always be the case.

I am having incredible problems getting even a single value into a dynamic array!

<%
Dim ChargeRates()
counter=0
ChargeRates(counter,0)="Ed"
%>

I will need to do a bit more research I think.

 

[Abraxus]

All right, I'm starting to get a good picture of what you're trying to accomplish here. Definitely complicated and can be accomplished with multidimensional arrays. My biggest difficulty with multidimensional arrays has been creating them from scratch. It's easy with getRows(), but for whatever reason my webserver doesn't allow me to Dim or Redim an array with a variable as one of the dimensions. It went so far that I ended up having to write a case select for every number within a large range that would explicitly Dim the array to the right size. Hopefully that's not always the case and you can dim an array with a variable.

The first thing I would suggest is to find out how many rows your array will need to have. You know that it's going to have 8 columns, but you'll need to write a select statement that returns a count of the distinct people matching the criteria. Then you can create the array:

Dim ChargeRates(7,totalrows)

I also recommend putting the recordset into an array, it'll be easy to work with and is the most memory efficient way to do it:

rsArray = TBL.getRows()
TBL.Close
Set TBL = Nothing

Once you have the array built to the right dimensions the only trick is to loop through the recordset such that you will put all the data where it goes.

[COLOR=green]'Position in ChargeRates array[/color]
crpos = 0
[COLOR=green]'ID for determining when to change rows[/color]
crid = rsArray(0,0)
[COLOR=green]'Setup for first record[/color]
ChargeRates(0,crpos) = rsArray(0,0)
ChargeRates(1,crpos) = rsArray(1,0)
ChargeRates(2,crpos) = rsArray(2,0)
[COLOR=green]'Loop through all rows of rs[/color]
For i=0 to UBound(rsArray,2)
   If crid<>rsArray(0,i) Then
      crpos = crpos+1
      ChargeRates(0,crpos) = rsArray(0,i)
      ChargeRates(1,crpos) = rsArray(1,i)
      ChargeRates(2,crpos) = rsArray(2,i)
      crid  = rsArray(0,i)
   End If
   [COLOR=green]'Put the rate in the right slot[/color]
   Select Case rsArray(3,i)
      Case "com1"
         ChargeRates(3,crpos) = rsArray(4,i)
      Case "comm"
         ChargeRates(4,crpos) = rsArray(4,i)
      Case "char1"
         ChargeRates(5,crpos) = rsArray(4,i)
      Case "char"
         ChargeRates(6,crpos) = rsArray(4,i)
      Case "cony"
         ChargeRates(7,crpos) = rsArray(4,i)
   End Select
Next
[COLOR=green]'Hope that works, let's see[/color]
For r=0 to UBound(ChargeRates,2)
   For c=0 to UBound(ChargeRates,1)
      response.write ChargeRates(c,r)
      if c<UBound(ChargeRates,2) then response.write " --- "
   Next
   response.write "<br>"&chr(13)
Next

*pantpant* Man I hope that works, let me know!

 

[Abraxus]

Darn, I left out the "end if" at the end of the fourth-to-last line. Trying to get that last bit typed out fast for you and didn't check it, sorry.

 

[emozley]

Hi there thanks for this you have set me on the right track and I have used your method but just in a slightly different way.

The main problem I had was getting my head around a multidemensional array that was also dynamic. Once I'd got that sorted then using your code I was able to work through it methodically.

Here's what I ended up with:

<%

Set DB = Server.CreateObject("ADODB.Connection")
Set TBL = Server.CreateObject("ADODB.RecordSet")

DB.Mode = adModeReadWrite
DB.Open "SOS", "myusername", "mypassword"

TBL.Open "SELECT FEETR_0.""FEE-EARNER"", FEETR_0.""CHARGE-RATE"", FEETR_0.NAME, FEBANDS_0.BAND, FEBANDS_0.""CHARGE-RATE"" FROM PUB.FEBANDS FEBANDS_0, PUB.FEETR FEETR_0 WHERE FEBANDS_0.""FEE-EARNER"" = FEETR_0.""FEE-EARNER"" AND ((FEBANDS_0.BAND='comm') OR (FEBANDS_0.BAND='com1') OR (FEBANDS_0.BAND='char') OR (FEBANDS_0.BAND='cha1') OR (FEBANDS_0.BAND='cony')) ORDER BY FEETR_0.""FEE-EARNER""", DB

Dim ChargeRates()
counter=0

Do While Not TBL.EOF

OLDFEEEARNER=TBL("FEE-EARNER")

Redim Preserve ChargeRates(7,counter)

ChargeRates(0,counter)=TBL("FEE-EARNER")
ChargeRates(1,counter)=TBL("CHARGE-RATE")
ChargeRates(2,counter)=TBL("NAME")

If TBL("BAND")="com1" Then ChargeRates(3,counter)=TBL("CHARGE-RATE")
If TBL("BAND")="comm" Then ChargeRates(4,counter)=TBL("CHARGE-RATE")
If TBL("BAND")="cha1" Then ChargeRates(5,counter)=TBL("CHARGE-RATE")
If TBL("BAND")="char" Then ChargeRates(6,counter)=TBL("CHARGE-RATE")
If TBL("BAND")="cony" Then ChargeRates(7,counter)=TBL("CHARGE-RATE")

TBL.MoveNext

If Not TBL.EOF Then
If TBL("FEE-EARNER")<>OLDFEEEARNER Then
counter=counter+1
End If
End If

Loop
TBL.Close

Set TBL=Nothing
Set DB=Nothing

%>
<table>
<tr>
<td>FE Ref</td>
<td>Default Rate</td>
<td>Name</td>
<td>A</td>
<td>B</td>
<td>C</td>
<td>D</td>
<td>E</td>
</tr>
<%
For i=0 to ubound(ChargeRates,2)
%>

<tr>
<td><% = (ChargeRates(0,i)) %></td>
<td><% = (ChargeRates(1,i)) %></td>
<td><% = (ChargeRates(2,i)) %></td>
<td><% = (ChargeRates(3,i)) %></td>
<td><% = (ChargeRates(4,i)) %></td>
<td><% = (ChargeRates(5,i)) %></td>
<td><% = (ChargeRates(6,i)) %></td>
<td><% = (ChargeRates(7,i)) %></td>
</tr>

<%
Next
%>
</table>


cheers for all your help - one star for you!

Thanks

Ed

 

[Abraxus]

Very happy to help! I spent a lot of time learning multidimensional arrays and do appreciate the powerful stuff you can do with them, it just takes a lot of effort to remember and apply the tricky syntax

Eric

 

[Tarwn]

It should also be possible to modify your SQL Statement to output the data in the exact format you need instead of looping through it in your code to reformulate it. I don't know what type of databaes your using or if this syntax is supported, but there should be an equivalent:

TBL.Open "SELECT FEETR_0.""FEE-EARNER"", FEETR_0.""CHARGE-RATE"", FEETR_0.NAME, " & _
"SUM(CASE WHEN FEBANDS_0.BAND = 'comm' THEN FEBANDS_0.""CHARGE-RATE"" ELSE 0) AS commSum,SUM(CASE WHEN FEBANDS_0.BAND = 'comm' THEN FEBANDS_0.""CHARGE-RATE"" ELSE 0) AS commSum, SUM(CASE WHEN FEBANDS_0.BAND = 'com1' THEN FEBANDS_0.""CHARGE-RATE"" ELSE 0) AS com1Sum,SUM(CASE WHEN FEBANDS_0.BAND = 'char' THEN FEBANDS_0.""CHARGE-RATE"" ELSE 0) AS charSum,SUM(CASE WHEN FEBANDS_0.BAND = 'cha1' THEN FEBANDS_0.""CHARGE-RATE"" ELSE 0) AS cha1Sum,SUM(CASE WHEN FEBANDS_0.BAND = 'cony' THEN FEBANDS_0.""CHARGE-RATE"" ELSE 0) AS conySum " & _
"FROM PUB.FEBANDS FEBANDS_0, PUB.FEETR FEETR_0 WHERE FEBANDS_0.""FEE-EARNER"" = FEETR_0.""FEE-EARNER"" GROUP BY "SELECT FEETR_0.""FEE-EARNER"", FEETR_0.""CHARGE-RATE"", FEETR_0.NAME ORDER BY FEETR_0.""FEE-EARNER""", DB


Additionally, you should look into doing a JOIN rather than table, table as it would reduce the amount of work that the database has to do in order to build the table.

-T