snocross
asked on
SQL Resultset field order
Here is my SQL:
Qry.SQL = "select A.ABAN8, A.ABMLNM, A.ALADD1, A.ALADD2, A.ALADD3, A.ALADD4, A.ALCTY1, A.ALADDS, A.ALADDZ, C.A5CLMG, B.MAAN8, C.ABALPH, D.ABALPH, D.CAANAME from EDWDATA1.F01901 A, EDWDATA1.F0150 B, EDWDATA1.F01901 C, EDWARDQRY.F550101B D where (A.ABAT1 = 'IP' or A.ABAT1 = 'C') and A.ABAN8 = B.MAPA8 and B.MAOSTP = 'PAY' and B.MAAN8 = C.ABAN8 and C.A5CLMG = D.BROKER order by B.MAAN8, A.ABAN8"
To access the result set values I use:
doc.CustomerNumber = Res.GetValue(1)
doc.BrokerNumber = Res.GetValue(2)
ETC, ETC
doc.PersonName = Res.GetValue(14)
My question is I want to add a new field but I don't want to have to realign all the code. I want to add a 15th value. It's confusing to know which order each field will be in especially since there are three files I'm reading from. I want my new field A.xyzfield to show up as the last value or Res.GetValue(15). How should I structure the HTML?
-Snocross
Qry.SQL = "select A.ABAN8, A.ABMLNM, A.ALADD1, A.ALADD2, A.ALADD3, A.ALADD4, A.ALCTY1, A.ALADDS, A.ALADDZ, C.A5CLMG, B.MAAN8, C.ABALPH, D.ABALPH, D.CAANAME from EDWDATA1.F01901 A, EDWDATA1.F0150 B, EDWDATA1.F01901 C, EDWARDQRY.F550101B D where (A.ABAT1 = 'IP' or A.ABAT1 = 'C') and A.ABAN8 = B.MAPA8 and B.MAOSTP = 'PAY' and B.MAAN8 = C.ABAN8 and C.A5CLMG = D.BROKER order by B.MAAN8, A.ABAN8"
To access the result set values I use:
doc.CustomerNumber = Res.GetValue(1)
doc.BrokerNumber = Res.GetValue(2)
ETC, ETC
doc.PersonName = Res.GetValue(14)
My question is I want to add a new field but I don't want to have to realign all the code. I want to add a 15th value. It's confusing to know which order each field will be in especially since there are three files I'm reading from. I want my new field A.xyzfield to show up as the last value or Res.GetValue(15). How should I structure the HTML?
-Snocross
ASKER
Maybe I need to clarify my question just in case...
If my SQL is simple like this:
Qry.SQL = "select A.ABAN8, A.ABMLNM, A.ALADD1 from EDWDATA1.F01901 A"
I'm assuming Res.GetValue(1) will be ABAN8 and Res.GetValue(2) will be ABMLNM and Res.GetValue(3) will be ALADD1. Now if I move the fields around in the SQL will they get returned in that new order? I'm just trying to figure out how the result set fields are ordered. Are they basicly ordered in the order that I type them in the SQL?
If my SQL is simple like this:
Qry.SQL = "select A.ABAN8, A.ABMLNM, A.ALADD1 from EDWDATA1.F01901 A"
I'm assuming Res.GetValue(1) will be ABAN8 and Res.GetValue(2) will be ABMLNM and Res.GetValue(3) will be ALADD1. Now if I move the fields around in the SQL will they get returned in that new order? I'm just trying to figure out how the result set fields are ordered. Are they basicly ordered in the order that I type them in the SQL?
ASKER
Heman, if you do not use OrderBy then what order does it put the columns? The order that I typed them regardless of the order on the source files?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So your example would display like this??
col2 (sorted), col1, col4, col3
I've done some testing now and if I don't use an OrderBy statement it seems to list them in the exact order that I write them in the sql.
col2 (sorted), col1, col4, col3
I've done some testing now and if I don't use an OrderBy statement it seems to list them in the exact order that I write them in the sql.
no not like that
If your table is like this
col1 col2 col3 col4
x 3 xx a
y 2 yy b
z 1 zz c
The the query will return the result as
z 1 zz c
y 2 yy b
x 3 xx a
See the difference.
~Hemanth
If your table is like this
col1 col2 col3 col4
x 3 xx a
y 2 yy b
z 1 zz c
The the query will return the result as
z 1 zz c
y 2 yy b
x 3 xx a
See the difference.
~Hemanth
ASKER
Ok, but I don't care about the sorting, I am worried about the arrangement of the columns. Anyway, the points are yours but my conclusion is that it arranges them in the order that I list them in my SQL.
yes
ASKER
Yes? I didn't ask a yes/no question...
select A.ABAN8, A.ABMLNM, A.ALADD1, A.ALADD2, A.ALADD3, A.ALADD4, A.ALCTY1, A.ALADDS, A.ALADDZ,
C.A5CLMG, B.MAAN8, C.ABALPH, D.ABALPH, D.CAANAME , A.xyzfield
Orderby governs the ordering
GetValue gives the column value, not the row value. So ordering should not be a problem in your case.
~Hemanth