Simply Codes: Oracle (SQL)

Showing posts with label Oracle (SQL). Show all posts

Monday, February 13, 2023

Tuning SQL

--- SELECT ALL ---

select * from page_errors;

--- SELECTED DATE ---

select to_char(a.request_datetime,'dd/mm/yyyy HH24:mi:ss'), a.*

from page_errors a

where to_char(a.request_datetime,'yyyy-mm-dd HH24:mi') = '2023-02-13 10:50'

order by a.request_datetime desc;

--- HIGHEST REQUEST ---

select z.tarikh_masa, count(*) as bil

from (

select to_char(a.request_datetime,'yyyy-mm-dd HH24:mi') as tarikh_masa

from page_errors a

where to_char(a.request_datetime,'yyyy-mm-dd HH24:mi') >= '2023-02-09 12:00'

and to_char(a.request_datetime,'yyyy-mm-dd HH24:mi') <= '2023-02-13 23:59'

) z

group by z.tarikh_masa

order by bil desc;

-- order by z.tarikh_masa desc;

Thursday, March 5, 2020

Creating new table with SELECT statement

The syntax for creating a new table is

CREATE TABLE new_table
AS
SELECT *
  FROM old_table

This will create a new table named new_table with whatever columns are in old_table and copy the data over. It will not replicate the constraints on the table, it won't replicate the storage attributes, and it won't replicate any triggers defined on the table.

SELECT INTO is used in PL/SQL when you want to fetch data from a table into a local variable in your PL/SQL block.

Credit to https://stackoverflow.com/questions/11892113/creating-new-table-with-select-into-in-sql

Friday, June 14, 2019

Tax Calculation and Round To Cent (for active record and tax >= 2.5)

SELECT COUNT(*) AS bil_akaun_dicukaikan,
SUM(E1.cukai_penggal_sebenar) AS anggaran_cukai
FROM
(SELECT D1.no_akaun,
D1.kod_stsakaun,
D1.nilai_thnn,
D1.cukai_sms,
D1.cukai_sms_sen,
D1.len_cukai_sms_sen,
D1.nilai_sen,
to_number(SUBSTR (cukai_sms,1, D1.len_cukai_sms_sen )) AS cukai_b4,
CASE
WHEN D1.nilai_sen = 1
THEN to_number(SUBSTR (cukai_sms,1, D1.len_cukai_sms_sen )) - 0.01
WHEN D1.nilai_sen = 2
THEN to_number(SUBSTR (cukai_sms,1, D1.len_cukai_sms_sen )) - 0.02
WHEN D1.nilai_sen = 3
THEN to_number(SUBSTR (cukai_sms,1, D1.len_cukai_sms_sen )) + 0.02
WHEN D1.nilai_sen = 4
THEN to_number(SUBSTR (cukai_sms,1, D1.len_cukai_sms_sen )) + 0.01
WHEN D1.nilai_sen = 5
THEN to_number(SUBSTR (cukai_sms,1, D1.len_cukai_sms_sen ))
WHEN D1.nilai_sen = 6
THEN to_number(SUBSTR (cukai_sms,1, D1.len_cukai_sms_sen )) - 0.01
WHEN D1.nilai_sen = 7
THEN to_number(SUBSTR (cukai_sms,1, D1.len_cukai_sms_sen )) - 0.02
WHEN D1.nilai_sen = 8
THEN to_number(SUBSTR (cukai_sms,1, D1.len_cukai_sms_sen )) + 0.02
WHEN D1.nilai_sen = 9
THEN to_number(SUBSTR (cukai_sms,1, D1.len_cukai_sms_sen )) + 0.01
ELSE D1.cukai_sms
END AS cukai_penggal_sebenar
FROM
(SELECT C1.no_akaun,
C1.kod_stsakaun,
C1.nilai_thnn,
C1.cukai_sms,
C1.cukai_sms_sen,
LENGTH(trim(C1.cukai_sms_sen)) AS len_cukai_sms_sen,
SUBSTR(C1.cukai_sms, LENGTH(trim(C1.cukai_sms_sen)),1) AS nilai_sen
FROM
(SELECT B1.no_akaun,
B1.kod_stsakaun,
B1.nilai_thnn,
B1.cukai_sms,
TO_CHAR(B1.cukai_sms,'9999999.99') AS cukai_sms_sen
FROM
(SELECT A1.no_akaun,
A1.kod_stsakaun,
A1.nilai_thnn,
A1.kdr_taksiran,
ROUND(A1.cukai_sms,2) AS cukai_sms
FROM
(SELECT no_akaun,
kod_stsakaun,
nilai_thnn,
kdr_taksiran,
CASE
WHEN kod_stsakaun = 'T'
THEN 0
WHEN (nilai_thnn * KDR_TAKSIRAN/100)/2 < 2.5
THEN 0
ELSE (nilai_thnn * KDR_TAKSIRAN/100)/2
END cukai_sms
FROM ntharta
WHERE nilai_thnn >= 0
AND kdr_taksiran >= 0
AND no_akaun NOT IN
( SELECT no_akaun FROM enpenilaian
)
AND kod_stsakaun = 'A'
ORDER BY no_akaun
) A1
WHERE A1.cukai_sms >= 2.5
) B1
) C1
) D1
) E1 ;

Saturday, May 11, 2019

Tax Culcalation and Cent Round Up

Wednesday, January 30, 2019

Convert Rows to columns

SELECT * FROM(
SELECT no_akaun, kod_hasil, debit, kredit FROM etlejer2018
) pivot
(
SUM(debit-kredit) FOR kod_hasil IN ('61101', '76101', '76102', '76103')
)
ORDER BY no_akaun;

Thursday, December 13, 2018

How to get column names from table

You can query the USER_TAB_COLUMNS table for table column metadata.

SELECT table_name, column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE table_name = 'MYTABLE'

Credit to: https://stackoverflow.com/questions/452464/how-can-i-get-column-names-from-a-table-in-oracle

Thursday, July 26, 2018

Date Different in Seconds

select a.*, to_char(a.created_dt,'dd/mm/yyyy hh:mi:ss') as start_dt,

to_char(a.updated_dt,'dd/mm/yyyy hh:mi:ss') as finish_dt,

round((a.updated_dt - a.created_dt) * 24 * 60 * 60,1) as seconds

from progress_bar_Status a

order by a.created_dt;

Menu Bar