[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Resultset field order

Posted on 2001-06-08
9
Medium Priority
?
173 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:snocross
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 6167900
I am not very sure what you want, but to add another column to be extracted from table add the column name as shown

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
0
 
LVL 5

Author Comment

by:snocross
ID: 6167903
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?
0
 
LVL 5

Author Comment

by:snocross
ID: 6167913
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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 200 total points
ID: 6168227
There is a confusion here ?@!

Orderby affects the resultset, not the way you retrieved the columns.

eg:
Select col1, col2, col4, col3 from x orderby col2

will order the result by col2 (default ascending) but the order in which columns are retrieved would be the way you specify.

Got it!



0
 
LVL 5

Author Comment

by:snocross
ID: 6177763
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.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 6178186
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
0
 
LVL 5

Author Comment

by:snocross
ID: 6178547
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.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 6178631
yes
0
 
LVL 5

Author Comment

by:snocross
ID: 6178654
Yes?  I didn't ask a yes/no question...
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question