SQL NULL [message #416948] |
Wed, 05 August 2009 06:05  |
kilimanjaro
Messages: 151 Registered: May 2009 Location: Tanzania
|
Senior Member |
|
|
HI PARTENERS.please get me off the hooke.
i have two tables,sold and purchases.both tables have stock_code and quantity.Now i want to get the balance of purchases quantity minus sold quantity.like this.
select a.stock_code,-sum(b.quantity)from purchases a,sold b
where a.stock_code=b.stock_code
group by a.stock_code,a.quantity.
The problem is ,i dont get a.quantity when no matching records in sold table.hw cn i et over this???
PLEASE ASSIST.
|
|
|
|
Re: SQL NULL [message #416950 is a reply to message #416948] |
Wed, 05 August 2009 06:24   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
milikiel wrote on Wed, 05 August 2009 13:05 | The problem is ,i dont get a.quantity when no matching records in sold table.hw cn i et over this???
|
Because the tables are inner joined, the entire row from PURCHASES with no corresponding row(s) in SOLD is not displayed.
If this is the problem, use outer join instead.
|
|
|
Re: SQL NULL [message #417122 is a reply to message #416948] |
Thu, 06 August 2009 04:14   |
kilimanjaro
Messages: 151 Registered: May 2009 Location: Tanzania
|
Senior Member |
|
|
Thankyou partners for your quick reply.
yes i have used the outer join but the value of the purchases quantity doesnt appear when no corresponding value
select a.STOCK_CODE,a.quantity - sum(b.quantity)p
from en_daily_purchases a,en_outdated_items2 b
where a.STOCK_CODE=b.item_code(+)
GROUP BY a.QUANTITY,a.STOCK_CODE
/
results
STOCK_CODE P
---------- ----------
305 8
2162
1202 195
i want the value of p-value of stock code 2162 to show up.
|
|
|
|
|
Re: SQL NULL [message #417125 is a reply to message #416948] |
Thu, 06 August 2009 04:25   |
kilimanjaro
Messages: 151 Registered: May 2009 Location: Tanzania
|
Senior Member |
|
|
hi ayush
Thankyou again for the reply.how do i overcome this then??? hw do i use code tags in regard to this problem??? examples please.
Thankyou.
is there a way of doin this within oracle forms?? am trying to get an LOV
|
|
|
Re: SQL NULL [message #417126 is a reply to message #417125] |
Thu, 06 August 2009 04:29   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
milikiel wrote on Thu, 06 August 2009 10:25 | how do i overcome this then
|
Quote: | You can use NVL function to eliminate NULLS with 0
|
RTFM
|
|
|
Re: SQL NULL [message #417127 is a reply to message #417125] |
Thu, 06 August 2009 04:29   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
You use CODE tags to format your post. It's explained in the big, blinking "READ THIS BEFORE POSTING" post at the top of the forum.
|
|
|
|
Re: SQL NULL [message #417130 is a reply to message #417125] |
Thu, 06 August 2009 04:31   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
You know your data model and data stored within the tables; unless you provide sample test case (which includes CREATE TABLE and INSERT INTO statements) - it would be input, as well as properly formatted output (desired result), it is difficult to help.
As of your formatting problems, well, you should have done that ages ago - read OraFAQ Forum Guide. Pay attention to "How to format your post" section.
P.S. Oh well, I guess that 4 messages pointing to the same should be enough (although, I wouldn't put my money on that).
[Updated on: Thu, 06 August 2009 04:32] Report message to a moderator
|
|
|
|
|
|
Re: SQL NULL [message #417156 is a reply to message #417154] |
Thu, 06 August 2009 05:33   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
So you prefer non-sensical answers over people pointing you to the location of documents where you can find plenty of working examples.
Maybe this forum isn't exactly the right source for you then..
|
|
|
|
Re: SQL NULL [message #417169 is a reply to message #417166] |
Thu, 06 August 2009 06:14   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
That answer with the NVLs round the Stock code certainly looks nonsensical to me.
I'd have used something like:SELECT a.stock_code,
a.quantity - Sum(nvl(b.quantity,0)) p
FROM en_daily_purchases a,
en_outdated_items2 b
WHERE a.stock_code = b.item_code (+)
GROUP BY a.quantity,
a.stock_code
|
|
|
Re: SQL NULL [message #417172 is a reply to message #417166] |
Thu, 06 August 2009 06:23   |
ayush_anand
Messages: 417 Registered: November 2008
|
Senior Member |
|
|
Quote: | Are u saying the answer is non-sensical ...
|
WHERE Nvl(a.stock_code,'-99999999') = Nvl(b.item_code (+),'-99999999')
This will only join null stock_codes with Null item_codes(in addition to outer join).Please read the whole chain to see what was required.
[Updated on: Thu, 06 August 2009 06:24] Report message to a moderator
|
|
|
|
Re: SQL NULL [message #417175 is a reply to message #417173] |
Thu, 06 August 2009 06:43   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
@Prajakta001 I am sure that you would be the first to admit that your English is not the best. This is NOT a criticism, simply an observation. What you will find is that often, problem descriptions that may seem very clear to you, turn out to be very unclear to those that you are asking for help. In this case a properly structured Test case along with expected results, brief description of the requirements along with what you have already attempted and what happened when you attempted it, will go a LONG way to helping those willing to assist you, to understand what it is that you are actually trying to do. Hence the common request for a test case.
What exactly is your problem with being asked not to be rude and not to shout? I think that that is a perfectly valid request. I'm sure that you wouldn't want to be rude to those that you are asking for help from.
You have to remember that the people on this site, generally, have real jobs with all of the problems that that entails. They owe you nothing, but are willing to help if you are willing to put in some effort. Being reprimanded by someone who has only just started to post would tend to turn most people away from helping.
|
|
|
Re: SQL NULL [message #417177 is a reply to message #417173] |
Thu, 06 August 2009 06:48   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
It is great if people come here and help others, the more the merrier. However, if people post code here to help others, it should at least be something useful.
Using nvl in an outer-join join condition on both sides simply does not make sense.
Anyway, my post was directed more to the original poster, who ignored a lot of useful hints by a number of people and went for a code snippet that he clearly does NOT understand. That was what I wanted to make clear to him.
|
|
|
Re: SQL NULL [message #417180 is a reply to message #417173] |
Thu, 06 August 2009 06:56   |
ayush_anand
Messages: 417 Registered: November 2008
|
Senior Member |
|
|
@Prajakta001 Be patient as you can see by the number of my messages(dont go by my joining date ) I am also a new joinee to this forum and we are here to learn new things(real life scenarios) which we might not be working at or will face in coming future.
So if I commit some mistake I gladly accept I have given some wrong help (of course unintentionally)But that also makes me learn in a way.Yes sometimes they are rude but 100% better than all of my teachers
[Updated on: Thu, 06 August 2009 06:57] Report message to a moderator
|
|
|
|
|
|
|
Re: SQL NULL [message #417284 is a reply to message #417236] |
Thu, 06 August 2009 23:34   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
milikiel wrote on Thu, 06 August 2009 18:53 | i thought your much older for such puberty comment.
|
Please explain what puberty comments you are referring to.
Quote: | who doesnt code snip on this forum??
|
Nothing wrong with that, never said so. I just pointed out that these snippets should be working and useful. If they are not and people have comments about it, one should be able to take those comments.
I too have made errors in solutions I posted. If someone points me to it, I do not go into defense, I accept the fact that I posted a wrong piece of code.
|
|
|
|
Re: SQL NULL [message #417292 is a reply to message #417288] |
Fri, 07 August 2009 00:02  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
I think you are taking this way to personal.
And if you would read pablolees comment thoroughly, you would see that he is not commenting on your English, he is explaining that people that are not native speakers can have difficulty expressing themselves in such a way that it is clear to others what they are after. That is why sometimes testcases can clear things up.
He did not mean to criticize your English, heck he even SAID so.
As for me getting into offensive mode, sorry but I have to disagree there. I did my best to explain what I posted, and if anything, I went defensive and tried to cool things down a bit.
[Updated on: Fri, 07 August 2009 00:03] Report message to a moderator
|
|
|