|
Here's my problem. I'm using Oracle 8i and I do not have the option of changing the design of the database. I have a master table with some fields and a unique key Code, something like this:
Code Field1 Field2
11111 1 2
11112 2 3
...
I also have another child table that contains some data that is related to my master table. It has two fields Key1 and Key2 which combined are unique:
Key1 Key2 Data1
1 1 1
1 2 5
2 1 12
2 2 3
...
Now, Key1 is linked to the master table through another "link" table:
Code Key1
11111 1
11112 2
...
Now what I want to end up with is this:
Code Field1 Field2 Key2_1data Key2_2data ...
11111 1 2 1 5
11112 2 3 12 3
In other words, the original table with fields added with the value of Data1 from the child table for each value of Key2. I hope that made sense. So I tried something like this:
SELECT master.code, master.Field1, master.Field2, (SELECT child.Data1 FROM child WHERE child.Key1 = link.Key1 AND child.Key2 = 1) AS Key2_1data, (SELECT child.Data1 FROM child WHERE child.Key1 = link.Key1 AND child.Key2 = 2) AS Key2_2data FROM master, link WHERE master.Code = link.Code (+)
This works and can be expanded for an arbitary number of values of Key2 by adding more of these nested select statements and there are actually only 10 values of Key2 for me to worry about, but that could change at some point. It just seems like it is a hugely ineffecient way to do things through. Is there a better way?
|
|
|
|
|
I think this is what you're looking for. I'm not sure if you need the outer join (plus sign) or not. Do you still want to show master data if there is no link/child data? If not, you don't; if so, you'll need one on the latter side of the three code comparisons.
SELECT
m.code,
m.field1,
m.field2,
c1.data1,
c2.data1
FROM master m, link l, child c1, child c2
WHERE m.code = l.code
AND l.code = c1.code
AND l.code = c2.code
AND c1.key1 = c2.key1
AND c1.key2 = 1
AND c2.key2 = 2
Michael Flanakin
Web Log
|
|
|
|
|
Thanks for your help. You code works but it should read l.key1 = c1.key1 and l.key1 = c2.key2 (the child table doesn't contain a code field).
Now I do want to display records from the master table that have no child entry or only have one child put I can't quite seem to figure out the right places to put the (+) signs.
|
|
|
|
|
Good catch. I thought it was odd that the code would be in the child table, too. Guess I got ahead of myself when writing the SQL. It's kinda hard to write that stuff when you don't have the tables to play with in front of you.
The plus sign represents an outer join. You typically do an outer join to a foreign key column that might be null. So, since the p.code column is the primary key, that comparison should be p.code = l.code(+) . I don't think you need one on the key comparisons. If so, they probably go on the l.key1 column. I'd have to play with it to be sure.
Also, just to make things easier on people who read your SQL, I'd suggest you put the outer join columns on the right side of the operator. Not that it's a huge deal, but I personally find it a bit easier to read that way. Maybe that's just because it's the way I'm used to it, tho.
Michael Flanakin
Web Log
|
|
|
|
|
hi all,
I am new to this group.
i have to create new aplication trigger for the
table.
give some id or Url address.
thanks in advance
rs_net
|
|
|
|
|
Given the vagueness of your request you could trying reading this article: http://msdn.microsoft.com/msdnmag/issues/03/12/DataPoints/[^]
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
First of all: HAPPY NEW YEAR TO YOU ALL.
The following code executed ONCE and it works just fine. But it fails for subsequent calls, given the following error msg: “No Transaction in Active”.
Private Sub Update_Account()
Dim T_OwnRec, A_OwnRec, P_OwnRec As Boolean
T_OwnRec = Data.Get_TCount
A_OwnRec = Data.Get_ACount
P_OwnRec = Data.Get_PCount
Try
'****ERROR
Data.Con.BeginTrans()
'****ERROR
If Update_All(T_OwnRec, A_OwnRec, P_OwnRec) = True Then
Data.Con.CommitTrans()
Trigger_Thread2LoadTags()
Else
Data.Con.RollbackTrans()
MiniMe = False
MsgBox("The Account could not be updated!!!", MsgBoxStyle.Information, "Unexpected error at:" & t_Str)
MiniMe = True
End If
Catch
Data.Con.RollbackTrans()
Handle_Error(Err, "Update_Account")
End Try
End Sub
The error is generated at the "Data.Con.BeginTrans". CAN ANYBODY OFFER SOME HELP. THANKS A LOT GUYS/GIRLS
A VERY LOST NEW GUY
|
|
|
|
|
I'm confused. What technologies are you using?
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
I am "trying" to code using VB.Net and ADO.
|
|
|
|
|
alexvw wrote: I am "trying" to code using VB.Net and ADO.
That's what is confusing. ADO.NET has BeginTransaction, not BeginTrans. (I'm assuming SQL Server on the back end, because you didn't fully answer my question)
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hi Guys, first of all Thanks for offering help. I found out what was causing the error (still do not understand why). The three top variables are being loaded by running queries and each function is supposed to return TRUE/FALSE (they do). I understood that Local Variables (within subs or functions) were automatically disposed off, once the focus leaves the sub or function. But my Local Recordsets are not being disposed off, and for some reason that goes beyond my little knowledge is preventing me from starting a Transaction.
this is weird. Because I am not hardcoding the dispossal of any of my local variables at all, and this is the only piece of code that generates this error.
Once again. Thank you guys for offering your help. HAVE A VERY HAPPY NEW YEAR.
Alex. (STILL A VERY LOST NEW GUY)
|
|
|
|
|
Check the state of the connection. A transaction cannot be initiated with the state of the connection other than OPEN.
Happy new year to all of u
Akif
|
|
|
|
|
Unfortunately the Connection is OPEN, I am able to execute other commands through my app.
|
|
|
|
|
Hi Guys, first of all Thanks for offering help. I found out what was causing the error (still do not understand why). The three top variables are being loaded by running queries and each function is supposed to return TRUE/FALSE (they do). I understood that Local Variables (within subs or functions) were automatically disposed off, once the focus leaves the sub or function. But my Local Recordsets are not being disposed off, and for some reason that goes beyond my little knowledge is preventing me from starting a Transaction.
this is weird. Because I am not hardcoding the dispossal of any of my local variables at all, and this is the only piece of code that generates this error.
Once again. Thank you guys for offering your help. HAVE A VERY HAPPY NEW YEAR
Alex. (STILL A VERY LOST NEW GUY)
|
|
|
|
|
alexvw wrote: Recordsets
ADO.NET does not have recordsets. Classic ADO does. You should be using ADO.NET. This may be an incompatibility issue.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
As Colin said, you should be using ADO.NET, not classic ADO. The reason your recordsets are not being disposed is that they are not managed objects, but wrappers around COM objects (whose lifetime you must manage yourself). ADO.NET is supported by the System.Data.Oledb namespace in .NET.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
What do u mean by recordset here. Is it traditional recordset of ADO or u r talking abt DataReader of ADO.Net.
Plz give the details of "Data" or atleast "Data.Get_TAccount"
Akif
|
|
|
|
|
how can i use database(access) in pascal .if it is impossible please give me another solusion.i search the web but did not get any advantage
|
|
|
|
|
aspnet_22 wrote: how can i use database(access) in pascal
PASCAL? Do you mean Delphi or TurboPASCAL? In tp it is imho impossible. With Delphi Prof you can use Access-DBs with die ADO-components.
'A programmer is just a tool which converts caffeine into code'
|
|
|
|
|
if do you mean we can use delfi utilities in tp7?if possible give me some samples?
|
|
|
|
|
aspnet_22 wrote: do you mean we can use delfi utilities in tp7
No, you can't do this. If you want to use PASCAL buy Delphi 2006 or if you prefer PASCAL for .NET u should buy VS2005 >= standard (standard, prof, team suite) and Chrome 1.5[^].
André
[edit] correct spelling [/edit]
'A programmer is just a tool which converts caffeine into code'
|
|
|
|
|
VickyC# wrote: Does anyone knows if there is a tool in the market that could optimize an SQl Query. I am looking for something that would do a mathematical way of optimization.
Isn't that what the query optimiser in SQL Server already does?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
|
|
|
|
|
|
VickyC# wrote: looking for a mathematical/relational optimization tool
Could you elaborate on what you need? Mr. Mackay's advise is probably the best. Only other optimization tool that could work, according to Michael Abrash's Zen of Code Optimization, is right between your ears
Paul
|
|
|
|
|
VickyC# wrote: I guess if this does not say it all then I am asking the wrong people.
Might not be a matter of asking the wrong people. Maybe there isn't an answer. Don't vote people down who are trying to help
|
|
|
|