Home » SQL & PL/SQL » SQL & PL/SQL » SQL NULL (SQL PLUS,2003 SERVER)
SQL NULL [message #416948] Wed, 05 August 2009 06:05 Go to next message
kilimanjaro
Messages: 134
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 #416949 is a reply to message #416948] Wed, 05 August 2009 06:22 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
please read about OUTER JOIN
Re: SQL NULL [message #416950 is a reply to message #416948] Wed, 05 August 2009 06:24 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
kilimanjaro
Messages: 134
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 #417123 is a reply to message #416948] Thu, 06 August 2009 04:17 Go to previous messageGo to next message
kilimanjaro
Messages: 134
Registered: May 2009
Location: Tanzania
Senior Member
Results

STOCK_CODE P
---------- --- - ------
305 8
2162
1202 195


Re: SQL NULL [message #417124 is a reply to message #417122] Thu, 06 August 2009 04:18 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
for your present query anything minus NULL = NULL
You can use NVL function to eliminate NULLS with 0
Please provide test cases and use CODE tags

[Updated on: Thu, 06 August 2009 04:19]

Report message to a moderator

Re: SQL NULL [message #417125 is a reply to message #416948] Thu, 06 August 2009 04:25 Go to previous messageGo to next message
kilimanjaro
Messages: 134
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 Go to previous messageGo to next message
pablolee
Messages: 2836
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


Quote:
examples please.

RTFM
Re: SQL NULL [message #417127 is a reply to message #417125] Thu, 06 August 2009 04:29 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #417128 is a reply to message #417125] Thu, 06 August 2009 04:30 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
hw do i use code tags in regard to this problem???


its for formatting your input

Post a working Test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Now I know why all the big participants of forum type this Smile
Re: SQL NULL [message #417130 is a reply to message #417125] Thu, 06 August 2009 04:31 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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 #417147 is a reply to message #417123] Thu, 06 August 2009 05:05 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

If u want to know @ NVL and if your query is working otherwise
NVL(colname, <REPLACE VALUE IN CASE OF NULL >)
REPLACE VALUE DATATYPE should match with the column datatype



SELECT   a.stock_code, 
         a.quantity - Sum(b.quantity) p 
FROM     en_daily_purchases a, 
         en_outdated_items2 b 
WHERE    Nvl(a.stock_code,'-99999999') = Nvl(b.item_code (+),'-99999999') 
GROUP BY a.quantity, 
         a.stock_code 



Regards
Prajakta
Re: SQL NULL [message #417150 is a reply to message #416948] Thu, 06 August 2009 05:10 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I imagine it's the quantity that needs the nvl not the stock_code.
Re: SQL NULL [message #417154 is a reply to message #416948] Thu, 06 August 2009 05:30 Go to previous messageGo to next message
kilimanjaro
Messages: 134
Registered: May 2009
Location: Tanzania
Senior Member
Thankyou Prajakta001

IT HAS WORKED. offcourse i tried it sometime ago,but i was putting nvl and sum together and not in brackets.

This is the appropriate use of this forum,and not a place of arrogance or quick unhelpfull replies and inappropriate Mad comments!!!

THANKYOU ALL

Re: SQL NULL [message #417156 is a reply to message #417154] Thu, 06 August 2009 05:33 Go to previous messageGo to next message
Frank
Messages: 7880
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 #417166 is a reply to message #417156] Thu, 06 August 2009 06:06 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Sorry Frank ...
Are u saying the answer is non-sensical ...
Re: SQL NULL [message #417169 is a reply to message #417166] Thu, 06 August 2009 06:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #417173 is a reply to message #417169] Thu, 06 August 2009 06:29 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Yeh answer was inteneded to tell only about how NVL works ..That is what he was asking in the forum..

if its again about the code / concepts / your so called non sensical comments ...
I have been visiting this forum and i find it really rude ...
where answers just dont come from seniors

When i am stuck somewhere and putting it in the forum ....
the least i expect is comements like "DONT SHOUT" /"DONT BE RUDE" , give us the test case when someone is asking i am not able to catch the exception from the cursor as no_data_found
and all so called seniors are asking for the test case for the same . wow ..Great going ... and wish u all luck ......

It sounded great when i could access this site from my office as i have very limited access ...
but wish could have got better ...


Re: SQL NULL [message #417175 is a reply to message #417173] Thu, 06 August 2009 06:43 Go to previous messageGo to next message
pablolee
Messages: 2836
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Smile ) 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 Smile

[Updated on: Thu, 06 August 2009 06:57]

Report message to a moderator

Re: SQL NULL [message #417236 is a reply to message #416948] Thu, 06 August 2009 11:53 Go to previous messageGo to next message
kilimanjaro
Messages: 134
Registered: May 2009
Location: Tanzania
Senior Member
hi frank,were is paragon city anyway??? Laughing

i thought your much older for such puberty comment.you have been here almost a decade....be for the forum expectation.
Embarassed
who doesnt code snip on this forum??
Surprised
but getting appropriate outcome is a challenge sometimes...
and we need friends like you.
Smile



Re: SQL NULL [message #417238 is a reply to message #416948] Thu, 06 August 2009 12:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
milikiel,
with free advice, you get what you paid for it.
Re: SQL NULL [message #417240 is a reply to message #417236] Thu, 06 August 2009 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i thought your much older for such puberty comment.

I'm pretty sure you are not with so manhy smileys in your post.

Regards
Michel
Re: SQL NULL [message #417243 is a reply to message #416948] Thu, 06 August 2009 12:48 Go to previous messageGo to next message
kilimanjaro
Messages: 134
Registered: May 2009
Location: Tanzania
Senior Member
ITs over OPs

Lets take it as fun.now back to business!!!
Re: SQL NULL [message #417284 is a reply to message #417236] Thu, 06 August 2009 23:34 Go to previous messageGo to next message
Frank
Messages: 7880
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 #417288 is a reply to message #417284] Thu, 06 August 2009 23:46 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Hi Frank ,

Pls keep me out of this ...
U know its not about the code and its about comments ........
I was just trying something may be it was wrong but i found your reply very rude thats what the comments were all about ..And if u have seen another posts from me ..u will see that i have accepted if its wrong on my part...

I am just talking about people being rude that supresses the enthusiasun of people who are trying ...

some one is commenting on my English .. U find it perfectly appropriate where as when i say the comment are rude,, U are getting into Offensive mode not me .

chao .. lets stop this chain here itself ... its not helping anyone ....
and moreover its no more about ORACLE i guess ....


Re: SQL NULL [message #417292 is a reply to message #417288] Fri, 07 August 2009 00:02 Go to previous message
Frank
Messages: 7880
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

Previous Topic: before update trigger
Next Topic: tuning of sql statement(top 25 emp highst earing)
Goto Forum:
  


Current Time: Sat Dec 10 03:01:30 CST 2016

Total time taken to generate the page: 0.33286 seconds