Friday, August 2, 2019

SQL


Query to divide a set of data into 10 batches
SELECT
    ROW_NUMBER() OVER(
        PARTITION BY mod(ROWNUM, 100)
        ORDER BY
            ROWNUM
    ) batch_number,
    h.*
FROM
    oe_order_headers_all h
WHERE
    ROWNUM < 1001
ORDER BY
    batch_number;

SELECT
    mod(400, 100)
FROM
    dual;

SELECT
    DATE '2018-10-01',
    TO_CHAR(DATE '2018-10-01', 'DAY'),
    last_day(DATE '2018-10-01'),
    TO_CHAR(last_day(DATE '2018-10-01'), 'DAY')
FROM
    dual;

SELECT
    COUNT(TO_CHAR(DATE '2018-10-01', 'DAY'))
FROM
    dual
WHERE
    TO_CHAR(DATE '2018-10-01', 'DAY') = 'SATURDAY';

SELECT
    COUNT(*)
FROM
    dual
WHERE
    dummy BETWEEN SYSDATE AND last_day(DATE '2018-10-01');

Number of working days

SELECT
    start_date,
    end_date,
    greatest(next_day(start_date, 'MON') - start_date - 3, 0) + ( ( next_day(end_date, 'MON') - next_day(start_date, 'MON') ) / 7
    ) * 5 - greatest(next_day(end_date, 'MON') - end_date - 3, 0) working_days
FROM
    (
        SELECT
            TO_DATE(:sdate) start_date,
            TO_DATE(:edate) end_date
        FROM
            dual
    );

No. of working days in a month

SELECT
    ( trunc(last_day(SYSDATE)) - trunc(SYSDATE, 'MM') + 1 - (
        SELECT
            COUNT(*) voffs
        FROM
            (
                SELECT
                    vdate,
                    TO_CHAR(vdate, 'DY') vday
                FROM
                    (
                        SELECT
                            trunc(SYSDATE, 'MM') + level - 1 vdate
                        FROM
                            dual
                        CONNECT BY
                            level <= abs(trunc(SYSDATE, 'MM') - last_day(SYSDATE) - 1)
                    )
            )
        WHERE
            vday IN (
                'SAT',
                'SUN'
            )
    ) ) working_days
FROM
    dual;

SELECT
    trunc(last_day(DATE '2018-11-01')) - trunc(DATE '2018-11-01', 'MM') + 1 - (
        SELECT
            COUNT(*) voffs
        FROM
            (
                SELECT
                    vdate,
                    TO_CHAR(vdate, 'DY') vday
                FROM
                    (
                        SELECT
                            trunc(DATE '2018-11-01', 'MM') + level - 1 vdate
                        FROM
                            dual
                        CONNECT BY
                            level <= abs(trunc(DATE '2018-11-01', 'MM') - last_day(DATE '2018-11-01') - 1)
                    )
            )
        WHERE
            vday IN (
                'SAT',
                'SUN'
            )
    ) working_days
FROM
    dual;

SELECT
    abs(DATE '2018-10-01' - last_day(DATE '2018-10-01') - 1)
FROM
    dual;

SELECT
    SYSDATE,
    trunc(SYSDATE, 'MM')
FROM
    dual;

SELECT
    trunc(last_day(SYSDATE)) - trunc(SYSDATE, 'MM') + 1
FROM
    dual;

SELECT
    *
FROM
    emp;

SELECT
    *
FROM
    (
        SELECT
            sal,
            DENSE_RANK() OVER(
                ORDER BY
                    sal DESC
            ) ranking
        FROM
            emp
    )
WHERE
    ranking = 4;

SELECT
    *
FROM
    (
        SELECT
            ename,
            job,
            deptno,
            sal,
            DENSE_RANK() OVER(
                PARTITION BY deptno
                ORDER BY
                    sal DESC
            ) ranking
        FROM
            emp
    )
WHERE
    ranking = 1;

SELECT
    level,
    MAX(sal)
FROM
    emp
WHERE
    level = 3
CONNECT BY
    PRIOR sal > sal
GROUP BY
    level;

SELECT
    add_months(SYSDATE, - 1)
FROM
    dual;

SELECT
    TO_CHAR(add_months(SYSDATE, - 1), 'MONTH')
FROM
    dual;

Employees hired in the month of September

SELECT
    *
FROM
    emp
WHERE
    TO_CHAR(hiredate, 'MON') LIKE 'SEP';

SELECT
    last_day(add_months(SYSDATE, - 1))
FROM
    dual;

SELECT
    last_day(TO_DATE('01-DEC-20', 'DD-MON-YY'))
