join

[jaanutn]

[code=oracle]

SELECT emptable.empnam e, emptable.deptid , depttable.deptn ame from emptable inner join depttable
ON emptable.deptid = depttable.depti d

ERROR at line 1:
ORA-00933: SQL command not properly ended

[/code]


i m using oracle8.. while trying to use join & its types i am getting this type of errors. plz any one tell me the answer

[amitpatel66]

SELECT emptable.empnam e, emptable.deptid , depttable.deptn ame from emptable inner join depttable
ON emptable.deptid = depttable.depti d

ERROR at line 1:
ORA-00933: SQL command not properly ended


i m using oracle8.. while trying to use join & its types i am getting this type of errors. plz any one tell me the answer

Try this:

[code=oracle]

SELECT et.empname, et.deptid, dt.deptname from emptable et, depttable dt
WHERE et.deptid = dt.deptid

[/code]

[jaanutn]

Try this:

[code=oracle]

SELECT et.empname, et.deptid, dt.deptname from emptable et, depttable dt
WHERE et.deptid = dt.deptid

[/code]

wow ......... itz working. thank you very much..

actually what was the wrong in it? just waned to know

[amitpatel66]

wow ......... itz working. thank you very much..

actually what was the wrong in it? just waned to know

Instead of using INNER JOIN ketywords, I had given alias names to the tables and used the alias names to select the columns and in the WHERE clause.

[jaanutn]

Instead of using INNER JOIN ketywords, I had given alias names to the tables and used the alias names to select the columns and in the WHERE clause.

SQL> select emptable.empname, emptable.deptid, depttable.deptname from emptable, depttable 
2 where emptable.deptid = depttable.deptid 
3 /
 
EMPNAME DEPTID DEPTNAME
------------------------- --------- -------------------------
rafferty 31 sales
jones 33 engineering
steinberg 33 engineering
smith 34 clerical
robinson 34 clerical

if i gave like this it is working... but if i use inner join word or left outer join word , it is not working.. wht should i give 4 inner join?

[amitpatel66]

Try this

[code=oracle]

select emptable.empnam e, emptable.deptid , depttable.deptn ame from emptable JOIN depttable ON emptable.deptid = depttable.depti d

[/code]

[jaanutn]

Try this

[code=oracle]

select emptable.empnam e, emptable.deptid , depttable.deptn ame from emptable JOIN depttable ON emptable.deptid = depttable.depti d

[/code]

SQL> SELECT emptable.empname, emptable.deptid, depttable.deptname FROM emptable JOIN depttable ON em
ptable.deptid = depttable.deptid
2 ;
SELECT emptable.empname, emptable.deptid, depttable.deptname FROM emptable JOIN depttable ON emptabl
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

i tried this too.. bt got error

[amitpatel66]

It is suported from Oracle 9i version and not in 8i

Check https://www.dba-oracle.com/oracle_ne...19_rittman.htm

[jaanutn]

It is suported from Oracle 9i version and not in 8i

Check https://www.dba-oracle.com/oracle_ne...19_rittman.htm

[quote]

thank u for the info..

how to delete empty records & similar data records(does not have primary key) in the table?

[amitpatel66]

[QUOTE=jaanutn]

thank u for the info..

how to delete empty records & similar data records(does not have primary key) in the table?

post what you have tried for?

There should be some column that can be used to find out the duplicates and delete them.

[jaanutn]

[QUOTE=amitpatel 66]

post what you have tried for?

There should be some column that can be used to find out the duplicates and delete them.

[code]


SQL> select * from borrower;

NAME LOANNO
-------------------- ---------
meena 90
jane 43
meena 90
jeen 89

SQL> delete * from borrower where name = 'meena';
delete * from borrower where name = 'meena'
*
ERROR at line 1:
ORA-00903: invalid table name

[code]

[quote]

how to delete the record named meena?

[quote]

[amitpatel66]

[QUOTE=jaanutn][QUOTE=amitpatel 66]


[code]


SQL> select * from borrower;

NAME LOANNO
-------------------- ---------
meena 90
jane 43
meena 90
jeen 89

SQL> delete * from borrower where name = 'meena';
delete * from borrower where name = 'meena'
*
ERROR at line 1:
ORA-00903: invalid table name

[code]

[quote]

how to delete the record named meena?

just say delete from and not delete * from.

You dont use * or any column name when you try to delete from a table.

[jaanutn]

thank u for the replies..

in the above table, it contains 4 records ( one empty record).. how to delete that?

[jaanutn]

SQL> select * from borrower;

NAME LOANNO
-------------------- ---------
jane 43
jeen 89
0


SQL> select count(name) from borrower;

COUNT(NAME)
-----------
4

SQL> delete from borrower where name = null;

0 rows deleted.

SQL> select count(4) from borrower;

COUNT(4)
---------
4


thank you for the replies

in the above table, it contains 4 records(one empty record)
how to delete that?

[amitpatel66]

delete from borrower where name IS null;