Home » SQL & PL/SQL » SQL & PL/SQL » oracle view
oracle view [message #355938] Tue, 28 October 2008 11:34 Go to next message
keywestfl9
Messages: 1
Registered: October 2008
Junior Member
i am writing a view to pull data from a table

field1 field 2 field3 field4

Data for the above fields looks like

field1 field2 field3 field4
test test1 20000 -1
test2 test4 -1 4000
test4 test 45 -1 400300
test5 test59 39929 -1

i want to retrive only the rows where either field3 or field4 doesnt contain -1

i tried where (field3 <> -1 or field4 <> -1) and it didnt work

Pleaset let me know how to do it
Re: oracle view [message #355941 is a reply to message #355938] Tue, 28 October 2008 11:47 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@keywestfl9,

keywestfl9 wrote on Tue, 28 October 2008 22:04

Data for the above fields looks like

field1 field2 field3 field4
test test1 20000 -1
test2 test4 -1 4000
test4 test 45 -1 400300
test5 test59 39929 -1

i want to retrive only the rows where either field3 or field4 doesnt contain -1


Based on the data given and if I understand your requirement correctly, it should fetch the four records right?
So what is not working? What is the output you are getting?

Please read OraFAQ Forum Guide on "How to Format Post".

Regards,
Jo
Re: oracle view [message #355942 is a reply to message #355938] Tue, 28 October 2008 11:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does "it didnt work" mean?

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) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: oracle view [message #355944 is a reply to message #355938] Tue, 28 October 2008 11:53 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Please do not take this criticism the wrong way.

Your post is an excellent example of a crappy post. We hate dealing with this kind of thing. Let me explain. You say you did such and such and it did not work. Your example however shows none of "what you did". It is useless to getting you an answer.

If you really want someone to work on this you must do the following:

1) post a create table statement
2) post some inserts to populate it
3) post the sql you ran and the result that shows it "did not work"

Indeed once you do this you may see the problem yourself.

If you can't take the time to post enough real information for someone to review, what makes you think we are going to spend the time to try and get you an answer.

And just so no one can call me a jerk, this is what you should have posted along with your question:

SQL> create table test1
  2  (
  3   field1 varchar2(10)
  4  ,field2 varchar2(10)
  5  ,field3 number
  6  ,field4 number
  7  )
  8  /

Table created.

SQL> 
SQL> insert into test1 values ('test','test1',20000,-1);

1 row created.

SQL> insert into test1 values ('test2','test4',-1,4000);

1 row created.

SQL> insert into test1 values ('test4','test',-1,400300);

1 row created.

SQL> insert into test1 values ('test5','test59',39929,-1);

1 row created.

SQL> 
SQL> select *
  2  from test1
  3  where field3 <> -1 or field4 <> -1
  4  /

FIELD1     FIELD2         FIELD3     FIELD4
---------- ---------- ---------- ----------
test       test1           20000         -1
test2      test4              -1       4000
test4      test               -1     400300
test5      test59          39929         -1

4 rows selected.

SQL> 

To whit I can reply, IT DOES WORK. You got all the rows where either field3 was != -1 or field4 != -1 because every row in your table satisfies this criteria.

Good luck, Kevin
Re: oracle view [message #355966 is a reply to message #355938] Tue, 28 October 2008 14:34 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
If you want rows where neither condition exists, then:

where field3 <> -1 AND field4 <> -1

Previous Topic: SQL for counting items in a list, from cropped fields
Next Topic: sql query - taking long time
Goto Forum:
  


Current Time: Sun Dec 04 12:21:28 CST 2016

Total time taken to generate the page: 0.17319 seconds