FROM
    dual;

SELECT
    trunc(add_months(TO_DATE('01-JAN-81', 'DD-MON-YY'), - 1), 'MM'),
    last_day(add_months(TO_DATE('01-JAN-81', 'DD-MON-YY'), - 1))
FROM
    dual;

Employees hired last month

SELECT
    *
FROM
    emp
WHERE
    hiredate BETWEEN trunc(add_months(SYSDATE, - 1), 'MM')  --first day of last month
     AND last_day(add_months(SYSDATE, - 1));    --last day of last month

SELECT
    *
FROM
    emp
WHERE
    hiredate BETWEEN TO_DATE('01-DEC-80', 'DD-MON-YY') AND TO_DATE('31-DEC-80', 'DD-MON-YY');

SELECT
    TO_DATE('01-DEC-80', 'DD-MON-YY'),
    TO_DATE('31-DEC-80', 'DD-MON-YY')
FROM
    dual;

SELECT
    TO_DATE('01-DEC-80', 'DD-MON-YY') + level - 1
FROM
    dual
CONNECT BY
    level <= abs(last_day(TO_DATE('01-DEC-80', 'DD-MON-YY')) - trunc(TO_DATE('01-DEC-80', 'DD-MON-YY'), 'MM') + 1);

SELECT
    *
FROM
    emp
WHERE
    TO_DATE(hiredate, 'DD-MON-YY') BETWEEN trunc(add_months(TO_DATE('01-JAN-81', 'DD-MON-YY'), - 1), 'MM')  --first day of last month
     AND last_day(add_months(TO_DATE('01-JAN-81', 'DD-MON-YY'), - 1));    --last day of last month

SELECT
    ROWID,
    e.*
FROM
    employees2 e;

DELETE FROM employees2
WHERE
    ROWID IN (
        SELECT
            MAX(ROWID)
        FROM
            employees2
        GROUP BY
            employee_id
    );

INSERT INTO employees2
    SELECT
        *
    FROM
        employees;

SELECT
    dbms_metadata.get_ddl('TABLE', 'EMPLOYEES', 'APPS')
FROM
    dual;

CREATE TABLE "APPS"."EMPLOYEES2" (
    "EMPLOYEE_ID"      NUMBER(6, 0),
    "FIRST_NAME"       VARCHAR2(20),
    "LAST_NAME"        VARCHAR2(25),
    "EMAIL"            VARCHAR2(25),
    "PHONE_NUMBER"     VARCHAR2(20),
    "HIRE_DATE"        DATE,
    "JOB_ID"           VARCHAR2(10),
    "SALARY"           NUMBER(8, 2),
    "COMMISSION_PCT"   NUMBER(2, 2),
    "MANAGER_ID"       NUMBER(6, 0),
    "DEPARTMENT_ID"    NUMBER(4, 0),
    "GENDER"           CHAR(1)
);

SELECT
    TO_CHAR(SYSDATE, 'fmMonthfm YYYY')
FROM
    dual;

Pivot

SELECT
    *
FROM
    (
        SELECT
            customer_id,
            product_code,
            quantity
        FROM
            pivot_test
    ) PIVOT (
        SUM ( quantity )
    AS sum_quantity
        FOR ( product_code )
        IN ( 'A' AS a, 'B' AS b, 'C' AS c )
    );

SELECT
    *
FROM
    (
        SELECT
            product_code,
            quantity
        FROM
            pivot_test
    ) PIVOT (
        SUM ( quantity )
    AS sum_quantity
        FOR ( product_code )
        IN ( 'A' AS a, 'B' AS b, 'C' AS c )
    );

First day of current month

SELECT
    trunc(SYSDATE, 'MM'),
    trunc(SYSDATE, 'MONTH')
FROM
    dual;

SELECT
    trunc(DATE '2017-03-28', 'MM')
FROM
    dual;

Last day of month

SELECT
    last_day(SYSDATE)
FROM
    dual;

SELECT
    last_day(DATE '2018-08-01')
FROM
    dual;

First day of year

SELECT
    trunc(SYSDATE, 'YEAR'),
    trunc(SYSDATE, 'YY')
FROM
    dual;

SELECT
    trunc(TO_DATE('18-JAN-18', 'DD-MON-YY'), 'YY')
FROM
    dual;

Last day of year

SELECT
    add_months(trunc(SYSDATE, 'YEAR'), 12) - 1
FROM
    dual;

SELECT
    add_months(trunc(TO_DATE('18-JAN-18', 'DD-MON-YY'), 'YY'), 12) - 1
FROM
    dual;

Number of days in a month

SELECT
    COUNT(trunc(SYSDATE, 'MONTH') + level - 1)
FROM
    dual
CONNECT BY
    level < abs(trunc(SYSDATE, 'MONTH') - last_day(SYSDATE) - 1);

Feb total days

SELECT
    COUNT(trunc(DATE '2018-02-18', 'MONTH') + level - 1)
FROM
    dual
CONNECT BY
    level < abs(trunc(DATE '2018-02-18', 'MONTH') - last_day(DATE '2018-02-18') - 1);

Salary in words

SELECT
    ename,
    sal,
    TO_CHAR(TO_DATE(sal, 'jsp'), 'jsp') sal_in_words
FROM
    emp;

Breaking name into 3 parts

SELECT
    substr('siva rama krishna', 1, instr('siva rama krishna', ' ', 1, 1)),
    substr('siva rama krishna', instr('siva rama krishna', ' ', 1, 1), instr('siva rama krishna', ' ', instr('siva rama krishna',
    ' ', 1, 1), 1)),
    substr('siva rama krishna', instr('siva rama krishna', ' ', 1, 2))
FROM
    dual;

Decode

SELECT
    ename,
    deptno,
    DECODE(deptno, 10, 'A', 20, 'B', 'C') dept_name
FROM
    emp
ORDER BY
    3;

Case

SELECT
    ename,
    deptno,
    CASE deptno
        WHEN 10   THEN
            'A'
        WHEN 20   THEN
            'B'
        ELSE
            'C'
    END dname
FROM
    emp
ORDER BY
    3;

Retrieving even/odd records
--even records

SELECT
    ROWNUM,
    empno
FROM
    emp
GROUP BY
    ROWNUM,
    empno
HAVING
    mod(ROWNUM, 2) = 0
ORDER BY
    1;

--odd records

SELECT
    ROWNUM,
    empno
FROM
    emp
GROUP BY
    ROWNUM,
    empno
HAVING
    mod(ROWNUM, 2) = 1
ORDER BY
    1;

Days and months from DOB

SELECT
    trunc(months_between(SYSDATE, TO_DATE('27-JUL-1992', 'DD-MON-YYYY')) / 12) years,
    trunc(months_between(SYSDATE, TO_DATE('27-JUL-1992', 'DD-MON-YYYY')) - trunc(months_between(SYSDATE, TO_DATE('27-JUL-1992', 'DD-MON-YYYY'
    )) / 12) * 12) months,
    trunc(SYSDATE) - add_months(TO_DATE('27-JUL-1992', 'DD-MON-YYYY'), trunc(months_between(SYSDATE, TO_DATE('27-JUL-1992', 'DD-MON-YYYY'
    )))) AS days
FROM
    dual;

SELECT
    trunc(months_between(SYSDATE, TO_DATE('18-JAN-1991', 'DD-MON-YYYY')) / 12) years,
    trunc(months_between(SYSDATE, TO_DATE('18-JAN-1991', 'DD-MON-YYYY')) - trunc(months_between(SYSDATE, TO_DATE('18-JAN-1991', 'DD-MON-YYYY'
    )) / 12) * 12) months,
    trunc(SYSDATE) - add_months(TO_DATE('18-JAN-1991', 'DD-MON-YYYY'), trunc(months_between(SYSDATE, TO_DATE('18-JAN-1991', 'DD-MON-YYYY'
    )))) AS days
FROM
    dual;

Finding letters starting with 'K'

SELECT
    *
FROM
    emp
WHERE
    substr(ename, instr(ename, ' ', 1, 1)) LIKE 'K%';

Display yesterday date for all hiredate

SELECT
    hiredate,
    new_time(hiredate, 'EST', 'PDT'),
    hiredate - 1
FROM
    emp;

Display date in words

SELECT
    hiredate,
    TO_CHAR(hiredate, 'DDSP MONTH YEAR')
FROM
    emp;

Display number in words

SELECT
    sal,
    TO_CHAR(TO_DATE(sal, 'J'), 'jsp') sal_in_words
FROM
    emp;

Display nth highest sal - dept wise
--level

SELECT
    deptno,
    MAX(sal)
FROM
    emp
WHERE
    level = 3
CONNECT BY
    PRIOR sal > sal
GROUP BY
    deptno;

--Dense_rank

SELECT
    deptno,
    sal
FROM
    (
        SELECT
            deptno,
            sal,
            DENSE_RANK() OVER(
                PARTITION BY deptno
                ORDER BY
                    sal DESC
            ) ranking
        FROM
            emp
    )
