|
I have a sql table with following schema.
DateStamp
BatchID
MixName
MixComment
BatchDestination
BatchComment
MixNo
MixRev
Ing1Name
Ing1Size
Ing1PRM
Ing1AcceptedUnderTol
Ing1SPCSample
Ing1TargetWgt
Ing1ActualWgt
Ing1PartialActualWgt
Ing1PreactWgt
Ing1SlowFeedTime
Ing1FastFeedTime
Ing2Name
Ing2Size
Ing2PRM
Ing2AcceptedUnderTol
Ing2SPCSample
Ing2TargetWgt
Ing2ActualWgt
Ing2PartialActualWgt
Ing2PreactWgt
Ing2SlowFeedTime
Ing2FastFeedTime
Ing3Name
Ing3Size
Ing3PRM
Ing3AcceptedUnderTol
Ing3SPCSample
Ing3TargetWgt
Ing3ActualWgt
Ing3PartialActualWgt
Ing3PreactWgt
Ing3SlowFeedTime
Ing3FastFeedTime
Ing4Name
Ing4Size
Ing4PRM
Ing4AcceptedUnderTol
Ing4SPCSample
Ing4TargetWgt
Ing4ActualWgt
Ing4PartialActualWgt
Ing4PreactWgt
Ing4SlowFeedTime
Ing4FastFeedTime
Ing5Name
Ing5Size
Ing5PRM
Ing5AcceptedUnderTol
Ing5SPCSample
Ing5TargetWgt
Ing5ActualWgt
Ing5PartialActualWgt
Ing5PreactWgt
Ing5SlowFeedTime
Ing5FastFeedTime
Ing6Name
Ing6Size
Ing6PRM
Ing6AcceptedUnderTol
Ing6SPCSample
Ing6TargetWgt
Ing6ActualWgt
Ing6PartialActualWgt
Ing6PreactWgt
Ing6SlowFeedTime
Ing6FastFeedTime
Ing7Name
Ing7Size
Ing7PRM
Ing7AcceptedUnderTol
Ing7SPCSample
Ing7TargetWgt
Ing7ActualWgt
Ing7PartialActualWgt
Ing7PreactWgt
Ing7SlowFeedTime
Ing7FastFeedTime
Ing8Name
Ing8Size
Ing8PRM
Ing8AcceptedUnderTol
Ing8SPCSample
Ing8TargetWgt
Ing8ActualWgt
Ing8PartialActualWgt
Ing8PreactWgt
Ing8SlowFeedTime
Ing8FastFeedTime
Ing9Name
Ing9Size
Ing9PRM
Ing9AcceptedUnderTol
Ing9SPCSample
Ing9TargetWgt
Ing9ActualWgt
Ing9PartialActualWgt
Ing9PreactWgt
Ing9SlowFeedTime
Ing9FastFeedTime
Ing10Name
Ing10Size
Ing10PRM
Ing10AcceptedUnderTol
Ing10SPCSample
Ing10TargetWgt
Ing10ActualWgt
Ing10PartialActualWgt
Ing10PreactWgt
Ing10SlowFeedTime
Ing10FastFeedTime
Ing11Name
Ing11Size
Ing11PRM
Ing11AcceptedUnderTol
Ing11SPCSample
Ing11TargetWgt
Ing11ActualWgt
Ing11PartialActualWgt
Ing11PreactWgt
Ing11SlowFeedTime
Ing11FastFeedTime
Ing12Name
Ing12Size
Ing12PRM
Ing12AcceptedUnderTol
Ing12SPCSample
Ing12TargetWgt
Ing12ActualWgt
Ing12PartialActualWgt
Ing12PreactWgt
Ing12SlowFeedTime
Ing12FastFeedTime
TimeStarted
TimeComp
ManIntervention
HA_Ing1Name
HA_Ing1Size
HA_Ing1PRM
HA_Ing1TargetWgt
HA_Ing1ActualWgt
HA_Ing2Name
HA_Ing2Size
HA_Ing2PRM
HA_Ing2TargetWgt
HA_Ing2ActualWgt
HA_Ing3Name
HA_Ing3Size
HA_Ing3PRM
HA_Ing3TargetWgt
HA_Ing3ActualWgt
HA_Ing4Name
HA_Ing4Size
HA_Ing4PRM
HA_Ing4TargetWgt
HA_Ing4ActualWgt
HA_Ing5Name
HA_Ing5Size
HA_Ing5PRM
HA_Ing5TargetWgt
HA_Ing5ActualWgt
HA_Ing6Name
HA_Ing6Size
HA_Ing6PRM
HA_Ing6TargetWgt
HA_Ing6ActualWgt
HA_Ing7Name
HA_Ing7Size
HA_Ing7PRM
HA_Ing7TargetWgt
HA_Ing7ActualWgt
HA_Ing8Name
HA_Ing8Size
HA_Ing8PRM
HA_Ing8TargetWgt
HA_Ing8ActualWgt
HA_Ing9Name
HA_Ing9Size
HA_Ing9PRM
HA_Ing9TargetWgt
HA_Ing9ActualWgt
HA_Ing10Name
HA_Ing10Size
HA_Ing10PRM
HA_Ing10TargetWgt
HA_Ing10ActualWgt
HA_Ing11Name
HA_Ing11Size
HA_Ing11PRM
HA_Ing11TargetWgt
HA_Ing11ActualWgt
HA_Ing12Name
HA_Ing12Size
HA_Ing12PRM
HA_Ing12TargetWgt
HA_Ing12ActualWgt
HA_Ing13Name
HA_Ing13Size
HA_Ing13PRM
HA_Ing13TargetWgt
HA_Ing13ActualWgt
HA_Ing14Name
HA_Ing14Size
HA_Ing14PRM
HA_Ing14TargetWgt
HA_Ing14ActualWgt
HA_Ing15Name
HA_Ing15Size
HA_Ing15PRM
HA_Ing15TargetWgt
HA_Ing15ActualWgt
HA_Ing16Name
HA_Ing16Size
HA_Ing16PRM
HA_Ing16TargetWgt
HA_Ing16ActualWgt
HA_TimeStarted
HA_TimeComp
I have been asked to sort of transpose this one long record, into another table that has multiple records associated with one batchid. So essentially instead of 11 repeating sets of columns for ingredient information they want multiple records for one batch id in which there is a seperate record for each ingredient and the data is stored under one set of columns from the 11 duplicate ingredient columns sets above.
So they want...
DateStamp
BatchID
MixName
MixComment
BatchDestination
BatchComment
MixNo
MixRev
Bin Number (Instead of a set of columns for each bin)
IngName
IngSize
IngPRM
and this would repeat for each ingredient within that batch.
My question is how can i transpose the above table which already exists into the lower table? Would I use a trigger on the top table? And insert into another table?
|
|
|
|
|
You may want to create a view which satisfies the requirement rather than a whole new table.
Just a thought. ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Good luck
|
|
|
|
|
How would I do what I described with a view? I am still learning with SQL server?
|
|
|
|
|
The keyword you're looking for is UNPIVOT.
What database are you using?
|
|
|
|
|
What a classic, this is the most basic of error in database design and now you have to deal with the issue.
Is the change in structure going to be a permanent change to the data structure or is it just to facilitate some reporting requirement?
I would go with the view solution if it is for reporting. A series (16 of them) of UNION queries would do the job but t would be ugly.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
This will be used only for reporting purposes.
|
|
|
|
|
Then go with the view idea
SELECT commonfields,
Ing1Name AS fieldanme,
Ing1Size,
Ing1PRM,
Ing1AcceptedUnderTol,
Ing1SPCSample,
Ing1TargetWgt,
Ing1ActualWgt,
Ing1PartialActualWgt,
Ing1PreactWgt,
Ing1SlowFeedTime,
Ing1FastFeedTime
FROM SomTable
UNION ALL
SELECT commonfields,
Ing2Name,
Ing2Size,
Ing2PRM,
Ing2AcceptedUnderTol,
Ing2SPCSample,
Ing2TargetWgt,
Ing2ActualWgt,
Ing2PartialActualWgt,
Ing2PreactWgt,
Ing2SlowFeedTime,
Ing2FastFeedTime
FROM SomTable
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I want to build a small bank application using sql oracle server
as back end and c# as the front end for my practice.
|
|
|
|
|
Okay. Put together the requirements, and start planning it out. This is how you start any project. People are not going to just drop what they are doing and give you code to any kind of application.
""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
What sort of banking application? HR? Trading? M&A? Retail?
Why don't you start by reading some books on programming before you decide you want to create something as complex as a 'banking' application?
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
I'm sorry I only do big banks. Can't help you out. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Chris Meech wrote: I'm sorry I only rob banks. Can't help you out.
FTFY
thatrajaNobody remains a virgin, Life screws everyone 
|
|
|
|
|
|
+5 for you! I've run across this site many years ago, and it's nice to see that it is still around
""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Really that's a nice site. Last 2 years I have used that site for 100s of questions in CP. BTW thanks.
thatrajaNobody remains a virgin, Life screws everyone
|
|
|
|
|
Ok.. you first have to plan out the features you wanna put in your application and then will fix the database structure and start coding modularized way.
Rest you can seek assistance any time here at Code Project for any type of stuck outs.
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
Member 9071414 wrote:
I want to build a small bank application using sql
oracle server
as back end and c# as the front end for my practice.
Depending exactly on what those terms mean then I wouldn't suggest that you do this yourself.
The primary requirement for something that manages money is it needs to be secure. Secondary to that is fulfilling the business needs of the company.
Normally some experience is necessary to correctly fulfill the first requirement. Failing to fulfill the first requirement could and probably will lead to monetary losses.
|
|
|
|
|
Hi everyone! I have two roles. I need to make members in one role be able to view members in another role. I'm stuck and don't know what to do now. Please help.
|
|
|
|
|
Are you using the ASP membership provider or something else ?
What type of information do these members need to see about each other ?
|
|
|
|
|
For this you can make Role 1 as Admin or Priorty Role.. which has command over Role 2.
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
Hello,
WE are moving to a 64 bit machine. The usual provider MSDAORA is not avaliable. Started to use the ORAOLEDB.ORacle provider. While this works, there is a data issue. One of them show up as data conversion to type numeric. This worked perfectly on the 32bit environment.
Which is the ideal provider to use on a 64 bit machine?
Much appreciated!!
|
|
|
|
|
I suspect you don't have many choices.
As you stated Microsoft doesn't do a 64 bit version.
And presumably you have spent the time to verify the actual problem in the Oracle version.
At one time there was a 3rd party company that created database drivers. But searching quickly I can't find them. You might have better luck.
Other than that I would suggest modifying your code. You can always have your SQL return a string, rather than numeric, and then process that in your C# code.
|
|
|
|
|
A bit of an issue.
The select statement has about 450 variables(?), I know, not something I created.
The select statement is not executing stating it is exceeding the 8000 characters selection limit.
This is the first time I am ecountering a limit on selection.
|
|
|
|
|
vanikanc wrote: The select statement is not executing stating it is exceeding the 8000 characters selection limit.
I can assure that a statement limit has existed for years. Pretty sure I saw it in Oracle 8.
So in terms of a previous driver you got lucky because you were at the border anyways. And depending on the data it could have gone over with the older driver.
The only solution for that, which I can think of (besides getting rid of all those fields) is to encode the data into xml and then return is as a blob and then re-extract.
|
|
|
|
|
I have to update several records and each record has a unique update. I'm very new to sql so any suggestions would be appreciated.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
