Scenario From Oracle Forum I have a transaction table consists of duplicate records I success to write a sql which get the duplicate records but unable to write the delete statement to delete this record. The sql query is as follows: Select invoice_no, slip_no, drug_code from transaction_detail having count(*)>1 group by invoice_no, slip_no, drug_code If [...]

[Read more →]

Scenario From Oracle Forum This function seems like a good idea for password validation, for example one number and 4-8 characters. However, this does not work – any ideas? select 1 from dual where regexp_like(upper(‘PA55WORD’),’^(?=.*\d).{4,8}$’) Solution which I gave: WITH data_Set AS ( SELECT 1 row_no, ’3434HERE’ text_col FROM dual UNION ALL SELECT 2 row_no, [...]

[Read more →]

Scenario From Oracle Forum I have to select data from table which has the words both ‘help’ and ‘window’ eg: window.open(‘help’) this type of data should be selected. Both the words are mandatory. Eg:window.open(‘hai’) should not be selected. This is not working select * from employee_comment WHERE regexp_like(text,’window*help’) ; Solution which I gave: WITH data_Set [...]

[Read more →]

Hi Guys, Today I came across one little bit funny piece of post at one of the thread in Oracle Forum. Member No 1 asked following doubts: Hi all, I have the table List flag_circ char(1) impo number(11,3) and I have to update flag_circ, if the number of decimal of the field impo, are 0 [...]

[Read more →]

Today I went through few post by Ask Tom, and stuck with this fantastic post Lets first create following tables to show the concept SQL> CREATE TABLE table_1 ( c_code NUMBER PRIMARY KEY, c_name VARCHAR2(40) ); Table created. SQL> CREATE TABLE table_2 ( c_code NUMMBER PRIMARY KEY, c_name VARCHAR2(40) ); Table created. Note: We need [...]

[Read more →]

Hi Guys! Today I came across very simple and very interesting fact! First Assume as follows: Table Structure ——————————- SQL> desc emp; Name Null? Type —————————————– ——– ————- EMP_CODE NUMBER EMP_NAME VARCHAR2(100) SQL> desc emp_dept; Name Null? Type —————————————– ——– ————- EMP_CODE NUMBER DEPT_CODE NUMBER FROM_DATE DATE TO_DATE DATE Table Data ——————————- SQL> select * [...]

[Read more →]

Today I came across another interesting solution. Situation was as follows: There is PROJECTS table, with columns as follows (datatype never mind): PERSON ID_STATE We need to select persons from this table having all the ID_STATE (2, 3 and 4) I found solution given by Aketi Jyuuzou very interesting and simple! SELECT person FROM projects [...]

[Read more →]

Today I came across one interesting question at Oracle Forum Dear experts! Please help me with this kind of query! Suppose that I have a table like the following to keep track of moving path of an item location datetime l1 2006/10/01 l1 2006/10/20 l1 2006/11/01 l2 2006/11/03 l2 2006/11/19 l1 2006/11/28 l1 2006/12/10 How [...]

[Read more →]

First have a look at following query: SELECT e_name, age, address FROM employees WHERE UPPER(e_name) = ‘RAJENDER SINGH’; Now, One of the important instruction that I normally give to my team that when ever they are writing the SQL query, never ever use function in a WHERE CLAUSE of a SQL statement ( as above). [...]

[Read more →]

One of the old way in which I normally do this by using GROUP BY and HAVING keywords. Example: DELETE FROM EMP WHERE ROWID NOT IN (SELECT RWID FROM ( SELECT COUNT(1), MAX(ROWID) RWID FROM EMP GROUP BY empno HAVING COUNT(1) > 1 )) AND empno IN (SELECT empno FROM ( SELECT empno, COUNT(1) FROM [...]

[Read more →]