1) Determine the maximum value present in column FiscalYear, and then the maximum value available for this FiscalYear under the column Accounting Period.
I can do this fairly easy on Microsoft SQL server, but so far was not able to do this easily on Oracle database. My other observation is that using the MAX function on Oracle is very slow (even with thse fields being indexed). Is it possible to run this query on Oracle with only one pass through the table where the returned result will show 2012 for FiscalYear, and 2 for Accounting Period?
if there is any inbuilt function or way to find a row in the table that is having value for maximum number of columns.
For example, the table A has 5 columns (c1,c2,c3,c4,c5) and it has 3 records(r1,r2,r3) and r1 has values only for c1,c2 r2 has values only for c1,c2,c3,c4 r3 has values only for c1
so I should get the result as "r3 has values for 4 columns & it is not having value for column c5".
I want to use the aggregate CORR_S function which take two numerical columns and output the correlation between them. Assuming I have the table:
Employee(name, gender, salary) where I want to create two derived columns, how would I do it?
Like, ------------------- SELECT CORR_S( SELECT salary FROM employees WHERE salary < 10000, SELECT salary FROM employees WHERE salary > 10000 ) as correlation FROM employees ---------------------
It will find the correlation between salary's less than $10,000 in one column and salary's more than $10,000 in another column. Of course, I get an error when running the query. It's no problem if I use CORR_S(salary, salary) but I want to use derived columns, is it possible? Or, will I need to create a new table view with two new columns CORR_S(lowSalary, highSalary)?
ive got two select statements which fetches data from different tables. I need to join the two result set . is it possible to do it from sql. Heres the query.
1)
SELECT COUNT(CASE WHEN (INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=1) THEN 1 END)CUSTOMER_EMAIL, COUNT(CASE WHEN INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=2 THEN 1 END)OTHER_EMAIL,
[Code]....
2)
SELECT SUM (CHEQUE_TOTAL) CHEQUE_TOTAL FROM RI_CHEQUE_VOUCHER_REFUND refund INNER JOIN CH_CASE case ON (case.id = refund.id) INNER JOIN EVA_ENTITY_DEFINITION ed ON (ed.name= 'ChequeRefundCaseED') WHERE case.creation_time<= SYSDATE AND case.creation_time>= SYSDATE-7
Again I need to combine the resultset.So the result would look like
I'm currently doing migration from Oracle 10gR2 RDF to Oracle 11gR2 Semantic Technology.I followed the steps on the documentation and successfully created the network using the following:
----- EXECUTE SEM_APIS.CREATE_SEM_NETWORK('rdf_tblspace'); CREATE TABLE rdf_network_trace (id NUMBER, triple SDO_RDF_TRIPLE_S); --Created SEQUENCE andTRIGGER FOR rdf_network_trace id [code]....
when I looked at my Node Ids, they were like +635762253807433724+, +6118969225776891730+. The problem is, I am not the one who is assigning Node Ids, They were automatically generated when inserting TRIPLE data to the rdf table.
Using Oracle 11gR2 on windows 7 client. I have a question on calculating sum() on multiple columns on different columns and store the results in a view. Unfortunately I could not post the problem here as it keeps on giving error "Sorry, this content is not allowed", without telling where or what it is! So I had to post it in the stack-overflow forum, here is the link: [URL] .........
I want to create a table with a length greater than 30.I Thought there was a way to override the max length for for a table name in Oracle 11.2.0.2.I cant find a documentation that states how to get it done.
I think the maximum length of table and column name in oracle 11g is 30 characters.I want to increase the limit as i want to import a mysql database that is having bigger table names.Can i preset the table name and column name length??
my above table consists of two columnc sl_no and Status,col1 indicates the process no and the status indicates it is implemented or cancelled or failed during implementation.
i need to find the percentage of the implemented+cancelled process over failed..
test case: lets consider, A->count(Sl_NO) B->Count(STATUS) where STATUS='Implemented'
[Code]..
i think i have satisfactorily given enough data.. make it out using sql query..
I have this table column of varchar2 data type. I need to find out if it contains anything other than numbers or alphabets(basically to see if it contains any special characters in it).
I just need to fetch only those rows that has special characters. How do I get this?
I have table emp that contains empno, empname, mgr .what i want is a general procedure that will take empno as input and will give all the child rows and parent for entered empno.
for ex
E A-->B-->C-->D F-->G H
When i will pass d as node it will return c,b,a,e,f,g,h
i want to find the name of user who make changes in the table structure or create any index or constraint or unique key or alter the column? Is there any way to find in Oracle. in which table what change has been done as well?
following Output needed
userid, username, schemaname, schemachangetime, "what_change_has_been_made", IP address or Computername
I have to find the next EMP whose MAX_SQUENCE needs to be updated with a sequence. In this case the next would be '010' I fetched the EMP who falls after the max value of the column max_sequence in order of EMPCODE.
select emp_next from ( SELECT emp,max_sequence, LEAD(emp, 1, 0) OVER (ORDER BY emp) AS emp_next from test_emp_wip where wip > 0 ) where max_sequence=(select max(max_sequence) from test_emp_wip where wip > 0) EMP_NEXT 010 After sometime, the record would look like :-
How to merge multiple rows into single row (but multiple columns) efficiently.
For example
IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5 23 asdc 1 Location USA NM ABQ Four Seasons 87106 23 asdc 1 Stats 2300 91.7 8.2 85432 23 asdc 1 Audit 1996 June 17 1200 65 affc 2 Location USA TX AUS Hilton 92305 65 affc 2 Stats 5510 42.7 46 9999 65 affc 2 Audit 1996 July 172 1100
where different attributes mean different thing for each Information_type. For example for Information_Type=Location
Attribute_1 means Country Attribute_2 means State and so on.
For example for Information_Type=Stats
Attribute_1 means Population Attribute_2 means American Ethnicity percentage and so on.
I want to create a view that shows like below:
IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime 23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200 65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100
i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.
We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,
Hierarchy Table Parent Child Report1Table A Table ATable M Table ATable N Table NTable Z
Requirement :
From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this
-ParentChild 1Child 2 Child 3 -Report1Table ATable M -Report1Table ATable N Table Z
Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.
SQL Statements to create hierarchy table:
create table hierarchy (parent varchar2(20), child varchar2(20)); insert into hierarchy values ('Report1','Table A'); insert into hierarchy values ('Report1','Table B'); insert into hierarchy values ('Table A','Table M'); insert into hierarchy values ('Table B','Table N'); insert into hierarchy values ('Report2','Table P'); insert into hierarchy values ('Table M','Table X'); insert into hierarchy values ('Table N','Table Y'); insert into hierarchy values ('Report X','Table Z');
Approached already tried :
1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;
2)Using connect by path function : select * from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path from hierarchy start with parent='Report1' connect by prior child =parent) a where Leaf not in (0);
Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.
I want to do an import of a table from my old dump file.The same table is already there in the development box but few more columns are added to that table while testing so in the dump those columns are not available.
TABLE_EXISTS_ACTION=TRUNCATE The new table SQL> desc "TESTINVENTORY"."TTRANSACTION" Name Null? Type ----------------------------------------------------------------------------------- -------- -------------------------------------------------------- TRANSACTIONIDNOT NULL CHAR(26) BRANCHCODE NOT NULL CHAR(3) EXTERNALSYSTEM NOT NULL CHAR(3) EXTRACTSYSTEM NOT NULL CHAR(3) OWNERBRANCHCODE NOT NULL CHAR(3) TRADEREFERENCE NOT NULL CHAR(20) [code]...
