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
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
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
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
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
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
Ignorance of certain subjects is a great part of wisdom
RE: Sequencing in Select Statement
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
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
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
Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.
RE: Sequencing in Select Statement
For SQL and technical ideas, visit my blog, Squared Thoughts.