I am new to SQL and I am just wondering if there is a solution to a problem I am having.I am using the piece of code below.Essentially what I am doing is selecting a field from a table and ordering that field in descending order. Using the Row_Number feature I then specify which Row I want to return.
Every day the row I will want is the Count of field1 for that day divided by 100 minus 1. This returns a single value of field1 and a single value of R.
I perform this operation every day. The only fields I change every day are the dates and the value of R. I use a seperate piece of SQL code to calculate R each day.
My problem is I have to often populate historical tables with this data. I can only run the code once for each day and for each value of R. Is there anyway I can alter this code such that it can return multiple values of field1 over several dates?The only way I can think of is to repeat the code multiple times using UNION but I am hoping there is a more efficient way.
SELECT *
FROM (SELECT Field1,
ROW_NUMBER() OVER (ORDER BY field1 desc ) R
FROM table
WHERE date >= TO_DATE ('20110215', 'YYYYMMDD')
AND date < TO_DATE ('20110216', 'YYYYMMDD')
)
WHERE R = 1227
--Note: 1227 = (count(field1)/100)-1
is there a way to return the next 7 dates just using a query... for example, I need a query that returns:
select (I don't know that put here) from dual
Date 2012-10-05 2012-10-06 2012-10-07 2012-10-08 2012-10-09 2012-10-10 2012-10-11
If possible, I would like to know if there's a way to pass a date and based on it, the query returns the next 7 dates based on the passed date... for example:
select (I don't know that put here) from dual where date > '2012-10-15'
Date 2012-10-16 2012-10-17 2012-10-18 2012-10-19 2012-10-20 2012-10-21 2012-10-22
select order_number, (select decode(hcp.contact_point_purpose,'ABC',hcp.email_address,'CDE',hcp.email_address,null) from hz_contact_points, hz_parties hz WHERE hz.party_id=hcp.owner_table_id) Email FROM oe_order_headers_all h WHERE h.order_number='102' .................... ..............
Actually the problem i am facing is the inner select query is returning multiple row , so my main query is erroring out, i need to capture the multiple row.
In the above example the inner decode statement returning two mail address, I need to capture that, but while executing the whole query it is erroring out as saying single query returns multiple values. capture multiple values
then these values are present as substring in the particular column in the source view. So I need to flag those records. For every record, I need to check whether all the values present in the reference table matches or not. If it matches then it should be flagged.
I can use in operator as we are not checking for the exact match and we are checking whether that value is present anywhere in that column record.
Looping results in performance issue. We can use PL/SQL for this. As the source view is put into a ETL internal file.
I have a table second_table which has a username and code field. A username (not the primary key) may be entered multiple times with different codes, or a single code 'ALL', in which case, the codes have to be fetched from 'third_table'. I am unable to form a 'case' clause to handle the fact that a list has to returned when the 'code' field from the second_table returns 'ALL'.
e.g.
Second_table username code A ALL B 23 B 56 B 33
Third_Table code 67 78
So, when the user asks the codes for user A, he should get 67 and 78 and when he asks for the user B, he should get 23,56 and 33
create table emp1 (empno number,deptname varchar2(30),deptno number,sal number); insert into emp1 values (1,'Bank',10,1000); insert into emp1 values (1,'Finance',20,400); insert into emp1 values (2,'Finance',20,4000); insert into emp1 values (3,'Account',30,3000); commit; select * from emp1;
actual output :
empno deptname deptno sal 1 Bank 10 1000 1 Finane 20 400 2 Finane 20 4000 3 Account 30 3000
Expected output :
empno deptname deptno sal 1 Bank 10 1000 2 Finane 20 4000 3 Account 30 3000
I am looking a output like above one. if any empno belongs to deptname Bank then give priority to that values else go to other dept like Finance but empno 1 should return only one row.i.e Bank dept only.
I have to return only one row based on dept values for one empno. how to do in sql or plsql?
Select OTMP_TCIS_RS.Get_UserInfo('EN') from dual; but i get the following error: ORA-14551: cannot perform a DML operation inside a query.
The intention of the code is to perform an insert into my table based on passing in values via an object into Stored Procedure Apply_Users_Update
Package Definition create or replace PACKAGE OTMP_TCIS_RS AS --1 PROCEDURE Get_UserInfo PROCEDURE Get_UserInfo( o_OutCode OUT INT, i_language IN VARCHAR2); FUNCTION Get_UserInfo( i_language IN VARCHAR2) RETURN NUMBER; [code]....
The data in one of the temporary table has been compiled as below. Number of Days elapsed between two transaction dates is required to be computed and multiplied with the balance. There can be multiple number of same transaction dates. When previous date and the current transaction dates are same the resulting number of day difference should be 0. But when they are different the difference between them is to be computed. On the last day of the Financial Year i.e. '31/03' of any year the difference day should be shown as 1 so as to make 365 or 366 days in a year. Simply deducting d1 from d2 on 31st will not be suffice as the difference is one day less.
Am calling the Function Batch to insert an update statemtnt into Batch_statement table in the DOWNLOAD_FUNC .But its failing with the error
SQL Error : ORA-14551: cannot perform a DML operation inside a query
Below Is the
FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS BEGIN INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID) VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1); RETURN 1;
I'm looking for a way to make CRLFs show in a CLOB.I'm feeding the insert statement with a concatinated string like this:
insert into table(Data) values (MyConc)
(MyConc is a string put togheter by another application)
Because of this I can't use the "|| chr(13) || chr(10) ||" because I only have that one concatinated string.Is there a character I can set in my string that automatically translates to a CRLF?
generates: ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 756 ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 245
Unfortunately the Body is not accessible to see.The spec of the function is:
FUNCTION MAIN (mvar IN varchar2) RETURN varchar2; I read somewhere that I can call it like:
var myVar VARCHAR2; call PACK.MAIN('blah') into :myVar
But this generates: ORA-01008: not all variables bound
I used select distinct(work_order) to come up with the three different possible scenarios the problem is that i need all this information on a single row
this is a conversion for distance. when i get this to work properly, it will generate reports on thousands of work orders with their converted distance markers.
I have the below query which is doing FTS and is very expensive causing load to timeout.
I did my analysis and found that table is having large number of records and hence FTS is taking long time causing timeout from app side.
I proposed to have this table partitioned but this is still pending with business and they in meantime want some solution other solution to fix this issue.
I've stucked with a query. I have a table that i store the IDs of logically equal records.
For example; A = B B = C X = Y Z = Y
My query must return all equivalent records. If you call the query with parameter 'A', the result set must contain B and C. And if you call the query with parameter 'Y', the result set will contain X AND Z. I have thought that i can write the query wity using start with connect by statement. But the query does not work as i expected. Here is my code and sample data:
create table temptable (ID1 number,ID2 number);/
insert into temptable values(11,12);/ insert into temptable values(12,13);/ insert into temptable values(13,14);/ insert into temptable values(13,15);/
SELECT distinct ID1 from ( SELECT * FROM temptable START WITH ID1 = 13 OR ID2 = 13 CONNECT BY NOCYCLE ( (PRIOR ID1 = ID1) OR (PRIOR ID1 = ID2) OR (PRIOR ID2 = ID1) OR (PRIOR ID2 = ID2)) ) WHERE ID1 <> 13 union [code]....
When i call the query with parameter 13, i'm expecting to get 11,12,14,15. But it returns only 12,14 and 15.
I am Having below query which is having total 664 records and for WHERE Clause (accountno ='13987135') it is having 3 records but when i am taking count it is returning 3 at first time and again returning 4 every time from then onwords.
MISSING_DATES EMPNO ---------------------- ---------- 09-SEP-12 TO 11-SEP-12 7499 23-SEP-12 TO 26-SEP-12 7499 01-sep-12 TO 30-SEP-12 7521 01-sep-12 TO 30-SEP-12 7788
