Solved

SQL Resultset field order

Posted on 2001-06-08
9
162 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
  • 5
  • 4
9 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 50 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:snocross
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
yes
0
 
LVL 5

Author Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Adjusting times for a meeting room 3 129
Lotus Notes Calendar Issue 5 101
Lotus Domino server 11 55
Lotus Domino Server 9 4 74
For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now