Oracle SQL Developer: How to transpose rows to columns using PIVOT function
You have a stray semi-colon in your statement, after: WHERE partyId = 100; Remove that to make it: SELECT * FROM ( SELECT partyId, contacttext, contacttypecd FROM CONTACT WHERE partyId = 100 ) PIVOT ( MAX(contacttext) FOR contacttypecd in (1 Phone, 2 Fax, 3 Mobile, 4 Email)); PARTYID PHONE FAX...
How to extract postcode/digits from one column and put it in to another column in SQL Oracle 9i
sql,regex,substring,trim,oracle9i
Below are two sql statements, one to select, and see the impact of the update statement, and the update statement. Back up your tables before you run any updates and always test that the update is doing what you expect by running it as a select statement first. Note: I...
java.sql.SQLException when trying to connect to Oracle database
Looks like ORA-12514: TNS:listener does not currently know of service requested in connect descriptor(according to error code). So maybe your service name isn't myDB?
Check: Oracle Data Access Components for Windows 7
oracle,oracle11g,oracle10g,oracle-sqldeveloper,oracle9i
Oracle Universal Installer is a Java application that can handle complex requirements. ODAC release for 9.2.0.7.0 will work fine with Windows7 32 bit release. If you face any problem during installation change the compatibility setting to "WindowsXP ServicePack 2". Hope it helps.
Oracle select one row in join and subquery inside a decode function
sql,oracle,subquery,decode,oracle9i
I think this query fits your needs: select ev.ID, ev.TIPO, evFECHAINICIO, decode(ev.PORCENTAJE, 100, 'COMPLETED', 'IN PROGRESS') status, count(*) nb from EVENTS_ELEMENTS el, EVENTS ev where ev.ID = el.ID_EVENTO group by ev.ID, ev.TIPO, evFECHAINICIO, decode(ev.PORCENTAJE, 100, 'COMPLETED', 'IN PROGRESS') ; I group by ID and status, status being computed thanks to...
When should I use a nested table and when a reference?
It depends on the requirements for usage of the data. In your example with airlines and flights a flight should have a foreign key to its airline. The main table is flights and airlines is a codebook. An example case where a nested table would be a good choice: A...
Using the ROLLUP total within a query
You can use RATIO_TO_REPORT function to get the ratio, without calculating the sum. SQL Fiddle Query 1: select item, cost, ratio_to_report(cost) over () as percent from costs Results: | ITEM | COST | PERCENT | |------|------|---------| | car | 10 | 0.625 | | cat | 2 | 0.125 |...
How to query and compare strings containing fractions in Oracle 9i
oracle,plsql,oracle9i,fractions
you are asking to execute a string or varchar2 as equation, the result is a value of this equation. right? here is a solution for this, I assume the space in "7 4/5" means "7+ 4/5" or you have to modify the function bellow to meet your requirements. create or...
connect to oracle9i using python2.7 on ubuntu 64bit
I was able to connect to Oracle successfully using the cx_Oracle module. (CentOs 5.9, Python 2.7.5, Oracle 10) The code would be something similar to: import cx_Oracle dsn = cx_Oracle.makedsn(host, port, sid) db = cx_Oracle.connect(user, password, dsn) After connecting to Oracle, you can create a Cursor object to make queries....
How to get each total from database?
Try This: SELECT T1.Company_accountNo As Account_no, sum(T2.Amount) As Total_Amount, count(T1.Company_accountNo) AS No_of_Cheque FROM Table1 T1 Inner Join Table2 T2 ON T1.id = T2.company_accnopky Group By T1.Company_accountNo I Think You are Missing Group By...
What is the purpose of “RETURN AS VALUE” in NESTED TABLES (Oracle 9i)
As far as I can tell, the only difference is that LOCATORs are a bit faster than VALUEs. But that doesn't make sense and I'm hoping somebody will prove me wrong; there's almost never a "fast=true" switch. According to the SQL Language Reference: RETURN [AS] Specify what Oracle Database returns...
Alter table add column as select statement
This is where you need VIRTUAL COLUMN. If you are on 11g and up, you could certainly do - alter table table_name add (column_name [data_type] [generated always] as (column_expression) [virtual]); In your case, it will be something like - alter table customer_schedule add (week_number data_type generated always as (TO_CHAR((SCHEDULE_DATE),'iw') VIRTUAL)...
SQL select from table with alias (Oracle9i)
Oracle does not accept AS for table aliases and I see no way to make it work. Can't you do anything in Java? AS for column aliases is optional in Oracle, so you could look for all " AS " in the generated string and remove them (thus removing AS...
SQL can't group by… Options?
I suggest the following query, with some major tweaks to yours and removed redundancy. Use explicit JOINs for better readability, and IN clause to save some space in code. Planner will translate this clause anyways. CREATE VIEW friends AS SELECT actor, director, film, num_together FROM( SELECT a.aid AS actor, b.aid...
Find how many times a table must be searched to get a match Oracle database
sql,select,numbers,oracle9i,kevin-bacon
This is difficult to verify without sample data, but the following answer is, at a minimum syntactically correct. What you want here is clearly a recursive query. There are to ways to do this in Oracle: using common-table expressions (CTE) (i.e. a with clause); or using the connect by clause....
Oracle 9i does not add as sysdba in JDBC connection String?
Issue is with ojdbc jar.....Please put the ojdbc5.jar in classpath and try.