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...
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...
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?
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.
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...
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...
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 |...
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...
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....
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...
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...
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)...
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...
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...
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....
Issue is with ojdbc jar.....Please put the ojdbc5.jar in classpath and try.