simply put, EXISTS is usually used for checking whether rows that meet a criteria exist in another (or the same) table. your SQL using EXISTS would look like this: select * from emp e where exists(select * from emp e2 where e.empno = e2.empno and e2.ename in (‘smith’, ‘brown’, ‘john’, ‘johnson’)) so you can see … Read more
You can use analytic functions SELECT * FROM (SELECT c.*, rank() over (partition by user_id order by ts desc) rnk FROM comments c) WHERE rnk = 1 Depending on how you want to handle ties (if there can be two rows with the same user_id and ts), you may want to use the row_number or … Read more
Maybe I don’t understand the question, but wouldn’t this do it? (you must know what you want back) INSERT INTO some_table (…) VALUES (…) RETURNING some_column_a, some_column_b, some_column_c, … INTO :out_a, :out_b, :out_c, … @Vincent returning bulk collect into for multi-row insert works only in conjunction with forall (in another words if you insert from … Read more
you should do select COALESCE (EMAIL, n’NO EMAIL’) from crmuser.accounts to convert the literal to NVARCHAR. eg http://sqlfiddle.com/#!4/73929/1 vs http://sqlfiddle.com/#!4/73929/2
The error you are getting is either because you are doing TO_DATE on a column that’s already a date, and you’re using a format mask that is different to your nls_date_format parameter[1] or because the event_occurrence column contains data that isn’t a number. You need to a) correct your query so that it’s not using … Read more
check your casing, the name is typically stored in upper case SELECT * FROM all_source WHERE name=”DAILY_UPDATE” ORDER BY TYPE, LINE;
I do this sort stuff a fair bit (usually generating insert/update statements). You just need to use the replace function to turn all the ‘ into ”. i.e. Change it to: str_comment:=’COMMENT ON COLUMN ‘||rec.table_name||’.’||rec.column_name ||’ IS ”’||REPLACE( rec.description,””,”””)||”’; ‘ ;
This is the syntax to insert into a table from a CTE: — CREATE TABLE tmp ( tmp_id NUMBER(10) ); INSERT INTO tmp( tmp_id ) WITH cte AS ( SELECT 1 AS tmp_id FROM dual ) SELECT tmp_id FROM cte;
The reason you can’t use SUM() in the WHERE clause is the order of evaluation of clauses. FROM tells you where to read rows from. Right as rows are read from disk to memory, they are checked for the WHERE conditions. (Actually in many cases rows that fail the WHERE clause will not even be … Read more
I think this will do the trick: SELECT REGEXP_REPLACE(COLUMN, ‘[^[:print:]]’, ”)