Home » SQL & PL/SQL » SQL & PL/SQL » USING Clause etc.
| USING Clause etc. [message #144490] |
Wed, 26 October 2005 16:34  |
donlcs
Messages: 62
Registered: October 2005
Location: VA, USA
|
Member |
|
|
Hi,
<1> ANSI join works perfectly below.
SQL> create or replace view empView AS
2 select emp.*
3 from HR.employees emp INNER JOIN HR.departments dept
4 on emp.department_id = dept.department_id
5 where dept.department_id = 10;
View created.
SQL> select * from emp
2
SQL> select * from empView
2 /
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
200 Jennifer Whalen
JWHALEN 515.123.4444 17-SEP-87 AD_ASST 4400
101 10
<2> with USING Clause I got nasty error
SQL> create or replace VIEW empView2 AS
2 select emp.*
3 from HR.employees emp JOIN HR.departments dept USING (department_id)
4 where dept.department_id = 10;
where dept.department_id = 10
*
ERROR at line 4:
ORA-25154: column part of USING clause cannot have qualifier
SQL> create or replace VIEW empView2 AS
2 select employee_id, last_name, salary, department_id, department_name
3 from HR.employees emp JOIN HR.departments dept USING (department_id)
4 where dept.department_id = 10;
where dept.department_id = 10
*
ERROR at line 4:
ORA-25154: column part of USING clause cannot have qualifier
SQL> create or replace VIEW empView2 AS
2 select employee_id, last_name, salary, department_id, department_name
3 from EMPLOYEES JOIN DEPARTMENTS USING (department_id)
4 where DEPARTMENTS.department_id = 10;
from EMPLOYEES JOIN DEPARTMENTS USING (department_id)
*
ERROR at line 3:
ORA-00942: table or view does not exist
<3> Based on the above, say, we're in PL/SQL, should I use USING Clause for this case or when?
<4> Question on Oracle convention, that is, :=
Example,
DECLARE
sql_stmt VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id NUMBER(4) := 7566;
...
emp_id NUMBER(4) := 7566;
Does the above
set local var, emp_id of data type, NUMBER and to default value of 7566?
NEVER MIND about this question. I figured it out. YES.
The following code seems to indicate so. And it brought up another question ...
<5> How to reference variable (I don't know if Oracle has the concept of local variable and global variable)?
According to the following script:
DECLARE
last_name VARCHAR2(10) := 'King';
my_last_name VARCHAR2(10) := 'King';
BEGIN
-- Deletes everyone, because both LAST_NAMEs refer to the column
DELETE FROM employees2 WHERE last_name = last_name;
dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows.');
ROLLBACK;
...
END;
Just Use the Variable Name to Reference it, however, the following script failed, why?
SQL> DECLARE
2 sql_stmt VARCHAR2(200);
3 plsql_block VARCHAR2(500);
4 emp_id NUMBER(4) := 7566;
5 salary NUMBER(7,2);
6 dept_id NUMBER(2) := 100;
7 dept_name VARCHAR2(14) := 'employee care';
8 location VARCHAR2(13) := 'md';
9 emp_rec SCOTT.EMP%ROWTYPE;
10 BEGIN
11 INSERT INTO SCOTT.dept VALUES (dept_id, dept_name, location);
12 SELECT * FROM scott.dept';
13 END;
14 /
ERROR:
ORA-01756: quoted string not properly terminated
However, referencing it in convoluted manner works, see below.
SQL> DECLARE
2 sql_stmt VARCHAR2(200);
3 plsql_block VARCHAR2(500);
4 emp_id NUMBER(4) := 7566;
5 salary NUMBER(7,2);
6 dept_id NUMBER(2) := 90;
7 dept_name VARCHAR2(14) := 'development';
8 location VARCHAR2(13) := 'wash dc';
9 emp_recNew SCOTT.EMP%ROWTYPE;
10 BEGIN
11 EXECUTE IMMEDIATE 'CREATE TABLE bonus2 (id NUMBER, amt NUMBER)';
12 sql_stmt := 'INSERT INTO SCOTT.dept VALUES (:1, :2, :3)';
13 EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
14 -- EXECUTE IMMEDIATE 'SELECT * FROM scott.dept';
15 END;
16 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 PERSONNEL DALLAS
90 development wash dc
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
6 rows selected.
THANK YOU VERY MUCH.
|
|
|
|
| Re: USING Clause etc. [message #144544 is a reply to message #144490] |
Thu, 27 October 2005 01:19  |
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
|
Senior Member |
|
|
hi,
you could use something like this.
select e.ename,e.job,e.sal,e.hiredate,d.dname from emp e inner join dept d
using(deptno)
and regarding the ORA-01756: quoted string not properly terminated.
SELECT * FROM scott.dept';--there's a single quote at the end which you need to remove and even then you will get an error
you need to select it into some variable.
regards,
|
|
|
|
|
|
| Re: USING Clause etc. [message #144634 is a reply to message #144544] |
Thu, 27 October 2005 08:49 |
donlcs
Messages: 62
Registered: October 2005
Location: VA, USA
|
Member |
|
|
To dhananjay,
Good catch on
SELECT * FROM scott.dept';--
So, it seems confirmed that with Oracle a variable can be referenced by simply using its exact name.
Example,
SQL> DECLARE
2 sql_stmt VARCHAR2(200);
3 plsql_block VARCHAR2(500);
4 emp_id NUMBER(4) := 7566;
5 salary NUMBER(7,2);
6 dept_id NUMBER(2) :=80;
7 dept_name VARCHAR2(14) := 'employee care';
8 location VARCHAR2(13) := 'md';
9 emp_rec SCOTT.EMP%ROWTYPE;
10 BEGIN
11 INSERT INTO SCOTT.dept VALUES (dept_id, dept_name, location);
12 END;
13 /
PL/SQL procedure successfully completed.
On
select e.ename,e.job,e.sal,e.hiredate,d.dname from emp e inner join dept d
using(deptno)
I'd prefer either using ANSI SQL or Oracle specific SQL rather than mashed the together in one statement.
Now, let me rephrase my question on USING. WHEN or in what case one must use USING Clause in Oralce, give an example?
TIA.
[Updated on: Thu, 27 October 2005 08:50] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Thu Jun 26 23:06:26 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
