Sequencing in Select Statement 1

[getjbb]

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

 

[AlexCuse]

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

 

[ptheriault]

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.

 

[getjbb]

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

 

[ESquared]

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.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[AlexCuse]

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

 

[getjbb]

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

 

[LNBruno]

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.

 

[ptheriault]

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.

 

[ESquared]

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

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

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

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]