October 28, 2008 at 10:29 am
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
October 29, 2008 at 6:02 am
Either your specs are wrong or your test data is wrong.
All your rows have -1 value either on field3 or field4 columns so your query would return no rows; by the way, your predicate condition should be AND instead of OR, like:
SQL>
SQL> create table test(
2 field1 varchar2(10),
3 field2 varchar2(10),
4 field3 number,
5 field4 number);
Table created.
SQL> insert into test values('test','test1',20000,-1);
1 row created.
SQL> insert into test values('test2','test4',-1,4000);
1 row created.
SQL> insert into test values('test4','test 45',-1,400300);
1 row created.
SQL> insert into test values('test5','test59',39929,-1);
1 row created.
SQL> create view view_test
2 as
3 select *
4 from test;
View created.
SQL> select * from view_test;
test test1 20000 -1
test2 test4 -1 4000
test4 test 45 -1 400300
test5 test59 39929 -1
SQL> select *
2 from view_test
3 where (field3 <> -1 and field4 <> -1);
no rows selected
SQL>
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy