oracle

[Solved] How to write and call an Oracle function in SQL

by

Generally it is considered bad practice to call a function in SQL which executes SQL. It creates all kinds of problems. Here is one solution: create or replace function my_fun ( p_sum in number) return varchar2 is begin if p_sum > 100 then return ‘ALERT’; else return ‘OK’; end if; end; / Run it like … Read more

[Solved] Oracle Sql Statement for unique timestamp for each row

by

The following UPDATE statement will guarantee that each row has a unique MY_TIMESTAMP value, by increasing the milliseconds by the rownum value. EDIT: After Alessandro Rossi pointed out that there could be duplicate values, the following query has been modified to use SYSTIMESTAMP for the update. UPDATE ITEM_HISTORY SET my_timestamp = SYSTIMESTAMP + NUMTODSINTERVAL(rownum/1000, ‘SECOND’); … Read more

[Solved] SQL ORA-02063 – How to fix this?

by

It appears to be the blank space in DEF_WELD_ INC, which should be DEF_WELD_INC. I created a table v_biq_r8_qwb_events to match the columns expected by subquery “AQE Source Data 5.30.2018”, removed the line breaks in things like ON (“AQE Source Data 5.30.2018″.”EVENT_NO” = “400 Machines”.”MIN(EVENT_NO)”) which I’m assuming should be ON (“AQE Source Data 5.30.2018″.”EVENT_NO” … Read more

[Solved] Total count in sql

by

Something like this: CREATE VIEW GetNumberOfStudentswithMajor AS SELECT COUNT(*) FROM dbo.Students where Major1 is not null and Major2 is not null solved Total count in sql

[Solved] Oracle-Conveting SQL to ANSI SQL

by

This section is probably causing the problem: FROM BOM_CHILDS C , XX_MAIN.XX_MAST MAST , XX_MAIN.XX_STPO STPO WHERE C.MATNR = MAST.MATNR(+) AND MAST.STLNR = STPO.STLNR(+) AND MAST.STLAN(+) = ‘1’ AND MAST.WERKS(+) = C.WERKS AND STPO.IDNRK IS NULL To make this a bit easier, lets rearrange the WHERE clause to order the tables by how they relate: … Read more

[Solved] add data into primary key [duplicate]

by

The old way of doing this is a multi-step process: add the column which will be the primary key update the column enforce the primary key. Something like this: create sequence t23_id; alter table t23 add id number; update t23 set id = t23_id.nextval ; alter table t23 add constraint t23_pk primary key (id); In … Read more

[Solved] what the meaning of this code in sql? i found this code in oracle apex and that says that query for data change history

by

select xxpst_util_pkg.get_type_id_by_code (‘CHANGE_REQUEST’, ‘MILESTONE_DUE_DATE_CHANGE’) comment_type_id from dual is a mean of calling a PLSQL function from a SQL statement. DUAL returns a single row, so it just yields the output of the function. It’s not dissimilar from doing my_variable := xxpst_util_pkg.get_type_id_by_code (‘CHANGE_REQUEST’, ‘MILESTONE_DUE_DATE_CHANGE’) In terms of what comes back, you’d need to look in the … Read more

[Solved] Last 6 months aggregation

by

You can try with something like this: select TO_CHAR(CALLDATE,’yyyymm’),count(*) from yourTable –filter last n months (in this case 7) where CALLDATE> SYSDATE – INTERVAL ‘7’ MONTH –group by month group by TO_CHAR(CALLDATE,’yyyymm’); If you need zeros for months without a call: WITH MONTH_COUNTER AS ( SELECT LEVEL-1 AS ID FROM DUAL CONNECT BY LEVEL <= … Read more

[Solved] Data grouped by date in oracle sql [closed]

by

This is a direct application of the Tabibitosan method for finding sets of “consecutive” rows in sequences. The difference of two analytic row_number() functions creates the additional grouping flag needed before we apply standard aggregation. select employee, job, min(start_date) as start_date, max(end_date) as end_date, FTE from ( select employee, job, start_date, end_date, FTE, row_number() over … Read more