Trying to understand how Oracle DATE works and how it differs to TO_DATE in the below manner...
SELECT DATE '2012-10-25' FROM DUAL;
SELECT TO_DATE('2012-10-25','YYYY-MM-DD') FROM DUAL;
I've had a look through the Oracle Docs but can't find much on this. Basically I want to know how safe the first option is and if it does the same as the second. Also, where does DATE get the Format String from (NLS setting?). Can it be employed to compare a literal with a date that has a TIME portion set to something other than 12:00:00?
I would like to use character functions (LPAD, INSTR, SUBSTR, etc) to accomplish what i feel should be rather easy. I would like to take the following character strings:
SQL> ed Wrote file afiedt.buf 1 select * 2 from (select deptno, ename, sal 3 ,dense_rank() over (partition by deptno order by sal desc) rank 4 from emp)
[code]...
why is that i just added ename on the ORDER BY part of the DENSE_RANK and then SQL> ed Wrote file afiedt.buf 1 select * 2 from (select deptno, ename, sal 3 ,dense_rank() over (partition by deptno order by sal desc, ename) ran
[code]...
ADAMS and WARD we're removed from the result, why is it? did it rank it as UNIQUE per sal and ename?
Again i getting confused with conversion function especially Explicit data type conversions. some cases oracle server automatically converts the data to the required type. This is called IMPLICIT CONVERSION. Explicit conversions are done by using the conversion functions.
How can I rewrite this without the analytic functions?
SELECT employee_ID, first_name, salary, RANK() OVER(ORDER BY salary desc) toprank_desc, RANK() OVER(ORDER BY salary ASC) toprank_asc FROM employees ORDER BY first_name
Is there any way to apply the restriction on analytical functions, just like WHERE and HAVING .AS we know that we can apply the restriction on table by using WHERE and grouping functions by using HAVING clause .
For Ex: Departments wise count including all employees record :
SQL> select count(*) over(partition by deptno) dept_Count, ce.* 2 from scott.emp ce 3 order by deptno, job;
DEPT_COUNT EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ----- ---------- --------- ----- ----------- --------- --------- ------ 3 7934 MILLER CLERK 7782 1/23/1982 1300.00 10 3 7782 CLARK MANAGER 7839 6/9/1981 2450.00 10 3 7839 KING PRESIDENT 11/17/1981 5000.00 10 5 7788 SCOTT ANALYST 7566 4/19/1987 3000.00 20 [code]....
I have created a package with some functions in it.I want to create a view with from 2 tables but also i wanto to use the functions from some fields of the view. (e.g. if the wview has 10 fields the 5 of them will be from the 2 tables and the other from package functions).
I use some quantity of functions with list of account id like in example below. Some functions use the same account id list, some use another. Everything works fine except those days when changes come and lists should be updated. Then I should edit each function... I think about creating new table for reference list like
CREATE TABLE MYREFERENCELIST AS SELECT XXXX AS ACCOUNTID, YYYY AS LIST1 FROM DUAL UNION ALL SELECT ZZZZ AS ACCOUNTID, UUUU AS LIST2 FROM DUAL
FUNCTION ACCOUNTID (arc_date date, cid number ) RETURN NUMBER AS [code]..........
Create and invoke the GET_JOB function to return a job title. a.Create and compile a function called GET_JOB to return a job title. b.Create a VARCHAR2 host variable called b_title, allowing a length of 35 characters. Invoke the function with job ID SA_REP to return the value in the host variable, and then print the host variable to view the result.
This is my FUNCTION: CREATE OR REPLACE FUNCTION GET_JOB( p_jobid IN jobs.job_id%TYPE) RETURN VARCHAR2
[code]...
This is how I invoked the FUNCTION but WHILE DECLARING THE BIND VARIABLE IT IS GIVING ME AN ERROR!!!!!
VARIABLE b_title VARCHAR2(35) set serveroutput on DECLARE P_JOBID VARCHAR2(10); v_jobtitle VARCHAR2(200);
I have created the following function in oracle database 10g then I used it in Oracle reports 6 . It is working fine for single row(one value). But when there is multiple rows then it show error .
Note:ot.po_code is number datatype and clr_name is varchar2 data type.
this is the script
create or replace FUNCTION get_colour_name (POC number) RETURN VARCHAR2 IS CLR_NAME VARCHAR2(100); BEGIN SELECT DISTINCT BC.COLOUR_NAME INTO CLR_NAME
Is there any function like fn:local-name(.) in oracle XPATH functions. I am new to Oracle & migrating procedure from SQL server to oracle where procedure taken XML script as input.
I need to convert below SQL server select statement to Oracle.
SELECT converseUID, 'Distributed', cast(ServiceCalls.query('fn:local-name(.)') as varchar(100)), 1, WIP.ServiceCalls.value('(TotalTime)[1]','int'), WIP.ServiceCalls.value('(Status)[1]','varchar(100)'), [code].......
Now i am trying to get the max(Value_2011) keep (dense_rank Last order by Month_ID) but i get a NULL. I can understand its because the Month_ID accomodates all years but i only need it to look at Month_ID for 2011 and return me the last dense_rank value, how can i achieve this?
I tried a couple of different methods like Last_Value() but i have group by in my original statement and i think analytical functions dont like GROUP by if they are not part of it. How can i achieve this?
I have a table (events) with this structure: customer_id, event_id, ... For each customer_id there can be several rows in the table. I need to run a query of the format: select customer_id, expensive_function(customer_id),... from events.
The expensive_function to be applied to customer_id in the query is really expensive (a Java class calculating a check sum) and the events table has billions of rows.
Rows in events table have same customer_id for a few rows, then continue with a different customer_id ang again coming back to the first, etc.
I was thinking that it should be a way to trigger calculation of expensive_function only when customer_id changes, in order to reduce the number of calls. Only my knowledge about SQL is not going that far and I cannot use PL/SQL or any other procedural language, need to stick to standard SQL (or Oracle version of it).
My applications is integrate with Excel 2003 by Ole2 package. Some of them excel books are using functions activates with Excel Complements (activate with men options: Tools-Complements-Analyisis Complements and Analysis complements VBA).
The problem is when I invoke this excel book in Oracle Forms, this functions don't work.
I am building a reporting table using the count analytic function in order to count up several different attributes in one statement.What I find is that this method quickly eats up my TEMP space. This is 10gR2. I have attempted to use MANUAL workarea policy with as large ofsort_area_size as possible (2G) but that does not seem to have any effect on performance or TEMP usage. The RAW table is about 12G with 75 million rows. I am not that concerned about execution time, but rather TEMP usage.
--INSERT into <object>... select distinct file_sid,filename,control_numb,processing_date,file_class, vendor_id,vendor_desc, c_status_id,c_status_desc,
[code]...
I am not seeing any increase in onepass or multipass executions on the PGA during execution of this statement using...
SELECT CASE WHEN low_optimal_size < 1024*1024 THEN to_char(low_optimal_size/1024,'999999') || 'kb <= PGA < ' || (high_optimal_size+1)/1024|| 'kb' ELSE to_char(low_optimal_size/1024/1024,'999999') ||
[code]...
I'd like to get a better explaination of how analytics use the instance resources and TEMP space. For example if I add a count with a different window (such as the last two columns commented in the above query) I blow out my temp space (70G). Is the critcal factor the use of distinct? or multiple windows? or something else?
When one enters the form, he can look up records either on Companyname or on Projectname. Therefor I have provided 2 buttons who pop-up a LOV. After eitherone is selected, the query has to be executed. There is a Master-Detail relationship between Company and Project.
My plsql for the company button:
declare v_show_lov boolean ; begin enter_query; v_show_lov := show_lov('LOVFIRMA'); if not v_show_lov then
[code]....
Plsql for the project button:
declare v_show_lov boolean; v_get_value number; begin v_show_lov := show_lov('LOVPROJECT'); if not v_show_lov then
[code]....
The first button only works when I go manually in Querymode first (by pressing F11). So I reckon my enter_query doesn't work? The property 'Fire in Enter-Query Mode' is Yes.
When I press it in non-query mode. It just fills in the LOV-values and the CCODE from company. It doesn't execute the query (probably because there is no enter_query).
When I enter query mode, the focus changes automatically to Company. And the LOV doesn't appear.
I have tried placing the enter_query on different places, just as the go_block and clear_block things, but there is always something wrong.
