select order_number, line_number, isnull(max(case when linenum=1 then name else null end),”) name1 , isnull(max(case when linenum=1 then value else null end),”) value1, isnull(max(case when linenum=2 then name else null end),”) name2, isnull(max(case when linenum=2 then value else null end),”) value2, isnull(max(case when linenum=3 then name else null end),”) name3, isnull(max(case when linenum=3 then value else … Read more
well you have 4 different ways to merge a table. First of all, you can create a query, which gives you the result as if it was a merge: a simple join: tsql or you may want to create a view, which is a new table which values are update in real time by a … Read more
Since you are using Oracle you can use CHECK constraint saying CONSTRAINT check_dates CHECK (my_date_column BETWEEN date ‘1994-10-25’ AND date ‘2016-05-10’) Your query (as in comment) should be like below create table dob5 ( birthdate date not null, CONSTRAINT check_dates CHECK (birthdate BETWEEN date ‘1994-10-25’ AND date ‘2016-05-10’) ); See this demo fiddle http://sqlfiddle.com/#!4/779f9
First of all (and as usual), you should not store DATE values as VARCHAR, even if you need only the quarter. You can do following. First convert the string into a DATE, e.g. SELECT TO_DATE(REGEXP_SUBSTR(‘Q2 FY07’, ‘\d{2}$’)||’-‘||REGEXP_SUBSTR(‘Q2 FY07′,’\d’)*3, ‘RR-MM’) FROM … Then you can change the output format according to your needs, i.e. TO_CHAR( TO_DATE(REGEXP_SUBSTR(‘Q2 … Read more
Always be specific when you have dates formatted as text: do the conversion explicitly with the correct date/time format for your data. to_date(’14/04/2015′, ‘DD/MM/YYYY’) Do that for all date inputs.
here lopping through each row and get non-null Comments,Assignee,Case_Info,Users values and return one row Isn’t that simply finding NOT NULL values in plain SQL – SELECT comments, assignee, case_info, users FROM sometable WHERE id = :id AND comments IS NOT NULL AND assignee IS NOT NULL AND case_info IS NOT NULL AND users IS NOT … Read more
As Ranzo notes several of the other answers are are also plausible. So, why is E the correct answer? Because the question asks for the most appropriate location for the code. Let’s break it down. The logic needs to be called from several places. It is therefore necessary to define it as its own function … Read more
You don’t need to nest. You need to join. You’ll probably need something like this, although I would need the exact table structure to be sure. But you’ll get the general idea. select b.TITLE, a.LASTNAME, i.UNITSONHAND from BOOK b inner join AUTHOR a on a.AUTHORID = b.AUTHORID inner join INVENTORY i on i.BOOKID = b.BOOKID … Read more
This one: Update item_stock A Set A.itemcode = item_stock.itemcode; Note: The data type of the item_stock.itemcode must be same as A.itemcode.
This code should work: select * from table where dbms_lob.compare(aClobColumn, bClobColumn) = 0 PS: I haven’t tried running it as I am not having access to an Oracle instance at the moment. Reference: https://docs.oracle.com/database/121/ARPLS/d_lob.htm#ARPLS600