×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Sequencing in Select Statement

Sequencing in Select Statement

Sequencing in Select Statement

(OP)
I have a situation where a person can have more then one item ordered. I need to layout the information as follows:

Person      Item Ordered      Item Description
----------------------------------------------
1            1 of 2           Item1
1            2 of 2           Item2
2            1 of 1           Item1
3            1 of 3           Item3
3            2 of 3           Item2
3            3 of 3           Item1
.
.

The information is in the same table and Item Ordered is in relationship to Person instead of Item Description.

I posted this same question on the Oracle forum, because the project is being done using two databases. Sql Server for development and Oracle for Production. I would like to get the SQL Server version of how to implement the select statement.

If anyone could help me it would be appreciated.


ponderena

RE: Sequencing in Select Statement

What exactly is your question?  What does the data look like that you want to get this result from?

I won't even get into why would you develop a database using one product and implement it using another.  Just make sure you stick to the ANSI standard and avoid using objects that are implemented differently between the two.  I don't use Oracle, but I imagine this would include User-Defined functions, triggers, and stored procedures.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

RE: Sequencing in Select Statement

If I were you I would do everything in my power to change the fact the you are using two extremely different platforms for dev and prod!  I use both Oracle and SQL and I can tell you that you are opening a can huge can of worms.  

You can not test your prod code at all. As an example: What if you have to check for NULL a value.  In Oracle its NVL in SQL its ISNULL.  

What you are doing goes against every best practice rule!

We all have a responsibility to make sure that the programs our companies implement are stable.  I can tell you that you would be promoting un-tested code into production that could impact the integrity of your companies data.

- Paul  
- If at first you don't succeed, find out if the loser gets anything.

RE: Sequencing in Select Statement

(OP)
The data in the table is as follows (There are other fields such as date, user, etc):

id                 31 chars
person name            50 chars
items_order_no             5 chars (could be alphanumeric)
item_description    30 chars
.
.

Yes, it is crazy that two database are being used to develop the system, but the people who make the decisions claimed that in the preliminary stages Oracle was causing problems.
So, they switched to SQL Server as the development database. Of course the end result it that the customer expects to implement Oracle. I suspect that someone was just too lazy to learn Oracle.

ponderena

RE: Sequencing in Select Statement

You have "1 of 2" in a character column? No.

Create two columns, PackageNumber and PackageTotal that are number data type. Even this has its issues because you are storing the packagetotal in two places and the packagetotal is information about all the packages, not each individual one, so it doesn't belong in the packagedetail table, it belongs in the packageheader table.

And I have to chime in and say that developing on SQL Server for production Oracle is like studying Portuguese for two months in order to take your Spanish final exam. You might get some answers right, or close, but you'll fail the class.

I predict that you will spend weeks and weeks converting the whole thing over to Oracle after you are "done" in SQL Server, or you will end up deploying the final product on SQL Server after spending weeks and weeks trying to convert it over to Oracle.

Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.

RE: Sequencing in Select Statement

Was there any reasoning given for why oracle would cause problems in development, but not in production?  In the absence of a damn good reason, I would look for another job.  It sounds like someone is making decisions on technology that has no business being in that position.

Ignorance of certain subjects is a great part of wisdom

RE: Sequencing in Select Statement

(OP)
"1 of 2" is how I want my output to look after formatting.
The number will be converted to character. The number is a count of how many items a person order:

<person 1>    <order number 1> of <total number of order:2> <Description of order number 1>

<person 1>    <order number 2> of <total number of order:2> <Description of order number 2>

<person 2>    <order number 1> of <total number of order:1> <Description of order number 1>
.
.

I asked my manager why Oracle and SQL Server and she stated that they were having load balancing issues (whatever that means). And when errors occured they were not sure how to fix them and it took too much time. At the beginning of the project there may not have been enough Oracle talent to tackle the problems. The Oracle talent available has been here for about 4 years before the project started. So, I wonder how much knowlege they DO have. I feel that an consultant should have been invested in. So, right now when stuff is put into testing for production we have to flip-flop between SQL Server and Oracle.

ponderena

RE: Sequencing in Select Statement

My last suggestion is a reiteration of Alex's post on 25 Jul 07 11:53:

RUN!  Do not walk to your nearest job postings.  This project (code named 'Titanic?') is doomed to fail.

< M!ke >
I am not a hamster and life is not a wheel.

RE: Sequencing in Select Statement

Quote:


The Oracle talent available has been here for about 4 years before the project started.
I don't mean to pile on....
But if the project has been in development for 4 years that should be a clue that something has gone really wrong.

- Paul  
- If at first you don't succeed, find out if the loser gets anything.

RE: Sequencing in Select Statement

All things considered, based on the limited information we have, I think the project is on shaky ground.

Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.

RE: Sequencing in Select Statement

In fact the total count doesn't belong in any table at all, if you can help it.

For SQL and technical ideas, visit my blog, Squared Thoughts.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close