| minus quantity b/w two table (merged 2) [message #625225] |
Fri, 03 October 2014 05:48  |
 |
akhter
Messages: 30
Registered: April 2014
Location: karachi
|
Member |
|
|
Any one know the query of minus b/w two table for exmple.... that current stock has 10 pen and dispatch 5 pen from current stock so remaining quantity must be 5 like this?
|
|
|
|
|
|
|
|
| Re: minus quantity b/w two table [message #625231 is a reply to message #625225] |
Fri, 03 October 2014 07:33  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Surely anyone with a crystall ball knows it. For the other ones, it depends on the structure of those "two table" and the required result.
Seems to me like a simple outer join, but, as the question is very vague, this is just an idea based on qualified guess.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: minus quantity b/w two table [message #625243 is a reply to message #625237] |
Fri, 03 October 2014 07:55 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
akhter wrote on Fri, 03 October 2014 14:40there is two table one is current stock (column1)and second one is dispatch advice(column1) ...now there is quantity of 10 pen in current stock then i dispatched 5 pen quantity from current stock ,,,now remaining quantity in current stock will be 5 of pen....
flyboy ....did you got
No, the only thing I would say that your tables are wrongly designed as they are not in 1NF (as column1 does not seem to be unique).
Happy waiting for somebody with a crystal ball to decipher your current table definitions and requirements.
|
|
|
|
|
|
| Re: minus quantity b/w two table [message #625246 is a reply to message #625244] |
Fri, 03 October 2014 08:06 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
@akhter - no one can answer your question because it is to vague.
Answer depends on structure of tables, relationship between them and expected result. You've given us none of those.
Without them we would need a crystal ball to determine what you need.
So stop complaining and supply more details.
|
|
|
|
| Re: minus quantity b/w two table [message #625247 is a reply to message #625244] |
Fri, 03 October 2014 08:07 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
|
Sweet pea, don't take a huff. You're the one asking (badly) for help here. You've been asked multiple times for a test case and haven't supplied it. you've been given some good advice from flyboy (your tables appear to be poorly designed) to which you snapped a snidey wee answer, demanding a solution to your very poorly construted question. This is not the best way to get people to give up their free time to help you.
|
|
|
|
|
|
|
|
|
|
| Re: minus quantity b/w two table [message #625251 is a reply to message #625250] |
Fri, 03 October 2014 08:21 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Test Case
For example:
create table my_test (a number, b varchar2(30), c number);
insert into my_test values (1, 'x', 2);
insert into my_test values (2, 'm', 10);
insert into my_test values (3, 'r', 2);
insert into my_test values (4, 't', 2);
i want to find the row with the highest value. for the above data, i would expect the followinf result:
a b c
2 m 10 [Updated on: Fri, 03 October 2014 08:26] Report message to a moderator |
|
|
|
|
|
|
|
| Re: minus quantity b/w two table [message #625254 is a reply to message #625252] |
Fri, 03 October 2014 08:33 |
|
akhter
Messages: 30
Registered: April 2014
Location: karachi
|
Member |
|
|
create table current_Stock (Code number, item_name varchar2(30), Item_qty number);
insert into my_test values (1, 'pen', 2);
insert into my_test values (2, 'mouse', 10);
insert into my_test values (3, 'pencil', 2);
insert into my_test values (4, 'monitor', 2);
create table dispatch_note (Code number, item_name varchar2(30), Item_qty number);
Now i want that when i take quantity of mouse from current_stock which is 10 in dispatch_note table, so remain quantity of mouse will be 5 in current stock.....now i think you got it
|
|
|
|
|
|
| Re: minus quantity b/w two table [message #625256 is a reply to message #625255] |
Fri, 03 October 2014 08:38 |
|
akhter
Messages: 30
Registered: April 2014
Location: karachi
|
Member |
|
|
Sorry
pablolee
i do correction
create table current_Stock (Code number, item_name varchar2(30), Item_qty number);
insert into current_Stock values (1, 'pen', 2);
insert into current_Stock values (2, 'mouse', 10);
insert into current_Stock values (3, 'pencil', 2);
insert into current_Stock values (4, 'monitor', 2);
create table dispatch_note (Code number, item_name varchar2(30), Item_qty number);
Now i want that when i take quantity of mouse from current_stock which is 10 in dispatch_note table, so remain quantity of mouse will be 5 in current stock.....now i think you got it
|
|
|
|
| Re: minus quantity b/w two table [message #625257 is a reply to message #625254] |
Fri, 03 October 2014 08:38 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
You have still not supplied data for dispatch_note, nor have you told us what the end result should be. For example, are you wanting to update your current_stock table, are you wanting to run a select that will display the stock level accounting for the current stock minus the dispatched stock etc. If you want a clear answer, you have to ask a clear question.
Just a heads up here. I think that this is a homework assignment, therefore I am not going to write the code for you. i will, however, happily help you write it yourself (once we have the requirements panned out).
[Updated on: Fri, 03 October 2014 08:39] Report message to a moderator |
|
|
|
|
|
|
|
| Re: minus quantity b/w two table [message #625262 is a reply to message #625261] |
Fri, 03 October 2014 08:57 |
|
akhter
Messages: 30
Registered: April 2014
Location: karachi
|
Member |
|
|
BlackSwan
now try to understand that i will take one row from current_stock (1, 'pen', 2) and put in dispatch_note(1, 'pen', 1)
now see the quantity which i took from current_stock table which was 2 after taking out 1 quantity from current_stock table then remain quantity in current_stock table must be display like this (1, 'pen', 1)
[Updated on: Fri, 03 October 2014 08:57] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: minus quantity b/w two table [message #625269 is a reply to message #625266] |
Fri, 03 October 2014 09:09 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
No, you don't join tables using constraints.
Show me a select statement that joins 2 tables. You choose the tables, you choose the columns, they can be completely fictitious or they can be tables that you are familar with, your choice. post the code here using code tags:
type
[code]Your code goes in here[/code]
|
|
|
|
| Re: minus quantity b/w two table [message #625270 is a reply to message #625268] |
Fri, 03 October 2014 09:10 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
akhter wrote on Fri, 03 October 2014 15:07Dear pablolee
i am not refusing to post full test case ,,i told you every thing ... and dispatch_note will be empty until we do transaction from current_stock
Yes, you are. I posted very clearly what a test case is. You failed to supply the insert for your second table and you have still not clearly stated your requirement. you do the first bit and I'll help you with the second.
I'm going to gues that you require that the item_qty value in the current_stock table needs to be updated based on the item_qty value in the dispatch table for matching items. Would that be correct?
Waiting on the first bit from you.
[Updated on: Fri, 03 October 2014 09:12] Report message to a moderator |
|
|
|
|
|
| Re: minus quantity b/w two table [message #625272 is a reply to message #625271] |
Fri, 03 October 2014 09:15 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
|
I have no intention of taking this conversation outwith this forum (unless you want to start paying me my contractor rate, in which case, once you have signed a purchase order and paid for a minimum of 1 day up front, I'll happily spend hours trying to eke out your requirements from you and write the code for your homework assignment.)
|
|
|
|
|
|
| Re: minus quantity b/w two table [message #625274 is a reply to message #625273] |
Fri, 03 October 2014 09:21 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
No - You've been told very clearly, several times, to provide a proper test case along with detailed requirements.
You do that, here, and we'll do our best to help you for free.
If don't do that, you won't get any help.
|
|
|
|
|
|
| Re: minus quantity b/w two table [message #625276 is a reply to message #625273] |
Fri, 03 October 2014 09:24 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
akhter wrote on Fri, 03 October 2014 15:18hmmmmmmmmmmmm this one the reason that you do not want to understand my query until i pay...............>>>>>
No, it was a sarcastic response. I would have quoted an exhorbitantly high hourly rate, one that you would never have taken up the offer on (unless you had [waaaaaaay] more money than common sense. My point is that I am NOT going to take your issue offline. You either post the details here or you don't get assistance for free. I've got about 20 minutes left before I head home so no doubt someone else will continue to ask for your requirements.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