WHERE
    ranking = 1;

SELECT
    *
FROM
    salgrade;

CREATE TABLE salgrade (
    grade   NUMBER,
    losal   NUMBER,
    hisal   NUMBER
);

INSERT INTO salgrade VALUES (
    1,
    700,
    1200
);

INSERT INTO salgrade VALUES (
    2,
    1201,
    1400
);

INSERT INTO salgrade VALUES (
    3,
    1401,
    2000
);

INSERT INTO salgrade VALUES (
    4,
    2001,
    3000
);

INSERT INTO salgrade VALUES (
    5,
    3001,
    9999
);

SELECT
    *
FROM
    salgrade;

Sum of two numbers

SET SERVEROUTPUT ON;

DECLARE
    v1   NUMBER;
    v2   NUMBER;
    v3   NUMBER;
BEGIN
    v1 := &enter_value_1;
    v2 := &enter_value_2;
    v3 := v1 + v2;
    dbms_output.put_line('sum of '
                         || v1
                         || ' and '
                         || v2
                         || ' is: '
                         || v3);

END;
/

CREATE TABLE emp1
    AS
        SELECT
            *
        FROM
            emp;

SELECT
    empno,
    sal
FROM
    emp1;

Accept empno as input and increase salary by 1000

SET SERVEROUTPUT ON;

DECLARE
    ip_empno NUMBER := &enter_employee_number;
BEGIN
--vempno := ip_empno;
    UPDATE emp1
    SET
        sal = sal + 1000
    WHERE
        empno = ip_empno;

    IF SQL%notfound THEN
        dbms_output.put_line(ip_empno || ' is not found ');
    ELSE
        dbms_output.put_line('salary of '
                             || ip_empno
                             || ' is updated ');
    END IF;

    COMMIT;
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('entered empno '
                             || ip_empno
                             || ' is not found ');
    WHEN OTHERS THEN
        dbms_output.put_line('encountered exception: ' || sqlerrm);
END;
/

Accept deptno as input and increment sal by 100

SELECT
    deptno,
    empno,
    sal
FROM
    emp1
ORDER BY
    deptno;

SET SERVEROUTPUT ON;

DECLARE
    ip_deptno NUMBER := &enter_deptno;
--cursor c1 is
--select deptno, empno, sal from emp1 where deptno = &enter_deptno;
BEGIN
    IF ( SQL%notfound ) THEN
        dbms_output.put_line('not found');
    ELSE
--for i in c1 loop
        UPDATE emp1
        SET
            sal = sal + 9
        WHERE
            deptno = ip_deptno;

        COMMIT;
    END IF;
--dbms_output.put_line('deptno: '|| i.deptno ||' '|| 'empno: ' || i.empno);
--end loop;
END;
/

SELECT
    greatest(next_day(TO_DATE('01-OCT-2018', 'DD-MON-YYYY'), 'MON') - TO_DATE('01-OCT-2018', 'DD-MON-YYYY') - 3, 0)
FROM
    dual;

SELECT
    ( ( next_day(TO_DATE('31-OCT-2018', 'DD-MON-YYYY'), 'MON') - next_day(TO_DATE('01-OCT-2018', 'DD-MON-YYYY'), 'MON') ) / 7 ) *
    5 days
FROM
    dual;

SELECT
    greatest(next_day(TO_DATE('31-OCT-2018', 'DD-MON-YYYY'), 'MON') - TO_DATE('31-OCT-2018', 'DD-MON-YYYY') - 3, 0) days
FROM
    dual;

SELECT
    greatest(next_day(TO_DATE('31-OCT-2018', 'DD-MON-YYYY'), 'MON') - TO_DATE('31-OCT-2018', 'DD-MON-YYYY'))
FROM
    dual;

Using cursor update multiple records

DECLARE
--ip_deptno number:=&enter_deptno;
    ldeptno   emp.deptno%TYPE;
    lempno    emp.empno%TYPE;
    lsal      emp.sal%TYPE;
    CURSOR c1 IS
    SELECT
        deptno,
        empno,
        sal
    FROM
        emp1
    WHERE
        deptno = &enter_deptno;

BEGIN
    OPEN c1;
    LOOP
        FETCH c1 INTO
            ldeptno,
            lempno,
            lsal;
        EXIT WHEN c1%notfound;
        UPDATE emp1
        SET
            sal = sal - 9
        WHERE
            deptno = ldeptno
            AND empno = lempno;

    END LOOP;

    CLOSE c1;
END;
/

SELECT
    deptno,
    empno,
    sal
FROM
    emp1
WHERE
    deptno = 10;

SELECT
    *
FROM
    gl_ledgers;

SELECT DISTINCT
    ledger_category_code
FROM
    gl_ledgers;

SELECT
    *
FROM
    fnd_form_functions_vl;

SELECT
    *
FROM
    fnd_responsibility_vl;

SELECT
    resp.*
FROM
    fnd_form_functions_vl   func,
    fnd_responsibility_vl   resp
WHERE
    func.application_id = resp.application_id
    AND upper(user_function_name) = upper('Define Alert');

Checking multiorg/ multi org

SELECT
    multi_org_flag
FROM
    fnd_product_groups;

Link between OM and AR

SELECT
    ooh.order_number,
    cust.interface_header_attribute1,
    ooh.header_id,
    cust.interface_header_attribute6
FROM
    oe_order_headers_all   ooh,
    ra_customer_trx_all    cust
WHERE
    TO_CHAR(ooh.header_id) = cust.interface_header_attribute6;

SELECT
    *
FROM
    ra_customer_trx_lines_all
WHERE
    sales_order = '308223';

Link between AR and GL

SELECT
    *
FROM
    xla.xla_transaction_entities   xte,
    ra_customer_trx_all            rcta,
    xla_events                     xe,
    xla_ae_headers                 xah,
    xla_ae_lines                   xal,
    gl_import_references           gim,
    gl_je_headers                  gjh
WHERE
    xte.source_id_int_1 = rcta.customer_trx_id
    AND xte.entity_code = 'TRANSACTIONS'
--    AND   trx_number = '136AE002013'
    AND xte.entity_id = xe.entity_id
    AND xe.event_id = xah.event_id
    AND xah.ae_header_id = xal.ae_header_id
    AND xal.gl_sl_link_id = gim.gl_sl_link_id
    AND gim.je_header_id = gjh.je_header_id;

SELECT
    dbms_metadata.get_ddl('PACKAGE', 'FND_WEB_SEC', 'APPS')
FROM
    dual;

SELECT
    fnd_web_sec.validate_login('CBROWN', 'WELCOME')
FROM
    dual;

SELECT
    fnd_profile.value('CBROWN/WELCOME')
FROM
    dual;

SELECT
    profile_option_name
FROM
    fnd_profile_options_tl
WHERE
    user_profile_option_name LIKE 'Guest%';

SET SERVEROUTPUT ON;

DECLARE
    stat BOOLEAN;
BEGIN
    stat := fnd_profile.save('GUEST_USER_PWD', 'CBROWN/WELCOME', 'SITE');
    IF stat THEN
        dbms_output.put_line('Stat = TRUE - profile updated');
    ELSE
        dbms_output.put_line('Stat = FALSE - profile NOT updated');
    END IF;

END;
/

Query to reverse a string

DECLARE
    ip_string   VARCHAR2(30) := '&enter_string';
    op_str      VARCHAR2(30);
BEGIN
    FOR i IN REVERSE 1..length(ip_string) LOOP op_str := op_str
                                                         || substr(ip_string, i, 1);
    END LOOP;

    dbms_output.put_line('reverse of '
                         || ip_string
                         || ' is '
                         || op_str);
END;
/
Query to reverse a string

SELECT
    LISTAGG(val) WITHIN GROUP(
        ORDER BY
            le
    )
FROM
    (
        SELECT
            level le,
            substr('SHIVAM', - level, 1) val
        FROM
            dual
        CONNECT BY
            level >= level
    );

SELECT
    *
FROM
    salgrade;

INSERT INTO salgrade VALUES (
    5,
    10000,
    12999
);

ALTER TABLE salgrade READ WRITE;

DELETE FROM salgrade
WHERE
    losal = 10000;


-
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Link between Receivables, Payables and Projects

 --Link between AP, AR and PA select aia.* from ap_invoices_all aia ,pjc_exp_items_all peia ,pjb_bill_trxs pbt ,pjb_inv_line_dists pild ,pjb...

  • Link between Receivables, Payables and Projects
     --Link between AP, AR and PA select aia.* from ap_invoices_all aia ,pjc_exp_items_all peia ,pjb_bill_trxs pbt ,pjb_inv_line_dists pild ,pjb...
  • Multi Select Feature in OAF
    Multi Select Feature in OAF: ·          We can use OADefaultListBean, which will allow enabling the multi select feature in list box wi...
  • JDR Tables in OAF
    http://happyrahoalways.blogspot.com/2017/07/jdr-tables-in-oaf-oracle-application.html JDR Tables in OAF (oracle application Framework) ...