oracle

sql bonus salary ques using decode (single row functions ) [on hold]

select ename , sal, to_char(hiredate,’Mon ddth, YYYY’),to_char( decode(trunc(months_between(sysdate, hire date)/12)>31), sal*1.2+(nvl(comm,0)/12), (trunc(months_between(sysdate, hiredate)/12)>=30), sal+100),’$99,999.9 9′) new_sal from scott.emp; select ename , sal, to_char(hiredate,’Mon ddth, YYYY’),to_char( decode(trunc(months_between(sysdate, hire date)/12)>31), sal*1.2+(nvl(comm,0)/12), (trunc(months_between(sysdate, hiredate)/12)>=30), sal+100),’$99,999.9 9′) new_sal from scott.emp ERROR at line 1: ORA-00907: missing right parenthesis ————————————— I have tried above example but not getting the error!! Source: oracle

Foreign key on two columns

For my school project i needed to create a database. This is my ERD. In SQL i create the Category table: CREATE table “Category” ( “Id” NUMBER(5) NOT NULL, “SubCategoryId” NUMBER(5) NULL, “Name” VARCHAR2(32) NOT NULL, CONSTRAINT “Category_pk” PRIMARY KEY (“Id”) ); ALTER TABLE “Category” ADD CONSTRAINT “Category_fk” FOREIGN KEY (“SubCategoryId”) REFERENCES “Category” (“Id”); Product table CREATE table “Product” (…
Read more

Rolling Average in Oracle SQL

LotNumber Device Measure MeasureDate RowNumber LotA DevA 1.1 10/1/15 0:00 1 LotA DevA 1.2 10/1/15 1:00 1 LotA DevB 1.1 10/1/15 2:00 2 LotB DevA 1.3 10/1/15 3:00 3 LotB DevA 1.4 10/1/15 4:00 3 LotA DevA 1.2 10/1/15 5:00 4 LotC DevA 1.3 10/1/15 6:00 5 LotD DevA 1.5 10/1/15 7:00 6 LotE DevA 1.1 10/1/15 8:00 7 LotF…
Read more

Extract number from string with Oracle function

I need to create an Oracle function that takes a string as parameter. The string contains letters and numbers. I need to extract all the numbers from this string. For example, if I have a string like RO1234, I need to be able to use a function, say extract_number(RO1234), and the result would be 1234. To be even more precise,…
Read more

Select only a row of two with a same field

I’m trying to solve this sql problem. For example i have this: A1 TIER TRY10,49 Tier 7 TRY104,99 Tier 51 TRY104,99 Tier 54 TRY1049,99 Tier 84 TRY11,19 Tier 6 TRY111,99 Tier 55 TRY111,99 Tier 52 I want to select only rows with smaller Tier when A1 of a row it’s equal to A1 of another row. For example A1 of…
Read more

Oracle SQL combine 2 selects to one columm

Sorry if this sounds vague … I have searched for the answer for this but, am finding it hard to explain – therefore hard to search for. I have a really simple script… select pr1.polypart_no match_from, pr1.part_no match_to from oes_polybox_replace pr1 where pr1.plant = ‘W’ and pr1.part_no = (select max(pr2.part_no) from oes_polybox_replace pr2 where pr2.plant = ‘W’ and pr2.polypart_no =…
Read more

Insert CLOB datatype in Oracle DB via DB link

I am trying to insert data from a text field which is of varchar2(8000) on sql server.I have created a similar column but of clob datatype on oracle db. can you please help me resolve the inconsistent datatypes issue and improve the performance of the query? PLease let me know if there is any other better way to insert huge…
Read more

How to display a list of employees joining on a particular date

I have a table EMPLOYEE and it contains data like this: EID JOB JOINING_DATE —— —- ———– 1 SE 2015-01-01 00:00:00.0 2 SW 2015-01-02 00:00:00.0 3 SD 2015-01-03 00:00:00.0 4 SX 2015-01-04 00:00:00.0 5 SP 2015-01-05 00:00:00.0 6 SV 2015-01-06 00:00:00.0 7 SM 2015-01-07 00:00:00.0 8 SE 2015-01-04 00:00:00.0 9 SW 2015-01-03 00:00:00.0 10 SD 2015-01-01 00:00:00.0 11 SX 2015-01-03…
Read more

JPA and ORACLE function: error when call a stored procedure using NamedNativeQuery

I have an Oracle function CREATE OR REPLACE FUNCTION SOL.UPDATE_LD_OAUDITORID ( vCUSTOMERID NUMBER, vAPPOINTOFCAID NUMBER, vAUDITORID NUMBER, vOAUDITORID NUMBER) RETURN NUMBER IS PRAGMA AUTONOMOUS_TRANSACTION; vGROUPID NUMBER; rTN NUMBER; CURSOR c IS SELECT COMPANYID, GROUPID, PERIODID, PROCESSID, CUSTOMERID, APPOINTOFCAID, AUDITORID FROM LIQUIDATIONSDETAILS WHERE CUSTOMERID = vCUSTOMERID AND APPOINTOFCAID = vAPPOINTOFCAID AND AUDITORID = vAUDITORID AND ( PERIODID = fn_periodcurrent AND PROCESSID…
Read more