Order By Clause

[lynchg]

I have a work table in DB2 in which I would like to arrange the records in a particular order. I use an insert statement to append records into the table and I would like to have them odered as I want.

I tried:

Insert Into tblXXX (Field1, Field2,...)
Select FieldA, FieldB,...
From tblZZZ
Where YYYYYYY
Order By FieldC, FieldD

Error message says the "Order" reserved word is invalid.

I tried to create a view selecting all fields from tblXXX and ordering on the fields I want, I get an error stating that the Order By clause is not allowed.

I just want to get a set of data in the order I want so that I can use tblXXX or a view based on tblXXX as a datasource for a Crystal report.

I am relatively new to DB2 and I am amazed and frustrated by the fact that I can't seem to perform a basic operation.

Am I out of my mind here?

 

[sathyarams]

Any order you insert the records, DB2 does not gurantee to return the records in the same order (or any order for that matter) ... It is your 'responsibility' to order the records in your SELECT Statement ...

Considering this fact, your efforts to insert records in a particular order is not the solution ...

Cheers
Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[FearsomeFastFemale]

You cannot use the insert command to affect the table order. You can control the order when it is read using order by. The index is defined by the table definiton, you can also have views to see subsets of the data.

Regards

 

[lynchg]

I would like to have a datasource that I can refer to by name from Crystal Reports. I would like to have the records in this datasource in the order I want so that I will be able to display the correct data on the report. I was thinking that I could create a view with an order by clause to accomplish this.

I was wrong. I can't believe that DB2 doesn't allow any kind of a persistent "object" to have it's records arranged in a particular order. I can't refer to a select statement as a datasource for a report.

Am I the first person to have a need like this?

 

[RiverGuy]

I haven't heard of DB2 not supporting views. But then I guess it depends on which version you have. Have you tried a CREATE VIEW statement?

 

[blom0344]

I have little knowledge off Crystal reports (using Business Objects), but I think most reporting tools rely on reporting capabilities to sort data. In that case data is fetched regardless of order and within the report all structuring is accomplished. (client-side)

Does Crystal Reports work differently in that respect?

Anyway, relational databases do not bother with ordering data within tables. The interaction with a table can enforce the ordering (like the order by in an SQL)

T. Blom
Information analyst
tbl@shimano-eu.com

 

[lynchg]

I come from a Microsoft background (VB, Access, SQL Server) and I developed a style of reporting that relied on SQL to carry the load. I was naively under the assumption that that it is normal to create a query or a view that can feed a report a set of records in a certain order. DB2 is not like this. The report was "completed" using Access as a testing database because our DB2 database was still under construction. Now I have come across this issue where you CANNOT have an Order By clause in a view. It may be possible to do an Order By from within an SP. If not I will be forced to try to rework the report, not an easy task as there is already a lot of work going on inside the report.

I have to say that my first real exposure to DB2 has left me extremely unimpressed. This issue is just one of many; data connection, object naming limitations, it can't handle complex querying, the fact that views don't really exist as objects and disappear when any of their constituent tables are rebuilt, etc...

 

[blom0344]

Sorry that you are unimpressed, but that will fade...
Actually DB2 SQL can handle VERY complex stuff, but it has its strength at different fields than T-SQL.

Database views are in fact database-'owned' pieces of SQL and in most databases an order by is not allowed. As mentioned before the reporting tool should take care of it, since ordering information is where you have it for in the first place.

data connections should not pose a problem, but since DB2 is not native to windows (as SQL server is) the MS product will have the advantage, but that is no more than predictable.

If you are used on the pecularities of both Access / SQL server you may need time to adapt, but there are loads of things that I can't do with access that I can do with DB2

You may download a copy of the SQL cookbooks by Graeme Birchall to get a good oversight of SQL in DB2 environment

Believe me, things are perhaps a little different that you were used to, but it is all there........



T. Blom
Information analyst
tbl@shimano-eu.com

 

[PruSQLer]

Good points by T. Blom.

My first experiences with RDBs were with using Access and I thought it was great. Then I started working with DB2 and realized how much more powerful it is. Now when people request us to develop an application in Access, we immediately ask if we can bump it up to DB2 since we know it will afford great advantages in handling large amounts of data, subsequent querying speed and security. We work with millions of rows in our tables and it wouldn't be possible to do in Access what we do with DB2, right on our PCs. Stick with it, you might develop a grudging admiration for DB2.