Oracle SQL

hello,

here you can find the cause and action to correct your statement :

Cause: The SQL statement ends with an inappropriate clause. For example, an ORDER BY clause may have been included in a CREATE VIEW or INSERT statement. ORDER BY cannot be used to create an ordered view or to insert in a certain order.

Action: Correct the syntax by removing the inappropriate clauses. It may be possible to duplicate the removed clause with another SQL statement. For example, to order the rows of a view, do so when querying the view and not when creating it. This error can also occur in SQL*Forms applications if a continuation line is indented. Check for indented lines and delete these spaces.

you can use :

[code]
UPDATE employees e
SET e.last_name = ‘test’
WHERE e.department_id = (SELECT d.department_id
FROM departments d
WHERE d.department_id = e.department_id);
[/code]

one example to create a function to return ‘Y’ if parameter value is number otherwise which is exception in this case to return (‘N’)

ex:

[code]CREATE OR REPLACE FUNCTION is_number (p_string IN VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC
PARALLEL_ENABLE
IS
l_num NUMBER;
BEGIN
l_num := TO_NUMBER (p_string);
RETURN ‘Y’;
EXCEPTION
WHEN VALUE_ERROR
THEN
RETURN ‘N’;
END is_number;[/code]

and here you can call the function created above to identify the value passed is number or not like :

[code]SELECT (CASE
WHEN (is_number (mycolumn) = ‘Y’)
THEN
‘your column value is number’
ELSE
‘your column value is not number’
END)
FROM myTable;[/code]

hope this help you.

You can find the following view to access all metadata about the columns :

user_tab_cols; — For all tables owned by the user

all_tab_cols ; — For all tables accessible to the user

dba_tab_cols; — For all tables in the Database.

and lets consider you want to add new column only if doesn’t exists you can use this pl/sql to check and add new column

[code]DECLARE
v_column_exists number := 0;
BEGIN
Select count(*)
into v_column_exists
from user_tab_cols
where column_name = ‘ADD_COLUMN’
and table_name = ‘departments’;

if (v_column_exists = 0) then
execute immediate ‘alter table departments add (ADD_COLUMN NUMBER)’;
end if;
end;[/code]

hope this help.